CI Market Dashboard

We have put together a collection of market indicators and track them to help you gauge the direction of the market.





Computerized Investing > September 16, 2017

Updating Data in Excel With XLQ Plug-In

PRINT | | | | COMMENTS (1) | A A   Reset

by Robert Baran

XLQ, a Microsoft Excel plug-in created by QMatix, is integral to spreadsheet maintenance and analysis here at AAII. XLQ brings real-time and live data into your spreadsheet. The tool functions as a time saver and great utility tool in keeping your market data up-to-date.

This article offers a brief explanation of XLQ and how to use XLQ to pull data into Excel. XLQ may also be used through VBA (the programming language of Excel) to create custom formulas and analysis routines as well as via Excel charts; this type of configuration is not covered here.

XLQ’s developer, Leo van Rijswijk, got his start at a local computer shop in 1984 writing programs after school for local businesses. Progressing from local college programmer to specialized software developer, Rijswijk was able to quickly advance his knowledge in program design. He specialized in C++ software development and developed several management reporting programs that have been implemented internationally. Rijswijk used his experience in C++ development and previously developed products such as QMan, QStar and QRep, to start XLQ. The tool became widely available in 2000. More information about how Rijswijk got started, and about him in general, can be found here.

Pricing and XLQ License

XLQ has two different licenses available for purchase. The premium version, XLQPlus, is priced at $149 and includes the XLQ Companion application (described below), a one-year license and support for AAII’s Stock Investor Pro, IQFeed, Interactive Brokers and TD Ameritrade. A majority of data sources require XLQPlus. The standard version of XLQ, XLQLite is priced at $89 and includes a one-year license and limited formula availability, including Google Finance and Yahoo Finance. A free-trial is available for XLQ that includes much of the XLQPlus subscription; a refund is not offered for either of the licenses due to the availability of this free trial. Each data source will be discussed separately in the following section.

Data Retrieval

XLQ draws market data from several data sources, some of which require separate subscriptions. The six data sources are: AAII’s Stock Investor Pro, Yahoo Finance, Google Finance, TD Ameritrade, DTN’s IQFeed and Interactive Brokers. Both Yahoo Finance and Google Finance are free market data sources and can be utilized with XLQLite and XLQPlus. AAII’s Stock Investor Pro, IQFeed, TD Ameritrade and Interactive Brokers require separate memberships, accounts or subscriptions as well as a license for XLQPlus in order to be used together.

Stock Investor Pro is a fundamental stock screening and research database that costs $198 per year for AAII members. For more information click here. The TD Ameritrade data source requires a brokerage account with TD Ameritrade. A standard individual TD Ameritrade brokerage account requires no minimum funding; margin or option privileges require a minimum of $2,000. IQFeed has a startup fee of $50 (which may be waived as a part of purchasing the subscription with XLQ) as well as a monthly subscription fee of $78 per month for the IQFeed Core Service. IQFeed charges additional service fees for more advanced features including foreign exchange data and U.S. futures data. Interactive Brokers requires a brokerage account to access both Interactive Brokers’ proprietary research database and XLQ.

XLQ draws market data from the six data sources and pulls it into Excel using formulas. These formulas are classified under three categories: day formulae, historic formulae and intraday formulae. AAII formulae is a fourth category for those using Stock Investor Pro. The day formulae allow users to work with the most recent quote information and can be updated in real time, depending on the data source. The amount, speed and accuracy of the information is dependent on the source used. For instance, Yahoo Finance provides delayed snapshot data, while sources such as Interactive Brokers, IQFeed and TD Ameritrade provide real-time data. IQFeed provides “millisecond precision” data and covers equities, indexes, options, futures and foreign exchange. Interactive Brokers provides similar real-time data but not with millisecond precision, and TD Ameritrade only provides real-time data for U.S. equities and indexes. Additionally, each source’s data is relative to which exchanges and products they cover or provide. Data sources may vary in the day formulae available, but all day formulae begin with the same syntax:

=xlqFunctionName(“Symbol”,“Source”)

For example:
=xlqprice(“AAPL”,“google”)

If you leave the “source” part of the formula blank, it will automatically use your default data source. Your default can be set within the program.

 

A list of the day formulae available in each data source is shown in Figure 1. XLQPlus has the most available formulae. An additional way to view day data directly is in XLQ itself by going to the “Q” on the task bar, once XLQ is open, choosing “view data” then choosing “day list.” This is a separate process and tool apart from using the formulae within Excel. As shown in Figure 2, you can add a symbol and then select the data source and the type of data you want to be shown in this table.

 

Once you have the symbols of the stocks in your portfolio or data you wish to be shown, you can return to the “view data” tab and select “day view.” This will show you an easy to view table of data for the symbol(s) you have selected. Additionally, each data source may have additional parameters required when referencing specific symbols for equities, options, futures or foreign exchange. In order to know the symbols used in either Yahoo or Google Finance, it is best to go to those separate websites and look up the item you would like to quote and retrieve the symbol from there. For example, Interactive Brokers requires their symbols to include the exchange, type and currency to be suffixed at the end of the symbol, like this:

=xlqPrice(“AAPL[NASDAQ,STK,USD]”,“ib”)

IQFeed also has a symbol list that can be found on the symbol guide on the IQFeed website. TD Ameritrade account holders can find the symbols to the items they are searching for within the research database on the TD Ameritrade website. Figure 3 displays the indexes within XLQ and their respective symbols for each data source, except for AAII Stock Investor Pro, which utilizes the separate symbols that can be found in the AAII demo, in the XLQ folder in your program files, once you have downloaded and installed XLQ.

Option symbols are available for Interactive Brokers, IQFeed and TD Ameritrade and are shown in Figure 4 with their respective basis symbol as well as details that explain the symbols. Symbols for AAII’s Stock Investor Pro are not specifically stated as being different or dissimilar from the standard base symbols of the New York Stock Exchange and a table of symbols for AAII Stock Investor Pro is not provided on QMatix’s website. Additionally, symbols for AAII Stock Investor Pro can be found in the demo if both an XLQPlus license and AAII Stock Investor Pro subscription are purchased.

 

What XLQ calls historic formulae allows users to work with basic market data for prior dates. Similar to the data sources with regard to day formulae, historic formulae availability differs between the data sources. Google Finance provides daily historical data while Yahoo Finance provides daily, weekly and monthly historical data. In Yahoo Finance, “adjusted close” is adjusted for dividends. TD Ameritrade provides daily, weekly and monthly data for U.S. equities and indexes. Interactive Brokers provides daily and weekly historical data including equities, indexes, options, futures and foreign exchange. IQFeed provides daily, weekly and monthly historical data including equities, indexes, futures, options and foreign exchange.

In addition, as with the day formulae, the source data available is relative to the exchanges and products that they cover. The historic view displays information based on the symbol and data source selected and is available under the historic view tab in the XLQ application under “view data.” There are three different historic formulae: historic formulae, historic formulae with additional parameters and historic range formulae.

Each historic formula begins with the same syntax:
=xlqhFunctionName(“Symbol”,“Date Reference”,“Source”)

For example:
=xlqhClose(“AAPL”,“9/01/2017”,“Yahoo”)

The date reference is also available in weekly and monthly form (for the sources that provide these time periods) by changing the syntax of the formula to “xlqhw” and “xlqhm,” respectively. The more advanced historic formulae are only available with XLQPlus.

The second historic formulae group allows for additional parameters to be set. The number of additional parameters will vary and can be set by the function “fx” option within Excel and when creating the formula. Historic formulae with additional parameters all begin with the same syntax:

=xlqXhFunctionName(“Symbol”,“date reference”,“any additional parameters”,“Source”)

