There is now an emerging trend towards “suit-to-fit” internal custom applications that focus on the unique requirements of various departments within a firm. These applications are often fueled by on-demand market data delivered over the cloud. When executed well, this combination delivers superior workflows that suit the firm’s exacting needs, while also reducing overall market data spend.

Why are market data terminals out of step?

Market data terminals don’t align with the evolving business needs of investment management. Terminals target the fat part of the bell curve, but as firms increasingly look to differentiate themselves with new strategies their needs become more unique, the curve flattens, and terminals become less relevant. In addition, in terms of data management, firms are moving towards a holistic and scalable approach that provides increased consistency and transparency, leaving behind legacy market data silos.

Of course many firms have been working on this challenge for years now, creating in-house data management solutions to serve the needs of internal and external constituents. When these systems take hold, it enables firms to focus on improving internal workflows, from pre-trade analysis and trading, to reporting and compliance. As custom applications for each of these areas are developed, it decreases the need for market data terminals.

Another important factor to consider is the outmoded pricing structure of market data terminals. The recent economic downturn provided a catalyst for firms to re-examine the value terminals provide. While vendors have added a wide range of datasets to their terminals over time, that data comes at a steep price. Subscription costs for industry-leading market data terminals now typically run from $10,000 – $20,000 per user per year.

This one-size-fits-all pricing structure is expensive and not terribly customer friendly.  Traditional market data terminal pricing structures fail to account for the fact that:

• many users only require access to a limited range of datasets
• many users only require a limited overall quantity of data
• terminal subscriptions are often underutilized due to personnel turnover

With emerging technologies, this pricing structure is likely not sustainable as it benefits the entrenched business models of market data vendors rather than their customers.

The rise of custom on-demand applications

One of the key benefits of new internal data management systems is that firms can add a layer of custom proprietary applications to enhance a wide range of workflows, including research, portfolio management, trading, compliance, reporting, and more. With today’s advanced technologies and cloud computing, it’s easier than ever to develop in-house applications that replicate or surpass the analytical capabilities provided by market data terminals.

Take for instance the integration of market data. In the past, incorporating market data into proprietary applications was a massive headache, requiring the time-consuming parsing of antiquated data feeds and flat files. With a cloud-based approach comes flexibility. Data is available instantly, pulled in from the cloud via web APIs with just a few lines of system-agnostic code. No extra infrastructure required.

Also underpinning the trend towards proprietary applications is the very real cost savings versus market data terminals. Instead of an exorbitant terminal fee for every user regardless of their needs, on-demand market data delivered over the cloud is based on actual usage. This means that custom apps and portals designed to a firm’s exact requirements can result in much lower overall market data spend.

What does the future hold?

There’s no doubt that market data terminals will continue to have a place in the market data ecosystem for the foreseeable future. However, as firms differentiate with new strategies and emphasize the preservation of their margins, they will increasingly look beyond terminals to the efficiency and custom fit of proprietary applications fueled by on-demand market data from the cloud.

This is the third post in our “Top 5 Investment Management Technology Challenges Addressed by the Cloud” multi-part blog series. Be on the lookout for our next post, “Creating branded, data-rich mobile apps that differentiate.”

__________

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.

Our Customer Spotlight series showcases the innovative ways our clients are using Xignite’s Cloud APIs to power their applications.

Wealthfront is an innovative new financial advisory service that addresses the needs of a new generation of tech-savvy investors who seek an alternative, low-cost approach to managing their investments.

We recently sat down with Daniel Carroll, Wealthfront’s Co-Founder, to learn more about the Wealthfront service and approach.

Q:  Tell us a little bit about Wealthfront

Daniel Carroll – Wealthfront is an SEC registered online investment advisor that manages your investments for you so you can live your life. We provide sophisticated investment advice at 75% lower fees than traditional financial advisors.

We are backed by well-known investors including Marc Andreessen, Jeff Jordan, DAG Ventures, and retired partners from Benchmark Capital and Kleiner, Perkins, Caufield & Byers.

Q:  Why should people use it?

Daniel Carroll – Wealthfront offers clients an alternative to high cost traditional investment advisors.  We provide a simple to use, transparent, low-cost platform that uses Modern Portfolio Theory to manage your investments. We allocate your investment into 6 asset classes based on a risk score that is determined by a brief investment questionnaire. We recommend a sound portfolio and rebalance it for you when necessary.

Q:  How does Wealthfront differ from traditional investment advisors?

