How to create an Excel based Multi-Asset Class P&L Tool in 5 Minutes

First Published Friday, 16th December 2011 02:26 pm from Xand : rsrivastava

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.

LinkedInTwitterFacebookEmail

href="http://api.tweetmeme.com/share?url=http%3A%2F%2Fxignite.web-services-blog.com%2F2011%2F12%2Fhow-to-create-an-excel-based-multi-asset-class-pl-tool-in-5-minutes%2F">

/> src="http://api.tweetmeme.com/imagebutton.gif?url=http%3A%2F%2Fxignite.web-services-blog.com%2F2011%2F12%2Fhow-to-create-an-excel-based-multi-asset-class-pl-tool-in-5-minutes%2F&source=xignite&style=normal&b=2"

height="61" width="50" />

P&L Tool

src="http://cdn.xignite.com/blog/12_08_11/PL_Tool4.bmp" alt=""

width="390" height="127" />

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 href="http://cdn.xignite.com/blog/12_15_11/MultiAsset_PL_FD_Web%20%283%29.xlsm">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 href="http://xignite.web-services-blog.com/2011/03/automating-excel-financial-models-with-live-market-data/">automating

financial models, href="http://xignite.web-services-blog.com/2011/03/how-to-import-market-data-into-excel-using-xml/">importing

market data using XML and href="http://xignite.web-services-blog.com/2011/03/getting-financial-market-data-in-excel-using-csv/">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:

href="http://www.xignite.com/xRealTime.asmx?op=GetExtendedRealQuotes">Xignite
ExtendedRealQuotes for stock quotes
href="http://www.xignite.com/xRealTimeOptions.asmx">Xignite
RealTimeOptions for options data
href="http://www.xignite.com/xBondsRealTime.asmx?op=GetBondTradedPriceRealTime">Xignite
Bonds for bond data
href="http://www.xignite.com/xFutures.asmx">Xignite
Futures for futures data

We covered how to import XML in a href="http://xignite.web-services-blog.com/2011/03/how-to-import-market-data-into-excel-using-xml/">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

href="http://www.xignite.com/xRealTime.asmx?op=GetExtendedRealQuotes">GetExtendedRealQuotes

operation, a Xignite Web service providing href="http://www.xignite.com/stock-quote/BATS-real-time-stock-quote-data--20003004.html"

target="_blank">real-time stock quotes.

GetExtendedRealQuotes<br /> Web Service

src="http://cdn.xignite.com/blog/12_08_11/GetExtendedRealQuote_2.jpg"

alt="" width="450" height="300" />

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

Keep in mind you must be logged into 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.

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:

href="http://www.xignite.com/xBATSRealTime.asmx?op=GetRealQuotes">Xignite
BatsRealTime for stock quotes
href="http://www.xignite.com/xOptions.asmx?op=GetEquityOption">Xignite
GetEquityOption for options data
href="http://www.xignite.com/xBonds.asmx?op=GetBondTradedPrice">Xignite
GetBondTradedPrice for bond data
href="http://www.xignite.com/xFutures.asmx?op=GetDelayedFuture">Xignite
GetDelayedFuture for futures data

You should end up with a table that looks

like this:

title="Stock Quote Table"

src="http://cdn.xignite.com/blog/12_08_11/Stock_Table.jpg" alt=""

width="450" height="87" />

Now follow the

same process for href="http://www.xignite.com/xRealTimeOptions.asmx?op=GetEquityOption">options,

href="http://www.xignite.com/xBondsRealTime.asmx">bonds,

and href="http://www.xignite.com/xFutures.asmx?op=GetDelayedFuture">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:

bgcolor="#C0C0C0">

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 href="http://cdn.xignite.com/blog/12_15_11/MultiAsset_PL_FD_Web%20%283%29.xlsm">

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.

Share and Enjoy:

href="mailto:?subject=How%20to%20create%20an%20Excel%20based%20Multi-Asset%20Class%20P%26L%20Tool%20in%205%20Minutes&body=http%3A%2F%2Fxignite.web-services-blog.com%2F2011%2F12%2Fhow-to-create-an-excel-based-multi-asset-class-pl-tool-in-5-minutes%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" />

href="http://twitter.com/home?status=How%20to%20create%20an%20Excel%20based%20Multi-Asset%20Class%20P%26L%20Tool%20in%205%20Minutes%20-%20http%3A%2F%2Fxignite.web-services-blog.com%2F2011%2F12%2Fhow-to-create-an-excel-based-multi-asset-class-pl-tool-in-5-minutes%2F"

title="Twitter"> src="http://xignite.web-services-blog.com/wp-content/plugins/sociable/images/twitter.png"

title="Twitter" alt="Twitter" class="sociable-hovers"

href="http://www.facebook.com/share.php?u=http%3A%2F%2Fxignite.web-services-blog.com%2F2011%2F12%2Fhow-to-create-an-excel-based-multi-asset-class-pl-tool-in-5-minutes%2F&t=How%20to%20create%20an%20Excel%20based%20Multi-Asset%20Class%20P%26L%20Tool%20in%205%20Minutes"

title="Facebook"> src="http://xignite.web-services-blog.com/wp-content/plugins/sociable/images/facebook.png"

title="Facebook" alt="Facebook" class="sociable-hovers"

href="http://www.linkedin.com/shareArticle?mini=true&url=http%3A%2F%2Fxignite.web-services-blog.com%2F2011%2F12%2Fhow-to-create-an-excel-based-multi-asset-class-pl-tool-in-5-minutes%2F&title=How%20to%20create%20an%20Excel%20based%20Multi-Asset%20Class%20P%26L%20Tool%20in%205%20Minutes&source=Financial+Market+Data+Web+Services+-+Xignite+Blog+It%26%23039%3Bs+not+the+data.++It%26%23039%3Bs+the+delivery.&summary=%0D%0A%0D%0ATracking%20multi-asset%20class%20portfolios%20in%20real-time%20is%20critical%20to%20active%20investors.%20Since%20many%20investors%2C%20particularly%20portfolio%20managers%20and%20analysts%2C%20typically%20spend%20a%20good%20part%20of%20their%20day%20in%20Microsoft%20Excel%2C%20having%20the%20capability%20to%20track%20mu"

title="LinkedIn"> src="http://xignite.web-services-blog.com/wp-content/plugins/sociable/images/linkedin.png"

title="LinkedIn" alt="LinkedIn" class="sociable-hovers"

href="http://digg.com/submit?phase=2&url=http%3A%2F%2Fxignite.web-services-blog.com%2F2011%2F12%2Fhow-to-create-an-excel-based-multi-asset-class-pl-tool-in-5-minutes%2F&title=How%20to%20create%20an%20Excel%20based%20Multi-Asset%20Class%20P%26L%20Tool%20in%205%20Minutes&bodytext=%0D%0A%0D%0ATracking%20multi-asset%20class%20portfolios%20in%20real-time%20is%20critical%20to%20active%20investors.%20Since%20many%20investors%2C%20particularly%20portfolio%20managers%20and%20analysts%2C%20typically%20spend%20a%20good%20part%20of%20their%20day%20in%20Microsoft%20Excel%2C%20having%20the%20capability%20to%20track%20mu"

title="Digg"> src="http://xignite.web-services-blog.com/wp-content/plugins/sociable/images/digg.png"

title="Digg" alt="Digg" class="sociable-hovers" />

href="http://www.stumbleupon.com/submit?url=http%3A%2F%2Fxignite.web-services-blog.com%2F2011%2F12%2Fhow-to-create-an-excel-based-multi-asset-class-pl-tool-in-5-minutes%2F&title=How%20to%20create%20an%20Excel%20based%20Multi-Asset%20Class%20P%26L%20Tool%20in%205%20Minutes"

