Yahoo historic data

(September 2024)

Since September 7 all Yahoo data returns N/A, with the actual request failing. They have also removed all download links for their historic data from their site.

24.41 has been updated to return data from Yahoo using a different interface that continues to work. If this interface is also disabled, Yahoo will be removed as a source from xlq.

If you are not already using one of the other historic sources supported please explore the alternatives.

xlq2 source / iex cloud

(June 2024)

IEX have given notice that they are leaving the market data business and will be terminating their iex cloud service on 31 August 2024. As a result the xlq2 source, supporting their data will no longer update after that date and will be removed from the following version.
(Since xlq2 24.4 the xlq2 / iex cloud source has been removed)

TDAmeritrade / Charles Schwab

(June 2024)

All TDAmeritrade users have now been transferred to Schwab and the TDAmeritrade service has ended.

TDA has been removed as a source from xlq2 24.3

Opening Excel workbooks via a link not working correctly

(October 2017)

The last couple of updates to Excel 2016 have introduced a problem for certain users and is still present in excel build 8431.2094 released on 4 October. I have contacted Microsoft explaining the problem, so hope it will be corrected. If not I will include a permanent workaround for the next update.

The problem occurs if you use a link to open your workbook. When opened that way excel now initializes the workbook before loading com addins. The addins are loaded once the workbook has recalculated. For xlq this results in #name being shown for all formulae and xlq can start briefly as opening triggers activation of the xlq bridge, but the connection is closed again due to there being no active addin.

Once the workbook is opened, the addin is then initialized and it is possible to press alt-ctrl-f9, or edit a cell containing xlq formulae etc. which will then trigger activation of the rest. It is also possible to avoid the problem by opening excel first, then opening your xls from within excel.