Daniel Carroll – There are two crucial differences. First is cost. Traditional financial advisors charge an average of over 1% while Wealthfront charges just 0.25%. We also don’t charge management fees on the first $25k invested.  Being a completely automated platform and removing the middleman allows us to offer dramatically lower fees.

Second difference is service – Most customers of traditional financial advisors have quarterly meetings with their financial advisors and frequent calls to check up. Wealthfront automates the handholding experience through leveraged communication platforms such as email, webinars, tools, and conferences. We’ve found that most of our clients actually don’t want to talk to anyone on the phone and prefer to do everything electronically.

Q:  Who are your target customers?

Daniel Carroll – Wealthfront targets 25 – 35 year olds who work in the tech community. Our product, voice and blog reinforce that focus.  Many of our clients have been approached by traditional financial advisors and are turned off by the high fees and the biased products.  They don’t trust these types of advisors they call “suits”.  Wealthfront provides an alternative to the suits and fills that void.

Q:  How does Wealthfront work?

Daniel Carroll – Here’s a great video that provides an overview of our approach.

Wealthfront- How It Works from Wealthfront on Vimeo.

Q:  You mentioned Modern Portfolio Theory.  What is that, and how can it help retail investors?

Daniel Carroll – Modern Portfolio Theory (MPT) attempts to maximize a portfolio’s expected return for a given amount of portfolio risk, or equivalently minimize risk for a given level of expected return, by carefully choosing the proportions of various asset classes rather than selecting individual securities. Almost every academic and investment professional believes MPT is the best approach to manage an individual’s portfolio.  Historically, rigorous MPT-based investment advice has only been available through high-end financial advisors who typically require minimum account sizes of at least $1 million and charge annual fees of at least 1% of assets under management annually.  Wealthfront’s Online Financial Advisor service makes it possible for everyone to enjoy cost-effective access to the benefits of MPT.

Q:  Tell us more about how Wealthfront manages rebalancing.

Daniel Carroll – The composition of your portfolio allocation will drift over time, as some of your holdings outperform others. Wealthfront continuously monitors your portfolio and periodically rebalances it back to your target allocation in an effort to manage your risk exposure and produce more stable returns for you.  In deciding when and how to rebalance, we carefully consider commission costs and tax implications.

A study performed by David Swensen, Chief Investment Officer of Yale University, found that rebalanced portfolios earned an average of 0.4% more per year over 10 years than portfolios that were not rebalanced. [Source: David Swensen, Unconventional Success (2005) 195]  You can find more information on rebalancing on our blog.

Q:  Why did you choose Xignite as your market data provider?

Daniel Carroll – First, I want to compliment Xignite on being a great partner.  Your service and support is of tremendous value to firms like ours who are looking to quickly integrate market data into a website without a great deal of overhead on the back end. Your flexible SOAP architecture is so easy to use and it was seamless to integrate into Wealthfront’s service. Choosing Xignite as our market data provider was a no-brainer.

Q:  We hear that you are having a conference in April focused on IPOs. Tell us more.

Daniel Carroll – The focus will be on IPOs, their significance, and how to get there. The event will feature discussions from CEOs and venture capitalists including Bill Gurley from Benchmark Capital, Sameer Gandhi from Accel Partners, Doug Leone from Sequoia Capital, Frank Quattrone from Qatalyst Partners, and more. Obviously, Wealthfront benefits from IPOs, too!  Many in the tech generation choose to invest their money with Wealthfront rather than with traditional financial advisors when their companies go public.

For a preview of the kinds of issues the conference will touch on, Wealthfront just released a video featuring several industry luminaries, including Eric Schmidt of Google, Bill Gurley of Benchmark Capital, Ben Horowitz of Andreessen Horowitz, and Frank Quattrone of Qatalyst Partners.

Great Perspectives on Tech IPOs from Wealthfront on Vimeo.

As if this wasn’t already a formidable challenge, making sense of the ever-increasing deluge of unstructured market data is adding to the complexity. Due to the recent economic crisis and subsequent market downturn, investment managers need to consider a wider array of information in their analysis while minimizing market data management overhead. Uncovering emerging trends in economics, commodities, options, short interest, and futures data can have a profound impact on individual securities and overall portfolio performance. In addition, managers may also need new data sets to explore the veracity of new investment strategies to keep up with competitive offerings.

