Automating Excel Financial Models with Live Market Data
from Xand : Joel York - 1st January 1970
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.
Ad hoc Excel financial models are employed across a wide range of applications from stock portfolio management, currency trading, loan processing, etc., but they all have one thing in common: they all need ad-hoc financial market data as the input to the analysis.
This is the third post in a series that describes how to use Xignite on-demand financial market data with Excel. The last post in the series described how to import live market data into Excel using Xignite Web Service XML output. This post will take it to the next level and explore how to create Excel financial models that not only bring in live market data over the Internet, but allow you to modify the market data requested using Excel macros to create dynamic Excel financial models. All the examples in this post are available for download in this Excel spreadsheet.
The first step is to import the XML output from a representative Web service request that you will use in the analysis. For example, if the analysis is driven by the current stock price of a range of equities, then you might want to use the XigniteQuotes Web service which provides delayed stock quotes for US Equities. This process was covered in the last post in this series and produces a table such as the one below.
Which for reference was created using the Excel Web XML import of the following Xignite Web service URL.
Where the authentication token at the end of the URL "firstname.lastname@example.org" is simply the email address used to log into your Xignite account. If you don't have an Xignite account, you can sign up for the free trial. (Note: To create more secure authentication, you can also use your IP address or any random string instead of your Xignite email, simply by adding a new token to your Xignite account.)
If you right click anywhere on the table, you can bring up the options for handling XML. In particular, take a look at what happens when you select "XML Source."
Excel will open up the details of what it calls the "XML Map" for the table.
So, what exactly is going on here??? If you have ever used Excel pivot tables, the leap to how Excel handles XML is not that big. What is happening is this:
- Excel links to the XML output at the Web service URL. Excel calls this the "Data Binding"
- Excel maps the XML output to a range of cells. Excel calls this the "XML Map"
- Excel adds formatting, sorting, filtering to the output to make it dynamic. Excel calls this type of table a "list"
We're going to continue by changing the name of the XML Map from the cryptic "ArrayOfQuote_Map" to simply "Quotes" by clicking XML Maps… and Rename… from the XML source dialog. You can also add and remove items from the table (Excel list) by dragging and dropping from the XML Map or simply cutting and pasting within the spreadsheet. For this example, we're going to remove everything except for symbol, name, date, time, high, low, last and volume by directly deleting the irrelevant columns in the spreadsheet. Notice that the remaining columns are bold on the XML Map whereas the removed columns are not, i.e., the bold shows which columns have been mapped from the XML page on the Web to the Excel list in your spreadsheet. So, now our XML map looks like this.
Now the fun begins! From the last post we know that we can update the data in our Excel table in real-time simply by clicking the Refresh All button. But, we can't change the parameters (e.g., stock symbols, currency pairs, etc.) that drive the output. We can only update it. What is really happening when we click Refresh All is that Excel goes back out to the "Data Binding" that points to the Web service URL and refreshes the XML output, then it uses the XML map to reload the table. To alter the output, we need to alter the input, i.e., the Web service URL in the Excel data binding. This can be done by creating an Excel Macro that automates the series of steps we used to create the table. The Excel VB Macro below does exactly this.
Private Sub CommandButton1_Click()
Dim Symbol_List As String
Dim Quote_Url As String
Symbol_List = ActiveWorkbook.ActiveSheet.Range("$J$1″).Value
Quote_Url = "http://www.xignite.com/xquotes.asmx/GetQuotes?
Symbol=" & Symbol_List & "
The simple Excel macro links to a button in our spreadsheet above and automates the following tasks when the button is clicked:
- Grabs a new set of symbols from the spreadsheet
- Creates the proper URL for the Web service request using the new symbols
- Loads the new URL into the Excel XML Map Data Binding
- Refreshes the Excel XML Map Data Binding
- Upon Refresh, the Web Service is called and the table reloads via the XML Map
The spreadsheet looks something like the image below.
This exact same procedure can be used with any Xignite Web service. Here is a sample of what a currency cross rate table might look like using the GetRealTimeCrossRateTable operation of the XigniteCurrencies Web service that was explored in the previous post in this series.
Finally, any service that returns a time series or similar data like the GetTicks operation of XigniteQuotes can be combined with Excel charts to create graphs that you can interactively update in real-time.
These three examples above use virtually the same Excel macro, the only difference is the Xignite Web service URL and the XML Map in Excel. They are available for download in this example spreadsheet, however, to make it work YOU MUST REPLACE THE GENERIC AUTHENTICATION STRING WITH YOUR OWN XIGNITE ACCOUNT in the VB Editor and YOU MUST ENABLE THE SECURITY SETTINGS IN EXCEL TO ALLOW MACROS as in the picture below.
If you've ever written an Excel macro in the past, this should be straightforward. These examples are just a springboard for automating Excel financial models with live market data. They show the basics of how to get live market data into your Excel financial model and update it dynamically. Building upon this foundation, you have the combined power of market data from every Xignite Web service and all Excel functions, charts and macros at your fingertips to create complex Excel financial models and presentation spreadsheets.