Market Data Feed - Financial Web Service - On-Demand

If stock market pundits like Franklin Templeton’s Mark Mobius are to be believed, markets are going to be just as volatile as they were in 2011. The VIX index, a popular measure of implied volatility, made multiple excursions into the 40s last year, finally ending 2011 at 23.8, up almost 32% from 2010.

Options trading strategies cater well to volatility, offering the opportunity to capitalize on market volatility as a source of investment. That being said, most option traders typically focus on one option Greek – Delta. While Delta certainly helps in mitigating the risk of an open position, it’s important to trade Delta in conjunction with the other option Greeks – Vega, Theta, and Gamma. Each option Greek measures a different dimension of the risk in an option position. The aim of an options trader is to manage the option Greeks so that all risks are acceptable.

In this post, I’ll provide step-by-step instructions on how to create your own real-time Excel-based option Greeks calculator with on-demand options data from Xignite’s market data cloud. This will facilitate your option trading process, by allowing you to look at all the option Greeks, side by side.

Although, this is our fifth post in the series on combining the power of Xignite and Excel, it’s our first one where we describe our powerful API mash-up platform, Splice, which provides you with the flexibility to get all the data you need in a single web service call.

Creating a Composite Black-Scholes Web Service API

Options are never traded by examining the option Greeks or the underlying stock in isolation. Traders need to look at the complete picture before making a decision. The composite web service API, we will create, addresses this need. It also provides the Black-Scholes option value, which can be used as a benchmark, for your own option Greek calculations. In this example, we will mash-up the following web service operations:

• GetLastSale for the latest stock quotes
• GetEquityOption for option quotes
• GetBlackScholesOptionValue for the Black-Scholes Value

A composite web service can be created by combining any of Xignite’s web service APIs. Keep in mind; you must be logged into Splice to do the same. If you don’t have an account yet, you can just sign up for a free trial.

After you login, click on create Splice, at the top of the page.

Clicking on the create Splice feature, will take you to the Splice editor, which offers a simple drag and drop UI.

The left pane lists all the web service APIs that Xignite offers. The right pane acts as a whiteboard where the web service APIs can be mashed-up.

The next step just involves dragging any web service from the left pane to the white board and linking the inputs and outputs you require for your web service call.

In this example, we used the GetLastSale, GetEquityOption and GetBlackScholesOptionValue web service APIs. The mash-up I created is called BS. You can access it on the Splice Studio community. You can also clone it and make modifications.

Note: For a more detailed explanation on how to create a Splice, watch this 5 minute intro video.

Importing Option Data into Excel

We covered how to import data into Excel, using web services in a previous post. The only difference in this post is that, you are now using a composite web service API that has been created by you (Isn’t that exciting!!!).

After you have imported data from the customized Black-Scholes API, there is another piece of data you need to import to complete the option Greek calculations. One of the most important inputs in option Greeks calculations is the volatility. In this example, I calculated historical volatility based on Google’s (GOOG) one year price movements. The historical data can be accessed using Xignite’s GetHistoricalQuotesAsof web service API. It lets you specify the period type (daily, weekly, monthly, quarterly or annually) and period, so that you can set up volatility calculations based on your volatility strategies. The GetHistoricQuotesAsof web service API produces a table as shown below:

Setting up an Option Greeks Calculator

All you need to do now is, provide the inputs and set up the calculations. The formula for all the option Greeks is present in the attached Excel spreadsheet.

To the make the process more intuitive, the attached spreadsheet contains a button that updates the stock quotes and also calculates the latest option Greeks. The macro that does this is given below:

Clicking on the “Calculate Option Greeks” button takes the inputs from the excel sheet, plugs it into the Black-Scholes formula, and returns all the option Greeks in the format as shown below.

As you saw in this post, using the Splice platform gives you the ability to create custom web service APIs that match your unique option trading strategies. Coupling these customized web service APIs with Excel gives you ability to quickly create a real-time options data Greeks calculator so that you can trade on volatility with all the important option indicators at your fingertips. Give it a try and let me know what think…..

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.

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.

