How to Import Market Data into Excel Using XML
from Xand : Joel York - 31st December 1969
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.
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 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 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 GetLastSales operation in XigniteBATSLastSale, an Xignite Web service providing 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 free trial.
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 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)
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.
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 GetCrossRatesTable operation of the 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.
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.
Next, add the following authentication text to the end of the URL "&Header_Usernamefirstname.lastname@example.org", 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.
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".
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.
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.
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.
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.