To succeed, managers need to not only embrace the flood of market data, but transform it into an asset. This presents a huge market data management challenge. Attempting to deal with these intensifying data requirements exclusively with in-house market data management solutions is resource intensive and takes focus away from extracting insight from the data, a real source of sustainable differentiation.

How the market data cloud can help

The potential of cloud technology and how it can be applied to financial services has generated some buzz recently. Investment management firms have been understandably hesitant to host sensitive client data in a cloud environment due to security considerations. However, the concept of pushing commoditized market data information to the cloud is gaining traction. By leveraging a cloud-based market data solution, investment managers can gain instant access to rich new data sources to better inform their existing research process and to explore new strategies, while at the same time reducing market data management complexity.

For example, take the case of a CIO considering the formation of a new international strategy. Accessing the necessary data requires a steep international data access fee and ties the firm into an annual contract. And if this new data comes in the form of a traditional datafeed, the ensuing data infrastructure costs will start to add up quickly. In contrast, a cloud-based market data solution with on-demand pricing allows the research team to perform ad hoc investigations into the strategy’s potential at a much lower impact, both in terms of costs and resources.

Adding new data sources from the market data cloud is simple, and has a light footprint on existing market data management infrastructures. Investment managers can quickly integrate cloud-based market data into any internal application with easy to use APIs in a fraction of the time it takes with traditional datafeeds. This agile approach enables firms to quickly respond to new data requests from the research team and lessen the strain on market data management systems.

This is the second post in our “Top 5 Investment Management Technology Challenges Addressed by the Cloud” multi-part blog series. Be on the lookout for our next post “Reducing Market Data Expenses – How Custom Applications Can Supplant Market Data Terminals”.

__________

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.

A siloed approach where each department or group maintains its own market data solution is no longer feasible given the redundancies and overhead. Instead, to optimize transparency and rein in costs, firms must embrace a more holistic market data solution, one that powers everything from internal research portals and portfolio management systems, to compliance reporting and mobile applications.

Introducing the market data cloud

Cloud technology is beginning to revolutionize market data management, marking a radical shift in the way investment management firms consume data across the enterprise. Market data clouds enable firms to bypass the sizeable investments in infrastructure, hardware, software, and maintenance that are typically associated with traditional datafeeds. Utilizing this new type of market data solution helps firms gain economies of scale, lower costs, and increase agility.

Below are five key investment management technology challenges that are addressed by the adoption of a cloud-based market data solution. Future posts in this series will expand on each of these areas.

Investment management technology challenge #1

Transforming the deluge of market data into an asset

In the wake of the recent economic crisis and market downturn, it’s more essential than ever for investment managers to broaden the scope of their research efforts. This requires finding a way to incorporate new sets of market data into the investment process without getting bogged down in technical data management issues.

Accessing market data via the cloud enables investment management firms to incorporate broad sets of real-time and referential data quickly on demand within proprietary in-house systems and 3rd party applications. The market data cloud’s flexible, easy to implement delivery methodology reduces the frustration of dealing with traditional data delivery methods, enabling immediate access to a wide range of financial instruments to get new investment strategies up and running quickly.

Investment management technology challenge #2

Reducing market data expenses

As investment management firms look to cut costs in today’s highly competitive environment, market data expenditures are an obvious target. One of the largest outlays within this cost center is market data terminals, which don’t align with evolving business needs for data flexibility. Terminals target the fat part of the bell curve, but as firms increasingly look to differentiate themselves with new strategies their needs become more unique, the curve flattens, and terminals become less relevant. With desktop terminals, you pay a premium for data sets that you don’t use. Firms with multiple terminals pay for the same data multiple times.

In contrast, a cloud-based market data solution enables investment management firms to more easily develop custom research portals and applications that better address the exacting needs of each internal constituency. And the on-demand pricing structure of the market data cloud is based on actual usage. This means that custom apps and portals designed to a firm’s specific requirements can result in much lower overall market data spend than market data terminals.

Investment management technology challenge #3

Streamlining and simplifying portfolio and compliance reporting

Demands for improved transparency have made reporting infinitely more complex. New regulations have resulted in onerous reporting requirements that are made even more difficult by inflexible systems, and the inability to quickly integrate market data.

A cloud-based market data solution allows firms to instantly enrich their regulatory and investor reporting with comprehensive coverage of real-time, historical, and reference data. No infrastructure required.

Investment management technology challenge #4

Creating branded, data-rich mobile apps that differentiate