Technology has led a dynamic transformation of the exchange industry, and cloud strategies are an important factor in unlocking the revenue potential of exchange data. The following article about how to monetize exchange data via the cloud was recently published in “How To Build An Exchange,” a fascinating must-read collection from our friends at Mondo Visione. Request access to the full collection from Mondo Visione here.

_____________

The global environment for exchanges and data originators is rapidly evolving with exploding data volumes, increasing and unpredictable regulation, expanding variety of financial instruments, fragmenting market, competitive encroachment and the need for new sources of revenue. Beyond these challenges, the race to microsecond latency will stretch the infrastructure capacities and budgets at major financial services firms.

Exchanges and data originators are struggling to service the opportunities of the future with the products and solutions of the past. A paradigm shift is rapidly approaching as old monolithic systems designed for universal applicability are being replaced by new custom tailored micro and mobile apps designed to satisfy the needs of a specific knowledge worker or user type.

The emergence of new mobile technologies and use-specific applications is altering the usage patterns of market data and changing the ways market data is purchased and consumed. If an emerging exchange is to succeed, it must tackle these issues in new ways.

A few years from now the market data application mix will boil down to two distinct flavors: low latency and everything else. How will exchanges capture new market data revenue profitably and manage the delivery of these two distinct flavors when their target applications, usage patterns and infrastructure requirements are so dramatically different?

Given its mission-critical impact on trading efficiency, low latency infrastructure has historically received the bulk of attention and investment. Meanwhile, legacy data distribution systems are crumbling under the weight of the increasing volumes of data produced by low-latency trading. The cost and complexity of servicing everything else is skyrocketing. Enter cloud computing. Once thought to be the domain of small and medium-sized businesses, cloud strategies are now seeing investment from exchanges and data originators to gain economies-of-scale, simplify the access and delivery of market data, decrease time-to-market for new products and add new revenue streams from new and underserviced segments.

In August of 2011, The Melbourne Group laid out the following cloud action plan for existing and emerging exchanges:

Make Core Market Data Assets Available from the Cloud

By creating an Internet-based cloud offering using standard cloud APIs, exchanges can offer both institutional and retail customers a means to access their core market data assets on-demand while driving down both the costs of access for the customer and the costs of service and maintenance for all.

Create Cross-Selling Opportunities with Direct Feed Customers

Direct feeds designed to service automated trading applications are poor vehicles for usercentric and mobile applications. A cloud offering coupled with an exchange’s direct feeds allows buy and sell-side firms to synchronize front and back-office operations seamlessly.

Monetize Exchange Data Assets by Targeting Unmet Business Needs

Without the cloud, the sheer volume of stale data sets make broad delivery cost-prohibitive to anyone but the largest clients. With the cloud, exchanges can move away from the one-size-fits-all approach and offer custom tailored products that allow customers to select and consume only the data required for their specific applications, modeling or back-testing.

Expand and Simplify Global Access and Delivery

Cloud-based delivery can be complemented with an e-commerce purchase and provisioning system to access new geographic regions and new customers through a 24×7 web presence which allows customers to select, purchase and use an exchange’s data products without a local sales organization, or any sales department intervention at all.

Increase Revenue by Expanding Retail Channels

A scalable, low-cost cloud platform allows exchanges to distribute exchange data directly to retail websites and investors. By accessing these channels directly, exchanges open revenue streams currently outside their reach and strengthen their brand within new and existing markets.

Deliver New Products Faster to Meet Emerging Customer Needs

New trading products create new information needs for financial services firms and market participants. The cloud allows exchanges to provide immediate global access to new market data products, removing constraints imposed by traditional market data vendors and eliminating infrastructure investments by customers.

Increase Market Transparency for OTC Derivatives and Complex Products

New exchange-traded products and new reporting requirements brought forth by Dodd-Frank, RegNMS and MiFid create new information needs for financial services firms and market participants. A cloud solution can provide both market participants and regulators easy global access to activity in these markets, providing accurate and auditable information that helps financial institutions manage risk and meet regulatory and internal compliance requirements.

