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 most widely-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.
What’s the best way to integrate market data into Microsoft Excel? Later in this article we’ll explain why a cloud-based market data solution provides an optimal combination of ease-of use and value. But first, let’s take a look at the most common ways market data can be integrated into Microsoft Excel.
Traditional means of integrating market data into 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 vendor’s Microsoft Excel Add-in
Let’s 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 doesn’t address the need for real-time data.
Finally, importing data via a vendor’s Microsoft Excel Add-in. This method is preferable to the other mentioned above, as there’s 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 delivers superior 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 that’s required is a few lines of code and an internet connection. Not a programmer? Not a problem—even 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 format—great 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 that’s 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 to import XML into Excel, check out our article How to Import Market Data into Excel Using XML.