In an increasingly connected world, customers expect detailed real-time market information wherever they are, whenever they want it. One way to differentiate from the competition is to provide data-rich mobile applications that showcase a firm’s retail offerings and keep customers coming back for more. Problem is, integrating market data into mobile applications using traditional datafeeds requires extensive development.

A cloud-based market data solution is well-equipped to meet mobile application challenges head on. Cloud-based web APIs stream real-time and referential data to mobile apps seamlessly. With a market data cloud, firms can reduce the development cycle and launch Android and iOS applications in less time.

Investment management technology challenge #5

Seamlessly integrating market data into proprietary Excel models

Microsoft Excel remains a central application in the investment management workflow. Incorporating market data can be cost prohibitive using Excel add-ins from traditional data vendors, and these add-ins don’t always include access to the full range of data required.

A cloud-based market data solution seamlessly delivers information into Excel via flexible web APIs. All that’s required is a few lines of code and an internet connection. And with on-demand pricing where you only pay for the data that’s used, it can help investment management firms reduce market data budgets.

Our Customer Spotlight series showcases the innovative ways our clients are using Xignite’s Cloud APIs to power their applications.

AlphaClone is a San Francisco-based investment management firm that enables investors to “follow the smart money” with equity strategies that mirror the world’s top hedge funds. In addition to investment management services, AlphaClone also offers a powerful online research platform that investors can use to analyze stocks and to create and backtest their own cloning strategies.

Xignite sat down with Mazin Jadallah, Founder and Chief Executive Officer, to learn more about AlphaClone.

Q: Describe AlphaClone’s service offering.

Mazin Jadallah – AlphaClone is a SEC registered investment adviser and research firm. Our objective is to give investors actionable, cheaper, more liquid access to the investment ideas of the world’s top institutional investors. Our investment research approach, a process called “cloning”, utilizes top hedge fund public disclosures to construct and backtest equity investment strategies.

Q: Very interesting concept. . . does cloning actually work?

Mazin Jadallah – Yes, cloning the investment strategies of top money managers has been independently shown by academic and industry research to generate alpha returns. AlphaClone offers investors several core investment strategies inside managed accounts as well as a web-delivered subscription research service.

AlphaClone enables investors to backtest cloning strategies before investing real money.

Q: How are the managers/funds in the AlphaClone database chosen?

Mazin Jadallah – The funds in the AlphaClone database represent roughly 70% of the total market value disclosed by all funds that file 13F-HR forms. Our fund database generally contains the largest institutional funds and hedge funds as measured by the total market value disclosed on their filings. Most of the managers AlphaClone tracks are fundamentals driven, value-oriented investors. We tend to avoid funds that are owned or operated by prime brokers, those with large ETF businesses, and those with very high turnover.

Q: Describe the technology behind AlphaClone and the advantages of Xignite Cloud APIs?

Mazin Jadallah – AlphaClone uses Xignite to pull in real-time price quotes. Simply put, when we compared Xignite’s cloud-based model with others we found it to be more reliable, cheaper, and perhaps most importantly, simple to implement. It’s hard to imagine making it any easier to incorporate market data in an application. By relying on Xignite for standard market data, it allows us to focus our development resources on areas of our service that provide us with a differentiated advantage.

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

Mazin Jadallah – Besides enhancing the AlphaClone research service with new features and applications, we’re planning to offer investors new ways to access our disclosure-based hedge fund replication strategies. That includes things like ETFs and mutual funds which allow both individual investors and professional money managers easy, convenient, and cost-effective access to our investment strategies. As a technology-driven asset manager, our focus will always be on leveraging technology in new and useful ways to empower investors.

Let’s review each of these trends individually to understand how important the cloud will be for the market data industry in 2012:

1. Market Data Supplier Economics

The suppliers of market data are in a state of flux. On one hand, the cost of business is soaring with the new technology resources required to support sky-rocketing message rates, microsecond execution, and new regulation. On the other hand, the exchanges are experiencing sluggish revenue growth. Traditionally exchanges had four distinct sources of revenue: 1.) execution; 2.) listings; 3.) clearing; and 4.) market data. Of these, only market data is growing, while the others have either completely dried up or are not likely to be a significant source of revenue in the future. The exchanges have responded to these unfavorable economics with a wave of consolidation in an attempt to reduce costs but the health of the industry depends on growing the revenue side of the equation. The exchanges understand that their best revenue strategy is to distribute their most valuable asset, market data, direct to consumers. This strategy has already seen success with the exchanges offering direct feeds, co-location, and other services to their low-latency clients.