The Developer Focus series profiles developers who have built applications using Xignite’s Cloud APIs, highlighting their experiences, observations, and advice.

John Morris is the lead developer and CTO for StockTouch, an innovative financial market data visualization app that is currently the #1 paid financial app in the iTunes Store (read more about StockTouch in our recent Customer Spotlight).

StockTouch’s groundbreaking data visualization interface has received widespread attention from the media and from Apple, who have selected StockTouch as one of the few apps to be loaded on all iPads and iPhones in their Apple Stores worldwide. We recently sat down with John to learn more about him and the development process behind StockTouch.

Q: Tell us a little bit about your background?

John Morris – I’ve been programming since 1981, mostly as a contractor. My primary focus has been the gaming industry, mainly for two reasons. First, I love games! But perhaps more importantly, game development requires optimizing code at all levels in order to ensure good performance. It’s a challenge I enjoy taking on.

Q: How did you get involved with StockTouch?

John Morris – I learned about the StockTouch project through a mutual friend. I joined the project initially as a consultant to build the prototype. As everything came together, I was offered and accepted a position as the co-founder and CTO of StockTouch.

Q: How is the development process for StockTouch structured?

John Morris – The StockTouch team is completely virtual with the core members in New York, Seattle and Los Angeles. There are actually only two of us who are involved on the coding side of StockTouch. I work on the client side, and we have a contractor who works on the server side. We also work very closely with the other members of the team that deal with the overall user experience and feature set.

To build StockTouch, I was initially given just six screenshots, but we always had a pretty clear vision of what we wanted to do. We wanted the app to have a very intuitive interface with a fluid user experience. As a game developer, I personally wanted the application to offer a superior and unique visual experience, backed by extremely powerful features. StockTouch performs its rendering via OpenGL, and we really push the graphical capabilities of the iPad to deliver a cutting-edge experience.

Q: How difficult was the transition to the iOS platform?

John Morris – I’ve been building iOS products since 2008, so it wasn’t an issue for me. For programmers unfamiliar with iOS, transitioning to the platform is not very easy—there’s a steep learning curve involved. There are hurdles to development, such as the preferred language being Objective C, dealing with a new set of APIs and navigating the performance and memory limitations. That said, if you manage to get past the learning curve, it provides a very powerful and robust platform.

Q: Any advice for iOS developers?

John Morris – I think a lot of developers are getting it wrong when they try to build for iOS. They just try to push versions of desktop applications out. Developers need to look at the platform as a whole. It’s a different paradigm. The usability is totally different from a desktop or a laptop. The touch and gesture features are very powerful tools. They define the iOS experience, so you have to think in terms of the mobile environment.

Q: How did the Xignite Cloud APIs help you?

John Morris – Our forte is the front end. We’re a small company and wanted to stay lean. Xignite is our one stop shop for all the financial market data we need. The main advantage Xignite offered us was that the market data is already sitting in a large persistent database.

We just make web service calls, parse it and then pass the data on to our users. That reduced our requirements for the server side, enabling us to shorten our development timelines. Plus, working with a single data source with a solid and consistent API structure is definitely a good thing.

Q: How do you plan to meet the requirements of your fast growing customer base?

John Morris – For a financial application, the primary concern is ensuring that users get access to timely financial market data the instant they want to see it.

To address this, we have implemented several custom compression/encoding algorithms. This ensures that our data is distributed in a timely fashion and we can meet the requirements of a large and growing user base.

Q: What are StockTouch’s plans for the future?

John Morris – Going forward, we plan to beef up the application. We would like to add more analytical features, statistics like alpha, beta and candlestick charts. New security classes such as ETFs are on the way as well. We’re also looking to add layers of personalization so that users can view analytics specifically related to their own portfolios.

Ultimately, our goal is to come up with a suite of financial applications, based on our data visualization competency, that are easy to use and help spread financial awareness for both professional and non-professional users.