title="StumbleUpon"> src="http://xignite.web-services-blog.com/wp-content/plugins/sociable/images/stumbleupon.png"

title="StumbleUpon" alt="StumbleUpon" class="sociable-hovers"

href="http://technorati.com/faves?add=http%3A%2F%2Fxignite.web-services-blog.com%2F2011%2F12%2Fhow-to-create-an-excel-based-multi-asset-class-pl-tool-in-5-minutes%2F"

title="Technorati"> src="http://xignite.web-services-blog.com/wp-content/plugins/sociable/images/technorati.png"

title="Technorati" alt="Technorati" class="sociable-hovers"

href="http://twitter.com/home/?status=tip%20@Techmeme%20http%3A%2F%2Fxignite.web-services-blog.com%2F2011%2F12%2Fhow-to-create-an-excel-based-multi-asset-class-pl-tool-in-5-minutes%2F%20How%20to%20create%20an%20Excel%20based%20Multi-Asset%20Class%20P%26L%20Tool%20in%205%20Minutes"

title="Suggest to Techmeme via Twitter"> src="http://xignite.web-services-blog.com/wp-content/plugins/sociable/images/techmeme.png"

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%2F12%2Fhow-to-create-an-excel-based-multi-asset-class-pl-tool-in-5-minutes%2F&title=How%20to%20create%20an%20Excel%20based%20Multi-Asset%20Class%20P%26L%20Tool%20in%205%20Minutes&notes=%0D%0A%0D%0ATracking%20multi-asset%20class%20portfolios%20in%20real-time%20is%20critical%20to%20active%20investors.%20Since%20many%20investors%2C%20particularly%20portfolio%20managers%20and%20analysts%2C%20typically%20spend%20a%20good%20part%20of%20their%20day%20in%20Microsoft%20Excel%2C%20having%20the%20capability%20to%20track%20mu"

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"

href="http://www.google.com/bookmarks/mark?op=edit&bkmk=http%3A%2F%2Fxignite.web-services-blog.com%2F2011%2F12%2Fhow-to-create-an-excel-based-multi-asset-class-pl-tool-in-5-minutes%2F&title=How%20to%20create%20an%20Excel%20based%20Multi-Asset%20Class%20P%26L%20Tool%20in%205%20Minutes&annotation=%0D%0A%0D%0ATracking%20multi-asset%20class%20portfolios%20in%20real-time%20is%20critical%20to%20active%20investors.%20Since%20many%20investors%2C%20particularly%20portfolio%20managers%20and%20analysts%2C%20typically%20spend%20a%20good%20part%20of%20their%20day%20in%20Microsoft%20Excel%2C%20having%20the%20capability%20to%20track%20mu"

title="Google Bookmarks"> src="http://xignite.web-services-blog.com/wp-content/plugins/sociable/images/googlebookmark.png"

title="Google Bookmarks" alt="Google Bookmarks"

class="sociable-hovers" />

target="_blank"

href="https://favorites.live.com/quickadd.aspx?marklet=1&url=http%3A%2F%2Fxignite.web-services-blog.com%2F2011%2F12%2Fhow-to-create-an-excel-based-multi-asset-class-pl-tool-in-5-minutes%2F&title=How%20to%20create%20an%20Excel%20based%20Multi-Asset%20Class%20P%26L%20Tool%20in%205%20Minutes"

title="Live"> src="http://xignite.web-services-blog.com/wp-content/plugins/sociable/images/live.png"

title="Live" alt="Live" class="sociable-hovers" />

href="http://www.friendfeed.com/share?title=How%20to%20create%20an%20Excel%20based%20Multi-Asset%20Class%20P%26L%20Tool%20in%205%20Minutes&link=http%3A%2F%2Fxignite.web-services-blog.com%2F2011%2F12%2Fhow-to-create-an-excel-based-multi-asset-class-pl-tool-in-5-minutes%2F"

title="FriendFeed"> src="http://xignite.web-services-blog.com/wp-content/plugins/sociable/images/friendfeed.png"