Excel-data download directed from RATS

[PeterF]

Dear Tom,

Bloomberg or LSEG (London Stock Exchange Group, which has bought data business from Thomson-Reuters) provide subscribers with an add-in for Excel, which allows to dowload data into an Excel sheet. As I did not want to open and close many Excel-sheets manually, I looked for a way to automate that precudure of getting LSEG-data in a RATS program. Based on the topic https://www.estima.com/forum/viewtopic. ... 724#p19724 (winrats inter-operation), I thought that it could be done be various OS instructions. But somehow, the attempts failed.

Code: Select all

Calendar(M)  1992:01

comp start = 1992:01
comp ende  = 2025:10

*
* compile the function string and write it in an Excel-sheet
comp macrostring = "=@RDP.HistoricalPricing(" + '"FRPPIY=ECIX"'+",VALUE,"+ $
   '"START:31-Jan-1992 INTERVAL:P1M SOURCE:RFV"'+ ",,"+ $
   '"SORT:ASC CH:Fd RH:Timestamp"'+",A1)"

* disp macrostring
report(action=define)
report(atrow=1,atcol=10) macrostring
open copy "S:\MyPath\RATS-Workstation Data.xlsx"
report(action=show,format=xlsx,unit=copy)

*
* Trying to run a batch file to start Excel and opening the file
*
OS "'Open Excel-Steet.bat'"

*
* Tryong to open Exel and loading the file
*
OS "C:\Program Files\Microsoft Office\root\Office16\excel.exe /d S:\MyPath'\RATS-Workstation Data.xlsx"

*
* Open with an vbs-script
*
OS "S:MyPath\Open Excel File.vbs"

*
* Close the Excel-File
OS "S:\'MyPath'\'Close Excel File'.vbs"

open data "S:\QCR Rats Files\RATS-Workstation Data.xlsx"
data(format=xlsx,org=col,labels) start ende FRPPIY<<Value

The batch file contains the following instructions:

Code: Select all

echo off
start /d "C:\Program Files\Microsoft Office\root\Office16\" excel.exe "S:\MyPath\RATS-Workstation Data.xlsx"
pause

The VBS script to open the excel file contains the following code

Code: Select all

Dim objExcel, objWB
dim i
dim WB_To_Close
set objExcel = GetObject(, "Excel.Application")
WB_To_Open = "RATS-Workstation Data.xlsx"
i = objExcel.Workbooks.Count
    set objWB = objExcel.Workbooks(i+1)
    if objWB.Name = WB_To_Close then
        objWB.Open
    end if

The VBS script to close the excel file is

Code: Select all

Dim objExcel, objWB
dim i
dim WB_To_Close
set objExcel = GetObject(, "Excel.Application")
WB_To_Close = "RATS-Workstation Data.xlsx"  ' enter name of WB to be closed here
for i = 1 to objExcel.Workbooks.Count
    set objWB = objExcel.Workbooks(i)
    if objWB.Name = WB_To_Close then
        objWB.Save
        objWB.Close
        exit for
    end if
    i = i + 1
next
'if there are no more workbooks open in that instance of excel, then quit excel
if objExcel.Workbooks.Count = 0 then 
    objExcel.Quit
end if

For all attempts to open the excel file, I got the message "###CreateProcess on S:\'MyPath'\'Open Excel File.xxx' failed (2)." The message for the attempt to close the excel file was correspongingly.

What did I do wrong? It could save a lot of time if I can automate the process to download data and run the program file in batch mode during the night.

Best regards
PeterF

[TomDoan]

We just posted an official 11.00e which I think fixes it. (There was a subtlety in the Windows call that was being used by OS that Excel used but most other programs did not).

[PeterF]

Dear Tom,

thank you for the reply and fixing it at build 11.0e. I did search a bit more at the internet. I found one explanation that when I start manually Excel, then Windors will look for the registry key of the COM-Addin in HKUM, while when the start is initiated by an object, then Windors looks at the key in HKLM section. If it does not find the key there, it would start excel without loading the COM-Addin. i have not yet tried it, because I am cautious with making changes in the registry.

If it will work, then it would be a convenient way to get data from a Bloomberg terminal or one from LSEG directly into RATS.

Best regards
PeterF

[TomDoan]

OS in 11.00d would not execute Excel at all. It turns out that the command line passed to the Windows API procedure had to be read/write (the reason why isn't all that clear) and Excel relied upon that. I don't know about running a VB script inside Excel, but OS "excel" does fire up Excel in 11.00e.