Topics

Stock Symbol Prices not updating when using Macro


constantine@...
 

Hi All,

I need to get historical prices from 200 stocks. For each stock, I need a macro to enter the symbol, refresh the price date,  copy and paste the price date to another workbook, and then enter the next stock symbol to obtain its historical price data. I cretaed the macro, but the stock prices do not update to the stock symbol entered. I've tried using  "WaitTime = Now() + TimeValue("0:00:10")" to give the vba a pause, bu that doesn't work. I've also tried 
ActiveSheet.EnableCalculation = False
ActiveSheet.EnableCalculation = True

I've also tried 
Workbooks(ThisWorkbook.Name).RefreshAll, and  Application.CalculateFull. They don't work

 The stock prices don't seem to update as long as the macro is running. Any suggestions that will allow me to do what I want?

Thanks in advance. 


Leo
 

Hi

 

When using xlq with VBA you need to use the xlqfxstatic wrappers and not the direct formulae. See the xlqfx sheet in xlqdemo if using in cells, and the vba example in the help file or in xlqaaiimacro.xlsm in the program files directory if using within the macro.

 

The xlqfxstatic wrappers avoid all optimizations and make direct calls to xlq, allowing you to manage the updates with changing cell references or calling CalculateFull within the macro.

 

I recommend also using Sleep() in VBA rather than Wait as it allows excel to continue doing other processing while the macro waits.

Also if you are requesting new data you can also monitor xlqhSymbolsToProcess to see if the data has been updated. It is recommended to make a dummy request run for the symbols you need for 1 request for the earliest date required, along with 1 xlqxh request per calculation needed, monitor with a sleep till xlqhSymbolsToProcess is 0 then do your processing. It will be faster than waiting on each new request.

 

Regards

 

Leo

   

On Tue, May 26, 2020 at 10:36 PM, <constantine@...> wrote:
Hi All,

I need to get historical prices from 200 stocks. For each stock, I need a macro to enter the symbol, refresh the price date,  copy and paste the price date to another workbook, and then enter the next stock symbol to obtain its historical price data. I cretaed the macro, but the stock prices do not update to the stock symbol entered. I've tried using  "WaitTime = Now() + TimeValue("0:00:10")" to give the vba a pause, bu that doesn't work. I've also tried 
ActiveSheet.EnableCalculation = False
ActiveSheet.EnableCalculation = True

I've also tried 
Workbooks(ThisWorkbook.Name).RefreshAll, and  Application.CalculateFull. They don't work

 The stock prices don't seem to update as long as the macro is running. Any suggestions that will allow me to do what I want?

Thanks in advance. 


constantine@...
 

Thank you for the response.

I looked at the xlqfx sheet in xlqdemo. I see the xlqfx function provides the closing price (or possible the current price), but how can I use it to return Open, High. Low?
Also, I looked, but can't seem to find where the help file or  xlqaaiimacro.xlsm files are located.
 I tried using the sleep function within the vba code I was previously using, but it didn't help. 

Thanks again.


Leo
 

Hi

 

I mentioned that you should use xlqfxStatic, not xlqFx. xlqFxStatic will work with VBA. xlqFx would be the same as using the formulae directly.

 

You can wrap any xlq function and not just closing price / current price. Just use it in the same way for open, high low, whatever. Give the function name you want to wrap along with the required parameters.

 

The files are in the program files directory, usually c:\program files\qmatix\xlq or c:\program files (x86)\qmatix\xlq, i.e. same place where the programs are and xlqdemo.xlsx. The help file is also there but can also be accessed from the windows start menu, from the xlq menu on the task bar, from the xlq menu in excel and from the menu in xlqCompanion.

 

The Sleep function on its own will not make it work, it is just better to use as it only pauses the thread but allows other excel threads to continue working.

 

Regards

 

 

Leo

On Wed, May 27, 2020 at 05:42 AM, <constantine@...> wrote:
Thank you for the response.

I looked at the xlqfx sheet in xlqdemo. I see the xlqfx function provides the closing price (or possible the current price), but how can I use it to return Open, High. Low?
Also, I looked, but can't seem to find where the help file or  xlqaaiimacro.xlsm files are located.
 I tried using the sleep function within the vba code I was previously using, but it didn't help. 

Thanks again.


constantine@...
 

Leo, thank you for all your help. xlqFxStatic worked perfectly. It works perfect even without a 'wait" or "sleep" command.