Latest XLQ Excel for automated trading

(Last Updated On: October 16, 2015)

Latest XLQ Excel for automated trading

Here is the latest news on this powerful software

Join my FREE newsletter to learn more about using this software for automated trading analysis 

The website, initially updated in June, now also has additional pages to reference formulae, symbols etc. Click Resources at the top of the web pages.

In addition to www.qmatix.com, it is now also possible to access the same site from www.xlqplus.com or www.xlq.fr.

 

Note: If your license has expired, you will need to renew it in order for this new version to function. Before downloading and installing you can verify your license by right clicking on the Q in the task bar (while xlq is running) and choosing “About”. If the “Good for updates through” date is earlier than the release date, you should renew your license from this page: http://www.qmatix.com/XLQ_Purchase.htm. If your license has expired and you would like to purchase a new license to work with the latest release, please contact me for a discount.

 

Installation

 

The installation process has changed. XLQ is now built using Visual C++ 2015 (except for the XP version). Starting with Visual C++ 2015 Microsoft will include the c++ runtime libraries as part of the operating system, and should be including them as a patch in the future, but until then they need to be installed.

The install process will now check if they are present, and if not, will download them from Microsoft and install them. In such a case the installation process will require an internet connection and a reboot.

Once the runtime libraries are installed they would not need to be installed again in the future for xlq or another program requiring them.

 

If the runtime libraries are not already on your computer and you prefer to not have to reboot as part of the install, you can install them first from here:

https://www.microsoft.com/en-us/download/details.aspx?id=48145

 

New excel addin

 

For the last couple of years, I have been working on a new design and completely re-written interface with excel which is included in this version. The new design opens up many new possibilities for future versions, and already includes a number of enhancements, all with ensuring compatibility with any existing work. Other than internal processing having been redesigned and optimized, xlq now also has its own ribbon (i.e. for excel 2010 and later). Earlier versions will still have the menu and excel 97 is no longer supported. The ribbon now also includes a link to the help file.

 

If you use excels ‘insert function’ option, xlq functions are now broken down by category allowing you to select only day / intraday / range / xlqxh / historic weekly etc. And when you select a formula, or press fx on any cell containing one, a lot more information on the formula will be provided, including description, named parameters, which sources are supported and the license of xlq required etc. In addition, if you now click “help on this function”, you will be taken to associated help pages on the website.

 

All formulae can be used as per earlier versions, but there is now also a new way of entering a formula, i.e. with =xlqfx. Xlqfx allows you to wrap a request for any xlq formula, allowing you to pass the formulae itself as a cell reference. The format is =xlqfx(formula name, symbol, source, parameters…) with the parameters depending on the formula entered and the order being that of the formula itself.

e.g.

=xlqFx(“xlqprice”,”msft”,”yahoo”)

=xlqFx(“xlqhclose”,”msft”,”yahoo”,-1)

Used with a cell reference, i..e =xlqFx(R1,”msft”,”yahoo”,-1) allows you to quickly change columns of data to different formulae.

 

In addition to xlqfx, there is also xlqfxStatic. It is used in exactly the same way, i.e. =xlqFxStatic(“xlqhclose”,”msft”,”yahoo”,-1), but the data will only be re-requested again when alt-ctrl-f9 is pressed (or from vba when Application.CalculateFull is called). This can potentially be used in many different ways. e.g.

Complex workbooks can avoid unneeded calculations on data that rarely changes.

You can now have the data only update when you want.

Used with a timer and comparing xlqfxstatic results to xlqfx you could compare snapshot data with real-time data for example every 30 seconds etc., or compare changes since a specific moment.

A workbook that is opened containing xlqfxstatic formulae will not update them unless alt-ctrl-f9 is pressed which allows you to continue working with the old data, or if the workbook contains only xlqfxstatic formulae it will not try and connect with xlq until the refresh is requested.

Due to the way xlqfxstatic works it is also the recommended way of calling xlq formulae from vba. The VBA examples in the help file and aaiiMacro.xls have been updated to use xlqfxStatic.

 

XLQ Bridge

 

Working along with the new addin in excel is a new program, the xlqBridge, which will run in the background, being started and closed by excel as needed. It allows for a 3-way communication between excel and the xlq data engine allowing for a better workload distribution and avoids potential bottlenecks. Apart from the overall improvements and workbook load time (the workbook would need to be save with this version) it also virtually eliminates potential “server busy” errors or many reasons for #xlq being shown.

 

XLQ Engine

 

