How to create an Excel based Multi-Asset Class P&L Tool in 5 Minutes
Tracking multi-asset class portfolios in real-time is critical to active investors. Since many investors, particularly portfolio managers and analysts, typically spend a good part of their day in Microsoft Excel, having the capability to track multi-asset class portfolios within Excel would be a tremendous benefit.
Getting real-time pricing on multi-asset class portfolios within Excel has historically been a tedious (and often expensive) chore, requiring multiple vendors and technologies. But with Xignite’s on-demand cloud APIs, it’s now simple and straightforward. Even those without a programming background can be up and running in just a few minutes.
In this article, I’ll provide step by step instructions on how to create your own multi-asset class P&L tool in Excel with real-time pricing data from Xignite. All the examples in this post are available for download in this Excel spreadsheet.
This is the fourth post in our series on combining the power of Excel and Xignite. For more info on this topic, be sure to review our posts on automating financial models, importing market data using XML and importing data using CSV.
Importing Multi-Asset Class Data into Excel
In this example, let’s suppose we hold a multi-asset class portfolio with a few stocks (ORCL, AA & T), an ORCL call option, an ORCL bond, and crude oil futures. To create a real-time multi-asset P&L tool, we need live financial data. We’ll use the following web services APIs from Xignite to import them into Excel via XML:
- Xignite ExtendedRealQuotes for stock quotes
- Xignite RealTimeOptions for options data
- Xignite Bonds for bond data
- Xignite Futures for futures data
We covered how to import XML in a previous post, but let’s quickly review the process by pulling the stock data as an example. We’ll start on the Web page for the GetExtendedRealQuotes operation, a Xignite Web service providing real-time stock quotes.
Once you are on the webpage, update the symbols (ORCL, AA, T) in the prompt at the top of the page, click on view in XML, and copy the URL that gets generated. Next, open a new Excel worksheet, go to the Data tab and select From Web, then paste the URL. At the end of the URL append the following; replacing the email address with the one you used to sign up for your Xignite account: &header_username=yourauthenticationtoken
Note : The free trial does not grant you access to the Xignite’s real-time services. You can use the following web service APIs instead:
- Xignite BatsRealTime for stock quotes
- Xignite GetEquityOption for options data
- Xignite GetBondTradedPrice for bond data
- Xignite GetDelayedFuture for futures data
You should end up with a table that looks like this:
Now follow the same process for options, bonds, and futures, which will result in new tables for each asset class. If after the import your data does not have headings, right click anywhere on the data, select XML > XML Source, then right click on the folder icon, remove the element and right click again and remap it. The paste option at the end of the data now has the option to include XML headings.
Implementing a Macro for Real-Time P&L
Now that the data has been imported, a simple macro enables us to monitor price movements in real-time by automatically refreshing the data.
To implement this, simply go to the Developer tab in Excel, select Macros, and then create a new macro called RefreshTime and select This Workbook in the Macros dropdown.
Next, paste the following macro script in the VBA window:
Sub RefreshTime() Application.ScreenUpdating = False ActiveWorkbook.RefreshAll Application.OnTime Now + TimeValue("00:00:01"), "RefreshTime" Range("D1").Value = "Last:" Range("E1").Value = Format(Now, "hh:mm:ss AM/PM") Application.ScreenUpdating = True End Sub
This macro refreshes all the Web Query connections set up in the worksheet every second. It also prints the last update time on the worksheet, so that you can be sure you’re getting real-time data.
Now that you have the proper data imported and the refresh macro set up, you can input your buy prices and set up the P&L calculations in Excel. To make the tool more visually intuitive, you can add conditional formatting to the data based on parameters like real-time price or volume movements.
Here’s a link to the multi-asset class P&L Tool that I built in Excel using this example. It refreshes the data every second and reflects changes in security prices and ITD (Inception-to-Date P&L), YTD (Year-to-Date P&L) & MTM (Mark-to-Market-P&L) by changing colors.
As seen in this example, combining Excel’s highly flexible and customizable tools with Xignite’s on-demand financial data enables you to quickly build powerful financial applications. This real-time multi-asset class P&L tool is just a springboard – building upon this, you can create even more complex Excel financial models and presentation spreadsheets to suit your needs.
In our next post , we will explore how we can build a real-time greek calculator using Xignite’s web service APIs.
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.