Convert Excel macros to run with LibreOffice Calc?

grumpyskeptic · 09-16-2018, 12:47 PM

I would like to bulk download historical stock data, for example for the constituents of the FTSE100 - 100 different stocks. There are a great many webpages that suggest ways of doing bulk downloads, but Yahoo and Google keep changing and updating things, so only the most recent are likely to work, and they also require a lot of knowledge of Matlab or Python for example, so would be very difficult to use for someone unfamiliar with those languages.

However the Excel spreadsheet on the webpage below has been updated recently, so should work: http://investexcel.net/multiple-stoc...der-for-excel/

Except that the macros give an error message when the spreadsheet is run in LibreOffice Calc 5.

Does anyone know how to get it working please?

Incidently, LibreOffice Calc 5 will not or cannot allow me to save the spreadsheet with macros.

Thanks.

ps. I would be willing to pay a smallish amount to bulk download historical stock data for British stocks with a capital value down to say £50million if anyone knows of a company that offers such a service. That would be many hundreds of stocks I expect.

pan64 · 09-16-2018, 12:58 PM

looks like libreoffice can't understand those macros. Would be nice to post that error message. At least.

grumpyskeptic · 09-17-2018, 05:29 AM

The error message is the same in both spreadsheets. After doing nothing except clicking on the "bulk download" button on the spreadsheet, I get an error message saying -

"LibreOffice 5.0.3.2. BASIC runtime error. '423' TintAndShade"

in the code, ".TintAndShade = 0" is highlighted.

It seems like something merely cosmetic, which I do not care about.

Looking through the code I see that it refers to the "crumb" thing, so with Excel it probably works.

Edit: So would it work if I just deleted every reference to "TintAndShade"?

I would be happy to just have some bare-bones BASIC code without any spreadsheet that saved the .csv files to HD, but do not know what to cut away to get this. The "crumb" problem is the difficult thing.

Thanks.

John VV · 09-17-2018, 10:47 PM

as i recall libreoffice's calc can use BASIC , whereas MS's excel uses "Microsoft Visual BASIC "

the two are not the same language

you will need to port the MS visual basic macros

Michael Uplawski · 09-18-2018, 12:44 AM

The Open/LibreOffice API is a development which aims at providing functionality in these office-packages similar to the Microsoft®-suites. I am not aware of any incompatibilities between OpenOffice and LibreOffice, so the current API-descriptions and tutorials should be okay: http://www.pitonyak.org/oo.php, for example. But there is more.

Do not expect something which is not Microsoft® to work like what is Microsoft®, OLE, COM or other stuff which does not exist in Linux!

Edit: Something else, I just found: https://wiki.openoffice.org/wiki/Doc...on/BASIC_Guide

Edit II: For munging stock data in an Excel-sheet.., would not XSLT be a solution? Which file-types are you trying to manipulate?

grumpyskeptic · 09-18-2018, 04:02 AM

I would be happy to download the data in any format. Once it is safe and secure on my HD, then I can convert it to any other format at my leisure.

I wish someone published a CD or DVD of historic (British) stock prices.

Thanks.

grumpyskeptic · 09-18-2018, 06:00 AM

There is an open-source program called Yloader that I hope is up to date. The source code in C++ is freely available.

http://yloader.com/

I tried running both the 32bit and 64bit .exe programs with Wine, but after installation nothing happened and I could not see the installed programs on the menu anywhere, including not under Wine. I see that the Windows .exe files are actually compressed files consisting of various things such as dlls.

How difficult would it be to compile the C++ code under Linux? I imagine it may need some tweaking for Linux. I am not a programmer.

Thanks.

AnanthaP · 09-20-2018, 02:09 AM

Just googled
libre office run time error 423

and came up with
https://ask.libreoffice.org/en/quest...e-error-codes/ (a forum)

which has got a list of error codes in a reply of which
https://opengrok.libreoffice.org/xre...classes/sb.cxx

Quote:

{ 1, ERRCODE_BASIC_EXCEPTION },
{ 2, ERRCODE_BASIC_SYNTAX },
{ 3, ERRCODE_BASIC_NO_GOSUB },
{ 4, ERRCODE_BASIC_REDO_FROM_START },
{ 5, ERRCODE_BASIC_BAD_ARGUMENT },
{ 6, ERRCODE_BASIC_MATH_OVERFLOW },
{ 7, ERRCODE_BASIC_NO_MEMORY },
{ 8, ERRCODE_BASIC_ALREADY_DIM },
{ 9, ERRCODE_BASIC_OUT_OF_RANGE },
{ 10, ERRCODE_BASIC_DUPLICATE_DEF },
{ 11, ERRCODE_BASIC_ZERODIV },
{ 12, ERRCODE_BASIC_VAR_UNDEFINED },
{ 13, ERRCODE_BASIC_CONVERSION },
{ 14, ERRCODE_BASIC_BAD_PARAMETER },
{ 18, ERRCODE_BASIC_USER_ABORT },
{ 20, ERRCODE_BASIC_BAD_RESUME },
{ 28, ERRCODE_BASIC_STACK_OVERFLOW },
{ 35, ERRCODE_BASIC_PROC_UNDEFINED },
{ 48, ERRCODE_BASIC_BAD_DLL_LOAD },
{ 49, ERRCODE_BASIC_BAD_DLL_CALL },
{ 51, ERRCODE_BASIC_INTERNAL_ERROR },
{ 52, ERRCODE_BASIC_BAD_CHANNEL },
{ 53, ERRCODE_BASIC_FILE_NOT_FOUND },
{ 54, ERRCODE_BASIC_BAD_FILE_MODE },
{ 55, ERRCODE_BASIC_FILE_ALREADY_OPEN },
{ 57, ERRCODE_BASIC_IO_ERROR },
{ 58, ERRCODE_BASIC_FILE_EXISTS },
{ 59, ERRCODE_BASIC_BAD_RECORD_LENGTH },
{ 61, ERRCODE_BASIC_DISK_FULL },
{ 62, ERRCODE_BASIC_READ_PAST_EOF },
{ 63, ERRCODE_BASIC_BAD_RECORD_NUMBER },
{ 67, ERRCODE_BASIC_TOO_MANY_FILES },
{ 68, ERRCODE_BASIC_NO_DEVICE },
{ 70, ERRCODE_BASIC_ACCESS_DENIED },
{ 71, ERRCODE_BASIC_NOT_READY },
{ 73, ERRCODE_BASIC_NOT_IMPLEMENTED },
{ 74, ERRCODE_BASIC_DIFFERENT_DRIVE },
{ 75, ERRCODE_BASIC_ACCESS_ERROR },
{ 76, ERRCODE_BASIC_PATH_NOT_FOUND },
{ 91, ERRCODE_BASIC_NO_OBJECT },
{ 93, ERRCODE_BASIC_BAD_PATTERN },
{ 94, ERRCODE_BASIC_IS_NULL },
{ 250, ERRCODE_BASIC_DDE_ERROR },
{ 280, ERRCODE_BASIC_DDE_WAITINGACK },
{ 281, ERRCODE_BASIC_DDE_OUTOFCHANNELS },
{ 282, ERRCODE_BASIC_DDE_NO_RESPONSE },
{ 283, ERRCODE_BASIC_DDE_MULT_RESPONSES },
{ 284, ERRCODE_BASIC_DDE_CHANNEL_LOCKED },
{ 285, ERRCODE_BASIC_DDE_NOTPROCESSED },
{ 286, ERRCODE_BASIC_DDE_TIMEOUT },
{ 287, ERRCODE_BASIC_DDE_USER_INTERRUPT },
{ 288, ERRCODE_BASIC_DDE_BUSY },
{ 289, ERRCODE_BASIC_DDE_NO_DATA },
{ 290, ERRCODE_BASIC_DDE_WRONG_DATA_FORMAT },
{ 291, ERRCODE_BASIC_DDE_PARTNER_QUIT },
{ 292, ERRCODE_BASIC_DDE_CONV_CLOSED },
{ 293, ERRCODE_BASIC_DDE_NO_CHANNEL },
{ 294, ERRCODE_BASIC_DDE_INVALID_LINK },
{ 295, ERRCODE_BASIC_DDE_QUEUE_OVERFLOW },
{ 296, ERRCODE_BASIC_DDE_LINK_ALREADY_EST },
{ 297, ERRCODE_BASIC_DDE_LINK_INV_TOPIC },
{ 298, ERRCODE_BASIC_DDE_DLL_NOT_FOUND },
{ 323, ERRCODE_BASIC_CANNOT_LOAD },
{ 341, ERRCODE_BASIC_BAD_INDEX },
{ 366, ERRCODE_BASIC_NO_ACTIVE_OBJECT },
{ 380, ERRCODE_BASIC_BAD_PROP_VALUE },
{ 382, ERRCODE_BASIC_PROP_READONLY },
{ 394, ERRCODE_BASIC_PROP_WRITEONLY },
{ 420, ERRCODE_BASIC_INVALID_OBJECT },
{ 423, ERRCODE_BASIC_NO_METHOD },
{ 424, ERRCODE_BASIC_NEEDS_OBJECT },
{ 425, ERRCODE_BASIC_INVALID_USAGE_OBJECT },
{ 430, ERRCODE_BASIC_NO_OLE },
{ 438, ERRCODE_BASIC_BAD_METHOD },
{ 440, ERRCODE_BASIC_OLE_ERROR },
{ 445, ERRCODE_BASIC_BAD_ACTION },
{ 446, ERRCODE_BASIC_NO_NAMED_ARGS },
{ 447, ERRCODE_BASIC_BAD_LOCALE },
{ 448, ERRCODE_BASIC_NAMED_NOT_FOUND },
{ 449, ERRCODE_BASIC_NOT_OPTIONAL },
{ 450, ERRCODE_BASIC_WRONG_ARGS },
{ 451, ERRCODE_BASIC_NOT_A_COLL },
{ 452, ERRCODE_BASIC_BAD_ORDINAL },
{ 453, ERRCODE_BASIC_DLLPROC_NOT_FOUND },
{ 460, ERRCODE_BASIC_BAD_CLIPBD_FORMAT },
{ 951, ERRCODE_BASIC_UNEXPECTED },
{ 952, ERRCODE_BASIC_EXPECTED },
{ 953, ERRCODE_BASIC_SYMBOL_EXPECTED },
{ 954, ERRCODE_BASIC_VAR_EXPECTED },
{ 955, ERRCODE_BASIC_LABEL_EXPECTED },
{ 956, ERRCODE_BASIC_LVALUE_EXPECTED },
{ 957, ERRCODE_BASIC_VAR_DEFINED },
{ 958, ERRCODE_BASIC_PROC_DEFINED },
{ 959, ERRCODE_BASIC_LABEL_DEFINED },
{ 960, ERRCODE_BASIC_UNDEF_VAR },
{ 961, ERRCODE_BASIC_UNDEF_ARRAY },
{ 962, ERRCODE_BASIC_UNDEF_PROC },
{ 963, ERRCODE_BASIC_UNDEF_LABEL },
{ 964, ERRCODE_BASIC_UNDEF_TYPE },
{ 965, ERRCODE_BASIC_BAD_EXIT },
{ 966, ERRCODE_BASIC_BAD_BLOCK },
{ 967, ERRCODE_BASIC_BAD_BRACKETS },
{ 968, ERRCODE_BASIC_BAD_DECLARATION },
{ 969, ERRCODE_BASIC_BAD_PARAMETERS },
{ 970, ERRCODE_BASIC_BAD_CHAR_IN_NUMBER },
{ 971, ERRCODE_BASIC_MUST_HAVE_DIMS },
{ 972, ERRCODE_BASIC_NO_IF },
{ 973, ERRCODE_BASIC_NOT_IN_SUBR },
{ 974, ERRCODE_BASIC_NOT_IN_MAIN },
{ 975, ERRCODE_BASIC_WRONG_DIMS },
{ 976, ERRCODE_BASIC_BAD_OPTION },
{ 977, ERRCODE_BASIC_CONSTANT_REDECLARED },
{ 978, ERRCODE_BASIC_PROG_TOO_LARGE },
{ 979, ERRCODE_BASIC_NO_STRINGS_ARRAYS },
{ 1000, ERRCODE_BASIC_PROPERTY_NOT_FOUND },
{ 1001, ERRCODE_BASIC_METHOD_NOT_FOUND },
{ 1002, ERRCODE_BASIC_ARG_MISSING },
{ 1003, ERRCODE_BASIC_BAD_NUMBER_OF_ARGS },
{ 1004, ERRCODE_BASIC_METHOD_FAILED },
{ 1005, ERRCODE_BASIC_SETPROP_FAILED },
{ 1006, ERRCODE_BASIC_GETPROP_FAILED },
{ 1007, ERRCODE_BASIC_COMPAT },
{ 0xFFFF, ErrCode(0xFFFFFFFFUL) } // End mark
In addition (see StarBASIC::GetSfxFromVBError), when VBA is enabled, codes map like this:

1, 2, 4, 8, 12, 73: ERRCODE_NONE;
10: ERRCODE_BASIC_ARRAY_FIX;
14: ERRCODE_BASIC_STRING_OVERFLOW;
16: ERRCODE_BASIC_EXPR_TOO_COMPLEX;
17: ERRCODE_BASIC_OPER_NOT_PERFORM;
47: ERRCODE_BASIC_TOO_MANY_DLL;
92: ERRCODE_BASIC_LOOP_NOT_INIT;

So maybe first no-op that line or try an equivalent function like
OK

Field95 · 09-20-2018, 03:35 PM

pandas-datareader is a python module for reading historical data from Yahoo, Google etc.

However, there's reports that Yahoo and others have changed their API and according to this, google did too recently

Quote:

As of v0.6.0 Yahoo!, Google Options, Google Quotes and EDGAR have been immediately deprecated due to large changes in their API and no stable replacement.

Reason for mention that specific tool is because it's recently updated and saying that it's not working.
So other older tools that may of depended on similar methods may be broken (possibly including this excel script)

EDIT:

It seems yahoo may be working.
I wrote up something but I'm not sure if the output is what you're looking for?

STOCK ABB 2017-2018 with Date / High / Low / Open / Close / Volume / Adj Close
https://i.imgur.com/x0IwHFz.png

