XLQ: Excel Spreadsheet Add-In

Without a doubt, the Internet has provided a tremendous boost to individual investors in terms of the amount of information we have at our disposal. Bring up almost any investment Web site and you have access to stock quotes, fundamental ratios and multiples, financial statement data, earnings estimates, and more.

For me, however, one of the biggest failures of the Internet has been the lack of this same data in a form where the end user can manipulate it in either a database or spreadsheet program for free. To be sure, many financial Web sites, such as Yahoo! Finance and MSN Money offer free historical price data going back many years. However, you are only able to pull up one ticker at a time and this lack of “batch processing” makes it time-prohibitive to rely on these sites for maintaining a database on many stocks.

However, one of my favorite Excel spreadsheet add-ins allows you to retrieve free end-of-day price and volume data as well as subscription-based real-time data: It is XLQ from QMatix (www.qmatix.com). In this installment of the Spreadsheet Corner, we walk through an overview of the program and some of the ways in which you can use it in your financial analysis.

XLQ Overview

XLQ delivers live and historical stock, index, mutual fund, option, future, or currency data pulled from either free or subscription-based Internet data services (the type of instruments and data provided varies from vendor to vendor). Beyond retrieving data, it provides you with a set of formulas that you can use to design your own portfolio manager and stock analysis spreadsheets.

XLQ comes in two versions—XLQlite and XLQplus. The versions cost $79 and $129, respectively; however, you can download and use the more robust XLQplus for free for 45 days. A paid subscription to XLQ entitles you to a year’s worth of program updates. After the first year, you can renew your license each year at a discount (currently $54 for XLQplus and $31 for XLQlite). Also, AAII members receive a $25 discount the first time they order XLQplus by using the code AAII-SIP.

The biggest difference between XLQlite and XLQplus is the support of third-party software—including AAII’s Stock Investor Pro—and additional data vendors. Furthermore, XLQplus includes XLQcompanion, which is a pre-built portfolio tracker. If you are looking to use Yahoo! or MSN as your data source, you will be fine with XLQlite. But XLQlite also lacks the more advanced formulas found in XLQplus.

XLQ supports Windows 2000 and Vista 32-bit and 64-bit, as well as Excel 1997 or later, although it is optimized for Excel 2002 to take advantage of improved functionality related to real-time data collection. Furthermore, XLQ can view XLQ data in any program or programming language supporting Microsoft’s Component Object Model (COM) technology.

Getting Started

XLQ comes with a variety of resources to help you get a grasp of the program. There is a help file with examples on how to create your own formulas in Excel and how to use the COM interface to use XLQ with Visual Basic and C++ programming languages. All support is done via E-mail, but responses generally come within a day. Also, there is a Yahoo! XLQ users’ forum where you can interact with other users and ask questions of the developer.

Data Sources

One of the true advantages of XLQ is its ability to pull data from free sources as well as subscription-based services. In effect, it is a “data center” that retrieves and stores real-time intraday and historical end-of-day data and then populates Excel spreadsheets via its unique formulas.

Currently, these are the data sources you can use with XLQ:

• Yahoo!—free historical data and delayed intraday data
• Yahoo!-RT—streaming real-time data for use in XLQplus for $13.95 a month (30-day free trial) (billing.finance.yahoo.com/realtime_quotes/signup)
• Yahoo!-EU—free European data from European Yahoo! sites
• Yahoo!-UK—free data from Yahoo!’s UK site
• Yahoo!-Int—free data from all other international Yahoo! sites
• IQFeed—Subscription-based streaming real-time data for equities, options, and futures for use with XLQplus (www.iqfeed.net/XLQ/)
• Interactive Brokers—For those with an account, streaming real-time data covering equities, options, and futures for use with XLQplus (www.interactivebrokers.com)

View Data

XLQ stores its data so that you can view it without having to use Excel, although you cannot manipulate the data within XLQ to perform calculations.

When you open XLQ, you should see this symbol in the notification area or system tray of the Windows task bar. When the status indicator is green, it means XLQ is active and receiving data. A yellow indictor means that there is no active connection to the Internet and the last attempt to retrieve data failed. Lastly, a red indicator means that XLQ is currently paused and no new data is being retrieved.

Also, there will be times when you see a red dot within the indicator, like this: . This means that the historical data is out of date. The red dot will clear when the historical data is up to date.

By right-clicking on the XLQ status indicator in the notification area () you can choose the View Data menu, where you can see a Day List, Historic View, or Day View, each of which show detailed stock data in various formats.

Day List

The Day List shows nearly 100 pieces of “day quote” data for all available stocks for the chosen data source; you can retrieve approximately 40 pieces of data using Yahoo! or MSN. These include last price, bid/ask, last trade size, last trade time, high and low price for the day, trading volume for the day, short ratio, and a variety of price multiples, including price-to-sales, price-to-book, and PEG ratio (ratio of price-earnings ratio to earnings growth rate). You can sort the data by double-clicking on any of the column headings and customize the layout to specify which columns are displayed. You can also copy and paste data from Day List, but the copy command does not appear to capture the column headings, which is an annoying inconvenience. A better means of exporting data is to create a CSV comma-delimited file for the symbols you are tracking with XLQ. You can use these files directly in Excel or any other program that can import comma-delimited data files.