It is possible to replicate the problem by doing the following:
Open a command prompt and change the directory to xlq, i.e.
cd\"program files"\qmatix\xlq and press enter
If you type in xlqdemo.xlsx and press enter you will see the same problem
But if you type in
c:\"program files"\"microsoft office"\root\Office16\excel.exe xlqdemo.xlsx and press enter (assuming that is your path for excel
it will work.

I have found that if you create a dummy vba addin that is loaded by default from the addin directory by excel it will also force the initialization of com addins before the xls is loaded and everything works as before. To use that as a workaround, follow these steps:

Open excel with a new workbook.
Click developer, visual basic.
(if you do not have a develop menu, click file / options / customize ribbons and check developer on the right and click OK)
In visual basic click Insert Module (not class module)
And paste the following 3 lines into it:
Public Sub Auto_Open()
'MsgBox "Hello"
End Sub

Then in excel choose file / save as, choose a name and change the type to Excel Addin (*.xlam). (Leave excel to store it in the roaming Microsoft addins directory as per default)
Then choose file / options / addins
Click manage “excel addins” and click Go
Ensure the addin you have created is checked.
(you can remove the ‘ before msgbox “hello” to show a dialog on starting excel to test if needed)

You can now close excel, and the addin will be opened every time excel is opened, and all other com addins should now initialize correctly when opened from a link.

Supported Hardware and Software

Supported

• Will run on any full version of Windows starting from Windows 8 through to Windows 11.
• Will run on any computer, desktop, laptop, tablet or virtual machine that is running a version of windows above.
  (including Microsoft Surface Pro and Surface 3)
• May be used on an Apple via Parallels (etc.)
• Excel is not required to use XLQ, but xlq will support any version of Excel starting from Excel 2000

Unsupported

• Will not run on windows RT or windows phone, nor will it work with Windows 95, 98, ME, NT 2000, XP or Vista
• Will not run on an iPad, Linux or Apple OS X.
  (if you need to, use Parallels, VM etc.)

Excel

#Name

• If you are seeing #Name in a cell containing an xlq formula it usually indicates that Excel has disabled add-ins, which can occur if excel crashes, although it usually asks before doing so.
  You can verify this by opening a new workbook and typing in =xlqPrice("msft") and pressing enter.
  In excel 2010 and later click the File menu and choose options. (In excel 2007 click the Office button and choose options)
  From the excel options dialog, choose add-ins. At the bottom of that screen it shows “manage” and in the drop down afterwards choose “disabled items” then click Go. If xlq is there, re-enable it, then close and restart excel and it should work again.
  If you are using Excel 2003, you can do the same by clicking help / about and clicking the “disabled items” button.
• Other reasons could be that xlq is not installed, the wrong platform (32 or 64 bit) is installed, or the COM add in 'xlq Loader' (xlq2init.dll, or earlier than xlq 5.3 xlqinit.dll), is not set to load when excel starts.

XLQ Data Engine

Cannot see the Q

• When xlq is running, there is a (usually) green or orange Q shown on the task bar next to the windows date / time. Right clicking this Q displays the 'Q menu' providing access to preference settings, status views and underlying data management and viewing. It is also where the registration code is entered.
  If xlq is running and you cannot see the Q, windows could be hiding it. In that case click the small triangular up arrow. You can then drag the Q to the task bar.
  Since xlq 5.1 you can now also press Alt-Ctrl-Shift-Q to display the Q menu.

Historic Data Corruption

• If there is a crash / power failure etc. when xlq is writing to disk, a file can become corrupted. If using xlqPlus and this occurs, when xlq is started a small dialog will show an error message, and closing it will again display a new one. xlq2 has protection in place to avoid this but if you suspect a data corruption the first thing to try would be to delete the data files.
• If this occurs, right click the Q and choose preferences, Under General settings, set "Delete all data on exit -once" to Yes, click apply, then close and restart xlq. (Version 5.3 or later required)
• To delete the data files manually, close xlq (rebooting if needed) and if using xlqPlus delete the files starting with h and ending with .xlq and .xqi (e.g. h_yahoo1(2,3 and 4).xlq and h_yahoo1.xqi etc.). If using xlq2 they will start with h2 and end with xlq2db1, xlq2db2, xlq2db3 and xlq2db4. All files may be deleted, or can be done per data source in which case all h2_source,* should be deleted.
  They are found by default in the users directory e.g. c:\users\[user name]\appdata\local\xlq\.
  If you cannot see the files in windows explorer. With Windows 10, from the ribbon choose view / Options, then in the dialog click View. With earlier versions of windows, press alt to see the menu and choose tools / folder options. Click the view tab.
  From there, change the option “Hidden files and folders” to “Show hidden files, folders and drives” and press apply.
  These files will be recreated when you start xlq, and the database will be repopulated as the data is needed.
  
  An alternative is to uninstall (which deletes the files) and re-install, but doing so you will also lose any preference changes and need to re-enter your reg. code.

Clean up Symbols

• Whenever a symbol is used by xlq, in excel, xlqCompanion etc. the symbol will be added to the stock manager. If you are no longer interested in all the symbols you should remove any unwanted ones.
• Right click the Q (or press Alt-Ctrl-Shift-Q) and select Stock manager (alternatively double click the Q).
  Choose the source, then select the symbols you no longer want by ticking delete. If you wish to select a number of rows, highlight them with the mouse, then right click and choose delete.
  To select all symbols, the most efficient is to right click the top left cell and choose delete. When all symbols are chosen processing is faster and additional cleanup is performed.
  The actual delete is done when you click the refresh symbol (double arrows).
  Any symbol that is deleted but is still being used will automatically be added again.
  
  Note the stock manager does not update automatically. To see the latest information, refresh (double arrows) should be pressed first.
• If you wish to remove all data, the quickest is to right click the Q and choose preferences, Under General settings, set "Delete all data on exit -once" to Yes, click apply, then close and restart xlq. (Version 5.3 or later required)

Cannot choose Exit from the Q menu

• If xlq is started by or is being used by another program Exit from th menu is disabled.
• If the program using xlq exits abnormally or crashes, exit will remain disabled. From xlq 5.3 you may now press ctrl, which will allow you to choose the menu option, now shown as "Force Exit!" to perform a clean exit.
• For versions earlier than 5.3 you would need to press ctrl-shift-escape to display the windows Task Manager and under processes end the task xlqEng1.exe

Data Sources

CoinMarketCap

• If you have entered your CMC token into xlq, you may work with their API's data directly in xlq. (xlq preferences / data source specific / CoinMarketCap)
• All subscription levels including Basic (free) would be compatible.
• For more information on CMC: (https://coinmarketcap.com/api/pricing/)

EODHistoricalData

• If you have entered your EODHD token into xlq2, you may work with their API's data directly in xlq. (xlq preferences / data source specific / EOD Historical Data)
• All subscription levels are compatible with the ALL-IN_ONE being the recommended. Discounted pricing for xlq2 users is available.
• For more information on EODHD click HERE

FinancialModelingPrep

• If you have entered your FMP token into xlq2, you may work with their API's data directly in xlq. (xlq preferences / data source specific / FinancialModelingPrep)
• Discounted pricing for xlq2 users is available.
• For more information on FMP click HERE

InteractiveBrokers

• If you have an InteractiveBrokers account you can use any of their market data that you are subscribed for via XLQ.
• TraderWorkStation (TWS) or their gateway needs to be running on the computer, and needs to be configured to allow for connections.
• In TWS click 'Configure' then choose API / Settings.
  "Enable ActiveX and Socket Clients" should be checked.
  And to avoid the connection message add 127.0.0.1 as a trusted IP Address.
  (Newer versions of TWS may include additional options but are not relevant)

• If you have any connection or symbol problems consult the xlq Interactivebroker status views (right click the Q in the task bar and choose view data / status). Look at both the day and historic status views for any errors.
• If you are not sure of the symbol to use, enter it into TWS then right click the row and choose contract info / description. The screen shown will show the symbol, type and currency to use.

IQFeed

• If you have an IQFeed subscription you can use any of their market data that you are subscribed for via XLQ.
• IQConnect needs to be installed on your computer and xlq will connect with it automatically as needed. You may download the latest version from here https://www.iqfeed.net/XLQ/index.cfm?displayaction=support&section=download
• If you are new to IQFeed, it is possible to request a free trial for a couple of weeks (https://www.iqfeed.net/XLQ/index.cfm?displayaction=start)

Norgate Data

• If you have a Norgate Data subscription you can use any of their market data that you are subscribed for via XLQ2.
• Your Norgate Data account would need to be active, with NDU installed on the computer. xlq will then connect automatically as needed.
• If you are new to Norgate, it is possible to request a 3 week free trial (with limited data) (https://norgatedata.com/freetrial.php)

Schwab

• If you have a Schwab account it is possible to work with their free real-time (including for options) and historic data.
• From xlq preferences you can use a link to sign into your Schwab account and authorise xlq for data access. This needs to be done at least every 7 days.
• Additional support via xlq2.
• Click Here for more information.

Stockwatch

• If you have at least an Intermediate subscription with Stockwatch, you can work with their market day data downloads via XLQ2.
• Your Stockwatch account would need to be active, and your sign in information entered into xlq preferences. You would also need to choose the exchanges you want data for and the number of days to retrieve.
• If you are new to Stockwatch, you may subscribe or see more info here: (https://www.stockwatch.com/Help/Prices.aspx?pro=N&currency=C)

Tiingo

• Tiingo data is free or subscription depending on the amount or type of data you need.
• To enable Tiingo in xlq you need to enter your token in xlq preferences / data source specific / tiingo, and everything is automatic afterwards.
• If you do not have a token, you would need to register (free) with tiingo. For more information see https://blog.tiingo.com/how-to-find-your-tiingo-api-token

Tradier

• If you have a Tradier account it is possible to work with their free real-time and historic data.
• From xlq preferences you can use a link to sign into your Tradier account and authorise xlq for data access. This needs to be done every day.
• Additional support via xlq2.
• Click Here for more information.