grumpyskeptic · 09-22-2018, 05:25 AM

Thanks, the output is exactly what I would want.

I am principally interested in British stocks. There are designated for example as TSCO.L for Yahoo Finance, and LON:TSCO for Google Finance. MRW.L and LON:MRW are two other examples.

It seems that Google are no longer providing historical stock data (or with great regret any stock screening), but the British Yahoo webpage for historical data for TSCO.L is https://uk.finance.yahoo.com/quote/T...y&frequency=1d

It may be possible to copy the URL for the .csv file and replace the details. The "crumb" at the end might not be a problem if you are already viewing the webpage and hence have the cookie that it apparently checks for.

The tinkering I have done so far with the .csv file URL indicates that the period1 and period2 numbers are in seconds with a start date of the first of January 1970. I tried replacing the period1 number with 864000, which corresponds to the 11th. of January 1970, and it did successfully download a csv file starting from the earliest dates it had available.

I have begun trying to learn Python, but do not know how to use URLS within it yet.

Edit/update: The bare-bones program I have in mind would involve going to a webpage like the link above, copying the link for the .csv file download, and then entering this URL string into the program. The program then replaces the period1 number with 0 and then replaces the stock code with a list I have in a text file, saving the downloaded .csv files to hard-disk. It does this slowly, to avoid upsetting the server.

Field95 · 09-25-2018, 03:37 PM

Doing this "correctly" might not be worth the effort in python due to the frequent changes of the api. Unless you use pandas-datareader

Here's a "hack" you can try.
On the finanical page with the "download csv" buttom, hit F12 for web developer tools (Firefox)
Go to the network tab, then click on download csv on the actual website

Right click on the top request which should have a "200" (valid) response on it in the web developer tools. Copy the request as a curl command and copy it in a similar fashion to the below script. The \ and newlines before every -H isn't needed, but helps with readability. Copy and paste all on one line in fine for the curl command.

Code:

#!/bin/bash                                                                     
                                                                                
for stock in "$@";                                                           
do                                                                                  
    curl --output "$stock".csv 'https://query1.finance.yahoo.com/v7/finance/download/'"$stock"'?period1=583714800&period2=1537570800&interval=1d&events=history&crumb=dsOmzbl9M5J' \
        -H 'Host: query1.finance.yahoo.com' \                                   
        -H 'User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:56.0) Gecko/20100101 Firefox/56.0' \
        -H 'Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8' \
        -H 'Accept-Language: en-US,en;q=0.5' \                                  
        --compressed \                                                          
        -H 'Referer: https://finance.yahoo.com/quote/TSCO.L/history?period1=583714800&period2=1537570800&interval=1d&filter=history&frequency=1d' \
        -H 'Cookie: B=ad1nsepd1dv3q&b=4&d=pTj4IhNpYEJI_8oxA.IfZwx6A9c-&s=1f&i=91HWTY69RuHT.aKjFJv_; F=d=So0FgyM9vK_KxdqVQRqM78ZadlGZ677d1rWGiBuahi4kKkAbog--; PH=l=en-US; AO=u=1; ucs=fs=1&lnct=1513112013&bnpt=1522210361&eup=2; GUC=AQABAQFbp4Fci0IhagTA&s=AQAAAFE7YSOB&g=W6Y76Q; PRF=t%3DTSCO.L' \
        -H 'Connection: keep-alive' \                                           
        -H 'Upgrade-Insecure-Requests: 1'
                                                      
