How to Import Market Data into Excel Using XML
First Published Thursday, 10th March 2011 03:06 pm from Xand : Joel York
The opinions expressed by this blogger and those providing comments are theirs alone, this does not reflect the opinion of Automated Trader or any employee thereof. Automated Trader is not responsible for the accuracy of any of the information supplied by this article.
/>
src="http://api.tweetmeme.com/imagebutton.gif?url=http%3A%2F%2Fxignite.web-services-blog.com%2F2011%2F03%2Fhow-to-import-market-data-into-excel-using-xml%2F&source=xignite&style=normal&b=2"
height="61" width="50" />
The beauty of Xignite on-demand market data is that it
can power any application from spreadsheets to major financial
websites, anywhere, anytime, because it uses industry standard
Web service APIs and XML data exchange formats. This is great
news for Excel users, because Excel has fantastic XML
support.
This is the second post in a series
for Xignite customer's using Excel that explores how to
import market data into Excel using XML. The previous post in
this series showed how you can href="http://xignite.web-services-blog.com/2011/03/getting-financial-market-data-in-excel-using-csv/"
target="_blank">import market data into Excel using a CSV
file (comma separated values), the most common data
exchange format for spreadsheets. As the native data format of
Xignite Web services, XML allows you to go beyond the simple file
import available with CSV to create direct links to
Xignite Web services that allow you to update real-time market
data from within Excel.
Importing
Market Data into Excel from an XML File
The
simplest way to get market data into Excel using XML is to import
an XML file just as you would href="http://xignite.web-services-blog.com/2011/03/getting-financial-market-data-in-excel-using-csv/"
target="_blank">import a CSV file. Simply go to the
Web page on Xignite's website for any Web service operation, type
in the relevant input parameters, and click the "View in XML"
button. This will open a new browser tab with the Web service
output in XML. Let's try it now on the Web page for the
href="http://www.xignite.com/xBATSLastSale.asmx?op=GetLastSales"
target="_blank">GetLastSales operation in
XigniteBATSLastSale, an Xignite Web service providing href="http://www.xignite.com/stock-quote/BATS-last-sale-quote-data--20003005.html"
target="_blank">real-time stock quotes. Keep in mind
you must be logged in to your Xignite Web services account in
order to receive data. If you don't have an account,
just sign up for a href="https://www.xignite.com/MyAccount/Register.aspx?service=&ReturnUrl="
target="_blank">free trial.
src="http://xignite.web-services-blog.com/media/importing-market-data-excel-xml.png"
align="center"" alt="importing-market-data-excel-xml"
/>
The output should look something like
the image above. The next step is to
save the Web page as an XML file (usually File>Save As and
choosing XML or .xml as the file type). Here is the href="http://xignite.web-services-blog.com/media/GetLastSales.xml"
target="_blank" rel="nofollow">sample XML file from
the GetLastSales example above. After saving the XML file, open
it in Excel (usually File>Open and choosing XML or .xml as the
file type)
src="http://xignite.web-services-blog.com/media/excel-xml-import.png"
alt="importing-market-data-excel-xml" />
You'll see a quick import dialog box like the
one above asking if you want to import the XML file as an Excel
table. You do, so just click OK.
src="http://xignite.web-services-blog.com/media/excel-xml-import-table.png"
align="center"" alt="importing-market-data-excel-xml"
/>
When Excel is done importing the XML
file, you should see a nicely formatted Excel table like the one
above.
Creating and Refreshing an Excel Table
from the Web
Importing your market data into
Excel as an XML file as described in the previous section works
great for a one-off analysis, but it doesn't really
take advantage of the power of on-demand. If you plan to reuse
your analysis and need to update the market data in any way,
e.g., same analysis, different symbol, daily end-of-day
calculations, or even live real-time calculations, then you are
better off creating a direct Internet connection between the
Xignite Web service and your Excel spreadsheet that you can
refresh at will. This section will describe how you can
create an Excel table that pulls market data directly into Excel
over the Web with a simple mouse click.
First, navigate to the Web page for the Xignite Web
service operation of interest. Then, type in the relevant input
parameters, and click the "View in XML" button just like before.
In this example, we're going to use the href="http://www.xignite.com/xcurrencies.asmx?op=GetRealTimeCrossRateTable"
target="_blank">GetCrossRatesTable operation of the
target="_blank">XigniteCurrencies Web service to
create a cross rate table in Excel that we can automatically
refresh with real-time currency exchange rates. The relevant XML
output should appear in your browser as in the image
below.
src="http://xignite.web-services-blog.com/media/excel-xml-import-table-from-web.png"
align="center"" alt="excel-xml-import-table-from-web"
/>
The next step is to copy the Web address
(URL) of the XML Web page from your browser. This URL is
highlighted in the image above and has the following value for
this example.
http://www.xignite.com/xcurrencies.asmx/GetRealTimeCrossRateTable?Symbols=USD%2CJPY%2CGBP%2CDKK
Next, add the following authentication text to the end
of the URL
"&Header_Username=yourxigniteaccountemail@yourcompany.com",
replacing the sample email address with your own email address
used to log into your Xignite account. This will allow Excel to
access Xignite market data using your account without having to
log in through the Xignite Website. When you are done, the
complete URL should look like this.
http://www.xignite.com/xcurrencies.asmx/GetRealTimeCrossRateTable?Symbols=USD%2CJPY%2CGBP%2CDKK&Header_Username=yourxigniteaccountemail@yourcompany.com
Let's get some market data! Open a new Excel
spreadheet and select the "Data" ribbon at
the top. Then press the "From Web" icon
(highlighted below). A dialog box like the one in the image below
will appear. Paste the complete Web service URL, including your
authentication email address, into the
"Address" bar and click
"Go".
src="http://xignite.web-services-blog.com/media/excel-market-data-web-query.png"
alt="excel-market-data-web-query" />
If all
has gone well, you should see the XML output in the dialog box
Web page region just like in the image above. When you do, press
the "Import" button. This will set off the
same process as in the previous section for importing the XML
file and should result in a cross rate table similar to the one
below.
src="http://xignite.web-services-blog.com/media/currency-exchange-rate-xml-excel-import.png"
alt="currency-exchange-rate-xml-excel-import" />
However, there is the one BIG difference from the simple
XML file import approach used in the previous section. The Excel
table is now linked directly to the Xignite Web service via the
Internet.
src="http://xignite.web-services-blog.com/media/currency-exchange-rate-excel-real-time.png"
alt="currency-exchange-rate-excel-real-time" />
The market data in your Excel spreadsheet is LIVE!
Simply click the "Refresh All" button on the
"Data" ribbon above and the table will
automatically update.
src="http://xignite.web-services-blog.com/media/excel-external-data-source-warning.png"
alt="excel-external-data-source-warning" />
You may receive a warning dialog box like the one above
on the first refresh. Since you hopefully trust the source of the
file, Xignite, us, just click "OK" and all
the exchange rates in the table will update to the most current
values. XigniteCurrencies provides real-time exchange rates that
update every ten seconds, so you can repeat the refresh every ten
seconds to get the latest exchange rates.
How
simple was that!
Well, the truth is that Excel
did a lot of work behind the scenes to link the XML output
directly to the table in your Excel spreadsheet, including
mapping each data element to a particular row and column. This
behind the scenes work sets up some very cool options for power
Excel users that we'll describe in the next post in
this series, such as modifying the symbols to get different
currency rates. In the next post, we'll explore how you
can create interactive Excel spreadsheets that pull live market
data over the Web using XML and completely automate your analysis
with Excel macros.
Share and Enjoy: rel="nofollow" target="_blank"
href="mailto:?subject=How%20to%20Import%20Market%20Data%20into%20Excel%20Using%20XML&body=http%3A%2F%2Fxignite.web-services-blog.com%2F2011%2F03%2Fhow-to-import-market-data-into-excel-using-xml%2F"
title="email">
src="http://xignite.web-services-blog.com/wp-content/plugins/sociable/images/email_link.png"
title="email" alt="email" class="sociable-hovers" />
title="Twitter" alt="Twitter" class="sociable-hovers"
title="Facebook" alt="Facebook" class="sociable-hovers"
title="LinkedIn" alt="LinkedIn" class="sociable-hovers"
title="Digg">
src="http://xignite.web-services-blog.com/wp-content/plugins/sociable/images/digg.png"
title="Digg" alt="Digg" class="sociable-hovers" />
title="StumbleUpon" alt="StumbleUpon" class="sociable-hovers"
title="Technorati" alt="Technorati" class="sociable-hovers"
title="Suggest to Techmeme via Twitter" alt="Suggest to Techmeme
via Twitter" class="sociable-hovers" /> rel="nofollow" target="_blank"
href="http://delicious.com/post?url=http%3A%2F%2Fxignite.web-services-blog.com%2F2011%2F03%2Fhow-to-import-market-data-into-excel-using-xml%2F&title=How%20to%20Import%20Market%20Data%20into%20Excel%20Using%20XML¬es=This%20is%20the%20second%20post%20in%20a%20series%20for%20Xignite%20customer%27s%20using%20Excel%20that%20explores%20how%20to%20import%20market%20data%20into%20Excel%20using%20XML.%20"
title="del.icio.us">
src="http://xignite.web-services-blog.com/wp-content/plugins/sociable/images/delicious.png"
title="del.icio.us" alt="del.icio.us" class="sociable-hovers"
title="Google Bookmarks" alt="Google Bookmarks"
title="Live">
src="http://xignite.web-services-blog.com/wp-content/plugins/sociable/images/live.png"
title="Live" alt="Live" class="sociable-hovers" />
title="FriendFeed" alt="FriendFeed" class="sociable-hovers"
src="http://feeds.feedburner.com/~r/Xignite/~4/Thn1QGgLmFc"
height="1" width="1" />
