It may be easier than you think to get the latest financial market data in Excel spreadsheets using Xignite. Most of our customers use Xignite’s web services to power their custom-built applications and websites with financial market data. But if you’re a financial analyst or a power user of Microsoft Excel, you may not want to build a new application. What if you aren’t a programmer? What if you just want the market data in Excel and you don’t need any other application? In that case, all you really want is a simple way to get market data in Excel so you can perform your analytical magic without the need to hire a programmer. In this blog post, I’ll show you a simple, easy way to do just that.
Web Pages Get Market Data in Excel Instantly
Excel can easily open files in the comma-separated values (CSV) file format. And Xignite offers an easy, convenient way to get market data from any of our web services into CSV format. Simply go to the web page on Xignite’s website for any web service operation and click the “View in CSV” button. 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 can only get data from Xignite’s web services if you have an account that entitles you to receive data. If you need an account, just sign up for a free trial.
If Excel doesn’t open when you double click the CSV file you download from Xignite, never fear. Normally, CSV files will open automatically in Excel. If a different application opens or if Windows asks you which application to use, that means Windows probably hasn’t associated the CSV file format with Excel. If that happens to you, just open Excel, press Ctrl+O and select the CSV file to open it directly from within Excel.
URLs Get Market Data in Excel Directly
You can also get market data from any of Xignite’s web services in CSV format simply by pointing your web browser to the right URL. To find the right URL with exactly the market data you want, first go to the web page of the operation you want to get data from, then click the “View in XML” button and then change the URL of the XML output to replace “.asmx” with “.csv” and you’re done!
Here’s an example: If you go to the web page for the GetLastSales operation in XigniteBATSLastSale and click “View in XML” it will take you to the following URL:
http://www.xignite.com/xBATSLastSale.asmx/GetLastSales?Symbols=msft,orcl,dell
To get this same data in CSV format instead of XML format, simply change “.asmx” to “.csv” in the URL (and add “&_DownloadFile=y” to make the file easier to download in some browsers) as follows:
http://www.xignite.com/xBATSLastSale.csv/GetLastSales?Symbols=msft,orcl,dell&_DownloadFile=y
If you prefer, you can even change how rows repeat their data by adding “&_RepeatParentData=false” to make sure the XML parent nodes are not repeated, as follows:
To get stock quotes for Microsoft, Oracle and Dell in CSV format, just enter this URL into your web browser. Your browser will prompt you to download the CSV file.
Just one last step: Since the file you download will not have the “.csv” file extension in its name, you’ll need to add “.csv” to the end of its file name so that Excel can recognize it as a CSV file.
Excel Gets Market Data Dynamically
If you need a lot of different kinds of data, you may not want to use web pages or manually entered URLs to get market data in Excel. If there are too many web pages or too many URLs you need to enter manually, then you may need a way to get market data in Excel more systematically.
Fortunately, we can use Excel itself to create the right URLs dynamically for you. Then all you have to do is click on the URL links to get market data in Excel.
In the example from the section above, you may have noticed that the symbol parameter values entered on the Xignite web page (MSFT, ORCL, DELL) are included in the URL itself (“Symbols=msft,orcl,dell”) setting the variable “Symbols” to the list of symbols you requested. If you wanted stock quotes for Google, Amazon and Apple instead of Microsoft, Oracle and Dell, all you have to do is replace “MSFT,ORCL,DELL” in the URL with “GOOG,AMZN,AAPL” instead. Then your URL would look like this:
http://www.xignite.com/xBATSLastSale.csv/GetLastSales?Symbols=goog,amzn,aapl&_DownloadFile=y
You could just as easily use Excel to create the above URL dynamically based on other cells in Excel where you enter which symbols you want to get.
Let’s take an example: In a blank Excel spreadsheet, fill cells A1, A2 and A3 with GOOG, AMZN, and AAPL respectively. Then, in cell A5 enter the following formula:
=”http://www.xignite.com/xBATSLastSale.csv/GetLastSales?Symbols=” & A1 & “,” & A2 & “,” & A3
This formula should create a URL that’s identical to the one above. If you go to that URL, you’ll get stock quotes for Google, Amazon and Apple. If you change GOOG to MSFT, then your URL will dynamically change to a URL that will get a stock quote for Microsoft instead of Google. Now Excel is dynamically creating the URL where you’ll find exactly the data you want.
Just one problem: You’ll notice your dynamically created URL in Excel can’t be clicked on. To make the URL clickable from Excel, just use Excel’s HYPERLINK formula. Here’s an example:
=HYPERLINK(“http://www.xignite.com/xBATSLastSale.csv/GetLastSales?Symbols=” & A1 & “,” & A2 & “,” & A3, “Click here”)
Now you can click the “Click here” link in Excel and your web browser will immediately go to the dynamically created URL and download the CSV file with the data you want.
Tip: Use Separate Excel Tabs for Raw Market Data vs. Analysis
So now you’re a market data Excel guru, capable of pulling all sorts of market data in Excel, slicing it, dicing it, pivoting it and charting it! Then you need to update all your analysis with the latest up-to-the-minute market data and suddenly you come to a screeching halt! You don’t want to lose all the analysis you’ve done or the charts you’ve created. How can you keep all your hard work from going to waste and just update the market data without affecting anything else in your Excel spreadsheet?
To make regular updates to your market data in Excel without rework, be sure to keep your raw market data in a separate Excel tab from the Excel tabs that include your custom analysis, charts, pivot tables and so on. If you keep raw market data from Xignite in a separate Excel tab, updating your analysis will be as simple and easy as copying and pasting new raw market data directly from Xignite into your raw market data tab in Excel. All of your other Excel tabs that reference the raw market data tab will update automatically with no need for rework.
See an Example
Get a concrete example of every tip in this blog post by checking out this Excel spreadsheet. The Excel spreadsheet uses a simple example that values an equity portfolio. One Excel tab includes the portfolio analysis, while the other holds the raw market data the analysis references. The Excel spreadsheet shows a dynamically created, clickable URL to get the latest raw market data in CSV format. The Excel spreadsheet also includes detailed, step by step instructions on how to refresh the portfolio’s valuation with the latest real-time market data from Xignite.
__________
For more info on how Xignite's market data cloud can improve your business processes, request a consultation with one of our data experts.
To test drive our market data APIs and experience for yourself how easy they are to integrate into your applications, request a free trial.
Author: John Neff
