MarketXLS Review

Sometimes I just want stock market data in Excel, and I don’t want to spin my wheels hacking about with VBA.

I’ve written VBA code for scraping market data from Yahoo Finance, Google and other websites, sometimes even with moderately successful results.

But I’ve never been completely ecstatic with the solution because I have to deal with too many problems, including

• ever-changing APIs,
• functions that no longer work because data is in a different location on a web page,
• slow updates for bulk data requests
• and many other headaches

I don’t always have the time to rewrite existing code, or prototype new UDFs (I have a job, a wife, two-year old son and another kid on the way. I’m lucky if I have a spare hour each night before sleep – time is a precious commodity). And frankly, copying and pasting data from websites seems so clunky and archaic.

So I surveyed the market for professionally-developed stock market data add-ins for Excel, and I evaluated several.

I eventually ended up buying MarketXLS, developed and supported by a clever group of people at Technitya. It provides over 90 functions for scraping stock market data from Yahoo Finance, and offers lifetime updates as a part of the initial cost.

You can see me use MarketXLS in this video.

If you’re familiar with the Yahoo Finance API, you’ll know that you can extract many items of financial information for a company. So things like

• Market capitalization
• Last trade price, and ask/bid prices
• EPS estimates
• Price/book value
• and more

You get data for stocks, mutual funds, bonds, and options.

Installing and Navigating MarketXLS

The software is sold as a digital download. After you install the add-in and start Excel, you’ll find a new tab.

There’s a handy link to a website with the latest updates (MarketXLS is regularly updated). The add-in checks for updates every time you load Excel, but you can disable this behavior via the Settings menu.

“Refresh Selected” and “Refresh All” are pretty obvious – they update all data that MarketXLS has downloaded with the most recent values.

This is quick because MarketXLS is multi-threaded – so multiple links to Yahoo Finance are initiated (don’t worry – this doesn’t slow down your computer).

MarketXLS provides over 90 new functions. I won’t give them all here, but this spreadsheet lists them all. All the functions are listed in the Formulas > Insert Function menu under three categories.

• Stock Quotes (89 functions)
• Mutual Funds (5 functions)
• Stock Options (8 functions)

A fourth category lists functions that give license information etc,

Downloading Market Data

You give each of the functions a stock ticker symbol and hit enter.

So let’s say I wanted the EPS estimate for the current year for KKR & Co LP (a private equity firm). I simply type

=EPSEstimateCurrentYear(“KKR”)

into a cell and press enter.

The function retrieves the data from Yahoo Finance and places it in the cell. It doesn’t get any more complicated than this.

You’ll need the Yahoo ticker symbol. But fear not, I’ve assembled a comprehensive list of tickers in a spreadsheet.

All the functions are available via the command completion menu. Just type the first few letters of a function name, and all the matches appear.

That’s a real time-saver, and reduces the cognitive load on my rapidly decreasing brain mass.

Some data, like the last trade price, is continuously updated during normal stock exchange hours. You can refresh this data with the latest values by hitting Refresh in the MarketXLS tab, or by pressing Shift+F9.

You can also download option prices – the ask, bid and strike prices, expiration date, volume and open interest.

I’ve already mentioned that MarketXLS is multi-threaded. It’ll open up multiple calls to Yahoo Finance at the same time. This mean retrieving data is fast. I’ve downloaded data for over 200 tickers – this took a few seconds.

A Final Few Words

In case it’s not clear, I’m pretty happy with MarketXLS. I can now concentrate on manipulating financial data, valuing stocks and making investment decisions, rather than hacking around with VBA or copying/pasting data from websites.

The lifetime free updates means I never have to worry about outdated software.

You can buy MarketXLS here. I’ve negotiated a 20% discount with the developer.

Disclaimer. I purchased MarketXLS with my own money. I now sell MarketXLS as an affiliate, but this has not affected my opinion of the software. I have no other connection with the developer.