done

Code:

./download_stock.sh AAPL WHATEVER

Code:

cat AAPL.csv
Date,Open,High,Low,Close,Adj Close,Volume                                       
1988-06-30,1.651786,1.669643,1.642857,1.651786,0.100921,28672000                
1988-07-01,1.660714,1.674107,1.651786,1.660714,0.101466,23634800                
1988-07-05,1.660714,1.687500,1.647321,1.687500,0.103103,26112800                
1988-07-06,1.683036,1.696429,1.647321,1.660714,0.101466,39138400

grumpyskeptic · 10-02-2018, 07:38 AM

Thank you greatly, Field95, very good of you to write the code.

I have not tried it yet, and know nothing about Bash nor have I ever used it.

What book that would provide just enough knowledge to use the code above would anyone recommend?

Thanks.

Field95 · 10-02-2018, 09:38 AM

Quote:

Originally Posted by grumpyskeptic

Thank you greatly, Field95, very good of you to write the code.

I have not tried it yet, and know nothing about Bash nor have I ever used it.

What book that would provide just enough knowledge to use the code above would anyone recommend?

Thanks.

You can always look at man bash. There are many guides on the internet If you've ever used the terminal, it's likely you've used bash. This is just a bit of looping structure.
There's very little actual bash code here so I'll break it down

This tells the file that it is a bash script so that you can run it like "./file" instead of "bash file"

Code:

#!/bin/bash

A typical for loop. Note the "$@" Not positive the quotes are needed here.
If you were to run a program like ./file a b c the $@ would contain "a b c" and the for loop would iterate
like

Code:

./file a b c
for i in $@
do
    echo "$i"
done
a
b
c

Code:

for stock in "$@";                                                           
do

This is the copied command from web developer tools.
A few changes are made, where it will output (instead of to the screen), usage of "$stock" for the for loop so that you can quickly download each one. Note I didn't write this part. It was just copied from the browser

Code:

curl --output "$stock".csv 'https://query1.finance.yahoo.com/v7/finance/download/'"$stock"'?period1=583714800&period2=1537570800&interval=1d&events=history&crumb=dsOmzbl9M5J' -H 'Host: query1.finance.yahoo.com' -H 'User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:56.0) Gecko/20100101 Firefox/56.0' -H 'Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8' -H 'Accept-Language: en-US,en;q=0.5' --compressed -H 'Referer: https://finance.yahoo.com/quote/TSCO.L/history?period1=583714800&period2=1537570800&interval=1d&filter=history&frequency=1d' -H 'Cookie: B=ad1nsepd1dv3q&b=4&d=pTj4IhNpYEJI_8oxA.IfZwx6A9c-&s=1f&i=91HWTY69RuHT.aKjFJv_; F=d=So0FgyM9vK_KxdqVQRqM78ZadlGZ677d1rWGiBuahi4kKkAbog--; PH=l=en-US; AO=u=1; ucs=fs=1&lnct=1513112013&bnpt=1522210361&eup=2; GUC=AQABAQFbp4Fci0IhagTA&s=AQAAAFE7YSOB&g=W6Y76Q; PRF=t%3DTSCO.L' -H 'Connection: keep-alive' -H 'Upgrade-Insecure-Requests: 1'

Even if you don't fully understand, you can follow these steps to solve your current problem

Code:

wget 'https://pastebin.com/raw/Wd6pwruC' -O download_stock.sh
chmod u+x download_stock.sh

Then run as so

Code:

./download_stock AAPL

grumpyskeptic · 10-03-2018, 04:42 AM

I now see that the main part of it is due to the Curl program, and the Bash part is, as you say, just there to loop through the stock codes.

Thanks.