Remember me

Register  |   Lost password?

The Trading Mesh

Seamlessly Integrate Market Data into Microsoft Excel

Mon, 23 Apr 2012 12:19:39 GMT           

Despite the proliferation of market data platforms and custom internal applications that have been designed to replicate or replace Microsoft Excel over the years, Excel remains arguably the mostwidely-used mission-critical application for financial services industry professionals.

Microsoft Excel spreadsheets and models are employed across a wide range of processes, from investment research and portfolio management to currency trading and loan processing, and just about everything in between. The common thread? They all require financial market data as an input.

Whats the best way to integrate market data into Microsoft Excel? Later in this article wellexplain why a cloud-based market data solution provides an optimal combination of ease-of use and value. But first, lets take a look at the most common ways market data can be integrated into Microsoft Excel.

Traditional means of integrating market datainto Microsoft Excel

There are three methods that are commonly used to import market data into Microsoft Excel, each with key drawbacks:

  • Downloading market data from public sources via CSV files
  • Scraping market data from public websites
  • Importing market data using a third-party vendors Microsoft Excel Add-in

Lets consider each of these methods, starting with downloading market data from public sources. Unless you enjoy downloading dozens of files from various websites and manually copying and pasting data each and every time you need it updated, this time-consuming method is not feasible for the vast majority of financial services professionals. In addition, the quality of market data pulled from public sites can be an issue, particularly for processes such as investment analysis where real assets are on the line.

Next up, scraping market data from public websites. This legally questionable practice involves creating automated processes to pull data from publicly-available resources, often violating the terms of service of the originating websites. Setting aside the legal implications and data quality issues, scraping is a time-intensive project that is subject to outages depending on the availability of the source website. In addition, scraping doesnt address the need for real-time data.

Finally, importing data via a vendors Microsoft Excel Add-in. This method is preferable to the other mentioned above, as theres less manual effort and the data is of higher quality. However, this is where cost becomes a major factor. To access market data via Microsoft Excel, vendors typically require you to purchase market data terminal licenses, despite the fact that you may only need the data within Excel.

In addition, if you only require a limited scope of market data, with Microsoft Excel Add-ins, you still pay a premium for a wide range of data you will never use. This problem is compounded if you require data on multiple asset classes and have to deal with more than one vendor.

How a cloud-based market data solution deliverssuperior results for Microsoft Excel users

In contrast to the aforementioned methods, a cloud-based market data solution offers a powerful combination of ease-of-use and value. With a cloud-based solution, all thats required is a few lines of code and an internet connection. Not a programmer? Not a problemeven those without a programming background can be up and running in just a few minutes.

A cloud-based solution uses Web service APIs that return data in XML formatgreat news for Microsoft Excel users, because Excel has fantastic XML support. XML allows you to go beyond the simple file import available with CSV to create direct links to Web services that allow you to pull and update real-time market data from within Microsoft Excel.

And finally, market data pulled from the cloud is priced on an on-demand basis, where you only pay for the data thats used. No licensing fees for market data terminals and no extra fees for the Microsoft Excel Add-in functionality. This represents a sizeable savings for those whose workflows tend to be primarily dependent on Microsoft Excel.

This is the final post in our Top 5 Investment Management Technology Challenges Addressed by the Cloud multi-part blog series. Check out the rest of the series in our Investment Management Community.

For more information on how easy it is toimport XML into Excel, check out our article How to Import Market Data into Excel Using XML.

, , , , , , , , , , , , , , , , ,