For example:
=xlqXhSMA(“AAPL”,-1,20,“Yahoo”)

The second historic formula also has versions available that allow for weekly and monthly calculations, which begin with “xlqxhw” and “xlqxhm,” respectively.

The data in the third historic formulae group, which allows for two date reference parameters as well as additional parameters, begins with the basic syntax:

=xlqRhFunctionName(“Symbol”,“Start Date Reference”,“End Date Reference”,“any additional parameters”,“Source”)

For example:
=xlqRhMean(“AAPL”,“09/01/2017”,-1, 3,“Yahoo”)

 

 

A list of date and data ranges for the third historic formulae can be found in Figure 5. A partial list of “xlqh”, “xlqxh”, week/month, xlqLite and xlqPlus formula availability can be found in Figure 6; the full list can be seen here. Additionally, some of the formulas have Type parameters, which are used by average, standard deviation and other formulae in the historic formulae category; these can be found in Figure 7. Separately, Flex Average Types can be found in Figure 8 and work with the formulas concerning averages, slope, intercept and standard deviation.

 

 

The intraday formulae allow you to work with historic tick or X minutes bars of data. The preferences can be changed in the XLQ preferences under “data settings.” The calculated intraday formulae start with “xlqxi.” All intraday formulae require a sequence parameter starting from 0 (most recent) until 2000 (oldest). A list of intraday formulae availability between XLQLite and XLQPlus can be found in Figure 9 and also on the QMatix website under resources, formulae help and intraday formulae.

 

Last but not least is the AAII formulae. The AAII formulae require an AAII Stock Investor Pro subscription to utilize. A basic formula extracts data and is given in the following formula:

=xlqAAII(“Symbol”,“Field”,“Period”,“Database”)

For example:
=xlqAAII(“AAPL”,“CashFlowQ_X”,2)

A vast amount of data fields is available in the AAII demo, which is included in your XLQPlus license, if you have a subscription to AAII Stock Investor Pro.

Compatibility, XLQ Companion and Demos

XLQ is solely developed for and operated on the Windows operating system and there is no compatibility with MacOS operating system. XLQPlus includes demos of a variety of functions within XLQ. The AAII demo demonstrates and exhibits how AAII Stock Investor Pro functions through XLQ and the formulae that are available. A subscription to AAII Stock Investor Pro is required for the demo to become available. The basic XLQ demo demonstrates how to use the program and lists each formula with examples of use.

XLQ Companion is an application similar to the day and historic view within the taskbar XLQ application. It allows for the user to input symbols based on a data source and create and manage a portfolio straight from this application. XLQ Companion is included in XLQPlus. Figure 10 displays a set of symbols in XLQ Companion; more can be found on the QMatix website. QMatix also includes an FAQ section as well as “solve a problem” section under the support category for any known or experienced issues with XLQ that a user might have or that a user might have a solution for.

 

Conclusion

XLQ is a program that offers users the ability to save time creating and maintaining their spreadsheets. This article only scratches the surface of what is available within XLQ. The free trial (available for 45 days) offers users ample opportunity to become accustomed to the program and decide whether or not it suits their needs. The issue arises that the separate data sources require separate subscriptions or brokerage accounts in order to be used in tandem with XLQ. As a user of AAII’s Stock Investor Pro, I recommend XLQ as a complimentary application to Stock Investor Pro as it is able to pull data directly from the AAII database and make it available to you. Overall, XLQ serves as a premium application that replaces manual spreadsheet maintenance and helps the user create a better and more reliable spreadsheet. More information on XLQ, its subscriptions and related subjects can be found at www.qmatix.com.


Discussion

Bud Sloan from NV posted about 1 month ago:

Can the XLQ Plug-In be used with EXCEL compatible spreadsheet tools such as OpenOffice Calc and LibreOffice Calc?


You need to log in as a registered AAII user before commenting.
Create an account

Log In