Day View

The Day View shows the same “day quote” data found in the Day List viewer, but only for the stock selected. Figure 1 shows the XLQ Day View for Jos. A. Bank JOSB, providing a nice “snapshot” of the daily activity in an individual stock.

Historic View

The Historic View shows all the daily, weekly, and monthly historical data available for a selected company. Just like with the Day List, you can sort data by double-clicking on the column headers. You can also copy and paste data from the view but, again, the column headers are not copied. Again, however, XLQ can create comma-delimited data files that include this data for each of the stocks you track.

With the Historic View, you also get a variety of technical data, such as exponential moving average EMA, moving average convergence divergence MACD, average true range, fast and slow stochastics, and Bollinger bands. Within the XLQ preferences, you can choose the default number of periods over which many of these indicators are calculated.

Also, within XLQ’s preferences, you can specify the number of days, weeks, and months of data to retrieve. With MSN historical data, you can retrieve and store daily data, in general, back to January 1970, weekly data back to July 1990, and monthly data back to January 1962.

XLQ and Microsoft Excel

With XLQ, you can use specialized formulas to pull live and historical stock data directly into Microsoft Excel. By default, XLQ will automatically start whenever you run Excel. If you have an active Internet connection, XLQ will automatically retrieve new data, and any formulas in your spreadsheet using XLQ data will automatically adjust to the new data.

Day Quote Formulae

Figure 2 shows the formula examples available to populate the most recent data into an Excel spreadsheet—Day Quote Formulae. While the formulas may look intimidating, they do not require any programming knowledge. If you know your way around a spreadsheet, you shouldn’t have any problems with the XLQ formulas. With a little patience, you will come to understand the logic.

Here is an example of a “day quote” data formula. As with any other functions or formulas in Excel, you begin with the equals sign (=):

=xlqPrice(“BAMM”)

Displays the last trade price for Books-a-Million from the default data source (Yahoo! or MSN) set in the XLQ preferences

However, you can also specify the source in the formula, perhaps overriding the program default:

=xlqPrice(“BAMM”, “MSN”)

Displays the latest trade price for Books-a-Million retrieved from MSN

For day quote data, you will be using MSN or Yahoo!, including Yahoo!’s international sites.

Historic Quote Formulae

Historical quote formulas will retrieve prior-day data for a symbol from a specific source. For this data you could be using Yahoo!, MSN, or subscription-based accounts from IQ Feed or Interactive Brokers.

With the historical formulas, you can retrieve historical data from specific dates or from a specific number of periods ago. You can also identify the highest or lowest intraday price (as well as highest closing price) for a symbol over a specified period of time. As with the Historic View, there are also formulas for numerous technical indicators.

The historical quote formulas differ slightly from the day quote formulas:

=xlqhFunction(“symbol”, “date reference”, “source”)

Such as with this formula:

=xlqhClose(“bamm”, 10, “msn”)

Displays the closing price for Books-a-Million 10 trading days ago, as retrieved from MSN

or:

=xlqhClose(“bamm”, 8/3/2009, “msn”)

Displays the closing price for Books-a-Million on Monday, August 3, 2009, as retrieved from MSN

You can also use two data references to specify a range of dates, as in this example:

=xlqhHighestClose(“bamm”, 10, -5, “msn”)

Displays the highest closing price for Books-a-Million between the range of 10 trading days and 5 trading days ago, as retrieved from MSN

Portfolio Tracking

If you are looking to track your portfolio without have to log into a Web site, XLQ offers a handy alternative. Figure 3 shows the “Portfolio & Streamer” worksheet from the XLQDemo.xls spreadsheet that you receive when you download and install the program. The beauty of XLQ is that you can make your portfolio tracking and stock analysis spreadsheets as simple or as complex as you wish.

While this spreadsheet contains quite a bit of data, the only data I had to enter for the upper Portfolio section of the spreadsheet was the Ticker, Purchase Date, Quantity Qty, and Fees. The spreadsheet, using both traditional Excel functions and XLQ formulas, did the rest.

The lower Streamer section serves as a type of watchlist, where all I had to do was enter in the tickers of the various stocks and indexes I wanted to follow and the XLQ formulas did the rest.

Conclusion

We have only skimmed the tip of the iceberg when it comes to all that XLQ can do. For $104 the first year (when you use the AAII discount), and $54 a year thereafter, XLQplus practically pays for itself in the data costs you save by being able to retrieve historical data from free data sources such as Yahoo! and MSN.

If you are a do-it-yourself investor who prefers spreadsheets to perform your financial analysis, or if you are looking for an inexpensive data source, XLQ may be what you are looking for.