In 2012 we will see more exchanges begin to focus on the largely untapped segment of consumers who need market data, such as historical trade and quote data, but are not latency sensitive. This is potentially a huge revenue source for the exchanges and is ideally suited to the on-demand market data cloud. Forward-thinking exchanges such as CME DataCloud, Direct Edge EdgeBook Cloud, and NASDAQ Data-On-Demand, have already moved in this direction, but 2012 will be the year that many more exchanges embrace the market data cloud to sell directly to consumers.

2. Market Data Consumer Economics

As with the suppliers of market data, consumers are also facing an uncertain future. For many consumers, particularly in the financial services industry, the whole process of data management has become overwhelming. The old model of bringing all market data in-house, so that it can be accessed quickly, is under considerable pressure. There is now simply too much data to do this cost-effectively. Another related issue is time to implementation. With intense competition, many investment firms require immediate access to global and multi-asset class market data. Unfortunately, the traditional method of having a vendor add a feed can be a very slow process. Investment firms require a much more nimble solution that allows them to quickly access discrete data sets.

In 2012 we’ll see more firms conclude that not all data should be brought in-house. This change in mindset will lead firms to become much more discerning about what data should be stored locally, and what data should be retrieved on an ad-hoc basis from a market data cloud.

3. Proliferation of Mobile Devices

It is clear that we are in the midst of a technological sea changeas the world moves from an Internet that is tethered to desktop PCs, to one that can be accessed from anywhere, through a wide array of always-on smartphones and tablets. Many experts predict that by 2014 mobile Internet usage will overtake desktop Internet usage. Already in late 2011 we saw mobile-based local search overtake PC-based search. The mobile Internet revolution will affect the market data industry greatly as apps and websites designed specifically for these devices demand more and more market data. An on-demand market data cloud is ideally suited for this mobile revolution because mobile apps typically require only discrete on-demand data sets and app developers are not inclined to build their own data management infrastructures. In 2012 the market data industry will experience a huge uptick in demand from theses mobile devices.

(Read more about the proliferation of mobile devices as it relates to the buy-side The 3 Phase Evolution of Buys-Side Mobile Apps.)

4. Regulation

Regulation is always a factor in any discussion on trends in the market data industry. 2012 will be no exception, particularly because many of the regulations that were proposed in direct response to the financial crisis of 2008, will either be implemented, or will be at a point in the approval process where we will have a better sense of their likely impact. The main thrust of new regulations such as Dodd-Frank, the Volcker Rule, and Basel III, is to manage systemic risk and to force transparency across the financial services industry. All of the important industry players will be significantly impacted including the exchanges, the buy-side and the sell-side. Complying with these new regulations will involve onerous and costly reporting requirements. These regulatory requests for data will serve to accelerate the adoption of on-demand cloud-based market data solutions because many of the players simply do not have the resources necessary to comply with these and future regulations. It is also likely that regulators themselves will favor cloud-based market data because it will promote industry best practices and transparency.

2012 will certainly be a year to watch for the market data industry and the adoption of the on-demand market data cloud model. We can expect to see the 4 major trends outlined above to all converge to make this the year of the market data cloud.

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.

Our Customer Spotlight series showcases the innovative ways our clients are using Xignite’s Cloud APIs to power their applications.

Quantbench is a San Francisco-based start-up that combines a unique marketplace for non-traditional data sets, with a financial data analysis platform for financial analysts, academics and data scientists. This 100% cloud-based financial research platform provides its members with the data sets and tools necessary to conduct deep quantitative analysis.

Xignite sat down with Stefan Petry, Co-Founder and Chief Operating Officer, to learn more about Quantbench.

Q: Describe the Quantbench platform?

Stefan Petry – Quantbench combines a cloud-based platform for unstructured data exploration and analysis, with a marketplace where investment researchers and fund managers can purchase non-traditional data sets.

The platform includes data sets that we and others provide. Right now our members have access to more standard market data sets such as tick-by-tick and end-of-day stock data, and indices data. In time, we will be adding more and more non-traditional data sets that are sourced from areas like web search, online advertising, news, social media etc.  Users can perform analyses using R-Project and Amazon Elastic MapReduce. They can also purchase certain data sets in our marketplace for download and in-house processing.

Q: Describe the technology behind Quantbench and the advantages of Xignite Cloud APIs?