There are now new preference settings for each source (right click the Q and choose preferences, select data source specific and choose the source. Previous versions of xlq would maintain all symbols for ‘day data’ until deleted in the stock manager. You may now choose per source whether you would like the data kept or not. By default, the data is no longer kept, so if you would like it to work as per prior versions of xlq you would need to change these settings after installation. These settings are for day data / real-time symbols only and not for historic data.

 

In xlq preferences under general settings there are 2 new options

Delete all data on exit – once and delete all data on exit – always.

Previously if there was a data corruption or for housekeeping purposes you needed to manually delete the data files. You may now choose the ‘once’ option to delete the files on exit automatically whenever needed.

The Always option is not recommended but if you require that every time you start xlq there is no existing data stored, it is now possible.

 

Previously if excel crashed, or for whatever reason you needed to ‘kill’ the xlq engine you had to do so from the windows task manager, now it is possible to do a forced exit by right clicking the Q and pressing ctrl. If exit is greyed, it will be changed to ‘Force Exit!’ and enabled. It allows for xlq to be shut down properly even if there are connections to it, or a prior connection has crashed. It should only be used as needed.

 

“insufficient memory” error using the com interface has been fixed.

 

YieldLowAve7Y was missing from the AAII field list and is now supported

 

IQFeed

 

The real-time data from IQFeed has included ‘odd lot’ or non-qualifying data, which other sources would exclude. For example, small quantities that would not alter the last price or be included in volume.

This data is now excluded from xlqPrice etc. by default, but you can include them via a new xlq preference setting for iqfeed.

Regardless of whether you include or exclude them it is now possible to see the information separately with 3 new formulae

xlqPriceNonQualifying, xlqSizeNonQualifying and xlqTimeNonQualifying

If you now include the non-qualifying trades, they will also be included in the intraday (not backfill) which was not the case previously.

There is now also a new preference setting for iqfeed intraday that allows you to only update the intraday on new trades only, rather than on bid / ask changes etc. which is the default.

 

InteractiveBrokers

 

The following new formulae are supported:

xlqVolumeRate being volume per minute

xlqTradeRate being trades per minute

xlqNoOfTradesToday (also supported by IQFeed)

 

Google

 

Working with a different interface xlq can now request historic data for more than US equities. Any historic request including an exchange as part of the symbol will use this interface, allowing you to request data for indexes and non US stocks, e.g. for the dow DJI:INDEXDJX, Danske Bank Copenhagen DANSKE:CPH, Haseko Corporation Tokyo 1808:TYO

 

xlqCompanion

 

New column and field ‘Days G/(L)’ showing the $ amount gained or lost for a held symbol for the current day. To add the column, select it from the field chooser and drag it to where you want.

 

Additional Formulae

 

In addition to the new formulae mentioned above, xlqhValidDateOrNext has been added. This formula allows you to test a date to see if there is market data for that date. If the same date is returned it is a valid date, if not the following trading date is returned. Xlqhdate would return the prior trading day

e.g. =xlqhValidDateOrNext(“msft”,”1/1/2015″,”yahoo”) would return 2 January 2015 whereas

=xlqhDate(“msft”,”1/1/2015″,”yahoo”) would return 31 December 2014.

This allows you to easily determine the first trading day for a given week, month etc.

 

The money flow index is now supported with xlqhMoneyFlowRaw, xlqhMoneyFlowRatio and xlqhMoneyFlowIndex, along with xlqxh and intraday versions.

 

Other Changes

 

The original range formulae, i.e. xlqhHighestClose, LowestClose, HighestHigh, LowestLow, HighestCloseDate, LowestCloseDate, HighestHighDate, LowestLowDate, HighestVolume, LowestVolume, HighestVolumeDate, LowestVolumeDate have been renamed to correspond with newer range formulae, i.e. starting with xlqrh. If you are using these, you would need to do a global replace in your projects to change the name.

 

The help file and sample spreadsheets have been updated.

HOW DO YOU START A PROFITABLE TRADING BUSINESS? Read more NOW >>>

NOTE I now post my TRADING ALERTS into my personal FACEBOOK ACCOUNT and TWITTER. Don't worry as I don't post stupid cat videos or what I eat!
This entry was posted in Quant Development and tagged , , on by .

About caustic

Hi i there My name is Bryan Downing. I am part of a company called QuantLabs.Net This is specifically a company with a high profile blog about technology, trading, financial, investment, quant, etc. It posts things on how to do job interviews with large companies like Morgan Stanley, Bloomberg, Citibank, and IBM. It also posts different unique tips and tricks on Java, C++, or C programming. It posts about different techniques in learning about Matlab and building models or strategies. There is a lot here if you are into venturing into the financial world like quant or technical analysis. It also discusses the future generation of trading and programming Specialties: C++, Java, C#, Matlab, quant, models, strategies, technical analysis, linux, windows P.S. I have been known to be the worst typist. Do not be offended by it as I like to bang stuff out and put priorty of what I do over typing. Maybe one day I can get a full time copy editor to help out. Do note I prefer videos as they are much easier to produce so check out my many video at youtube.com/quantlabs