Stefan Petry – Perhaps the most innovative part of our approach is that we make it easy for our users to get up and running quickly. Everything that a financial analyst or data scientist needs to conduct quantitative analysis is instantly available. We are able to achieve this because the technology behind our community is 100% cloud-based. All of the analytical tools, data sets, and infrastructure are on the cloud. This approach also enabled us to launch very quickly, and will allow us to scale efficiently as our community grows.

We use Xignite’s market data cloud because it helped us to execute on Quantbench’s cloud vision. Xignite’s broad data coverage acts as a comparison or benchmark to the other more non-traditional data sets we make available to the Quantbench community.  As we expand we will add more Xignite data sets to our platform. Finally, we really like Xignite’s simple and transparent approach to purchasing market data. All you need to do is visit www.xignite.com, select the desired data set, place it in the Xignite shopping cart, and then pay using a credit card.

Q: What are Quantbench’s future plans?

Stefan Petry – Our platform is still in its early stages. We launched just in November. Broadly speaking there are three areas that we will be focused on in the coming months. First, we will be adding more data sets. This data will include standard data sets, as well as many non-traditional data sets that firms will provide to our user base for quantitative analysis. Second, we will be extending the functionality of our non-traditional data set marketplace. Third, we will be adding more tools and languages, so that our users can perform more powerful analyses.

Our Customer Spotlight series showcases the innovative ways our clients are using Xignite’s Cloud APIs to power their applications.

StockTwits is the leading social network targeted at the investment community reaching several hundred thousand users on StockTwits.com, and tens of millions through the StockTwits Distribution Network. Since its launch, StockTwits has garnered much praise, including being named by Fast Company as one of the 10 most innovative Web companies, and by Time Magazine, as one of the 50 best websites.

Xignite recently spoke with Francis Costello, Chief Operating Officer, and Chris Corriveau, Chief Technology Officer of StockTwits, to learn more about this fast-growing company.

Q: Describe the StockTwits community?

Francis Costello – StockTwits was founded in 2008 by Howard Lindzon, our CEO. At that time Howard saw the opportunity to create an investing community based around social media. The community allows investors to share their investing ideas in 140-character tweets, all in real-time. The value of StockTwits is very much based on the wisdom of the crowds concept, with 1000’s of our users all contributing to create a unique financial communications platform. StockTwits is fundamentally based on this social construct, and is very different from the more established financial media outlets such as CNBC, which involve traditional one-way broadcast communication.

Q: Why has StockTwits been so successful?

Francis Costello – I think there are two main reasons for StockTwits’ success to date. First, StockTwits created and popularized the “$(Ticker)” tag which allows users to follow a twitter stream based on a security ticker. This innovation is at the very center of all that we do. Second, we are very serious about maintaining the quality of the conversation that our community offers. We use both technology and people to curate and monitor the community. We ensure that all of our house rules are strictly enforced.

Both of these aspects mean that our content gets broad distribution across the Internet including CNN Money, Yahoo Finance, and Reuters. It also means that we have been successful in bridging the divide between non-professional and professional investors. Many professionals now use StockTwits’ social stream, to augment their more traditional, news and price streams.

Q: How has Xignite’s market data Cloud API’s helped StockTwits?

Chris Corriveau – StockTwits has obviously experienced huge growth in the last few years. Because of this we try to keep our development as agile and as lean as possible. We look for technologies and partners that will allow us to maintain this rapid rate of development. The Xignite service is so vital to us because Xignite’s real-time pricing provides our users with instant context for StockTwits’ social stream. We found Xignite’s Cloud API’s to be very easy to work with and implement, offering multiple data formats. We’ve also discovered, that as StockTwits expands internationally, Xignite’s broad global coverage means that adding international market data is a simple and straightforward proposition.

Q: What are StockTwits plans for the future?

Francis Costello – Apart from growing the community here in the US, and expanding overseas, our other priority is to introduce more services for our professional users. The main difference with the professional market is that while StockTwits has obvious appeal, the needs of professionals can be very different from our non-professional users. Frequently many of the markets these professionals operate in are highly regulated. In June of this year we launched StockTwits Investor Communications Solutions, which are services that allow public corporations and investor relations professionals, to really take advantage of the StockTwits platform to communicate directly with investors. The service includes features like compliance functions that address the unique legal challenges presented by regulated communications in social media. As we offer more and more services to professionals, we will continue to tailor our solutions to the needs of different groups in this market.