LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 09-16-2018, 12:47 PM   #1
grumpyskeptic
Member
 
Registered: Apr 2016
Posts: 222

Rep: Reputation: Disabled
Convert Excel macros to run with LibreOffice Calc?


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.

Last edited by grumpyskeptic; 09-16-2018 at 12:48 PM.
 
Old 09-16-2018, 12:58 PM   #2
pan64
LQ Guru
 
Registered: Mar 2012
Location: Hungary
Distribution: debian/ubuntu/suse ...
Posts: 11,287

Rep: Reputation: 3393Reputation: 3393Reputation: 3393Reputation: 3393Reputation: 3393Reputation: 3393Reputation: 3393Reputation: 3393Reputation: 3393Reputation: 3393Reputation: 3393
looks like libreoffice can't understand those macros. Would be nice to post that error message. At least.
 
Old 09-17-2018, 05:29 AM   #3
grumpyskeptic
Member
 
Registered: Apr 2016
Posts: 222

Original Poster
Rep: Reputation: Disabled
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.

Last edited by grumpyskeptic; 09-17-2018 at 01:36 PM.
 
Old 09-17-2018, 10:47 PM   #4
John VV
LQ Muse
 
Registered: Aug 2005
Location: A2 area Mi.
Posts: 17,380

Rep: Reputation: 2571Reputation: 2571Reputation: 2571Reputation: 2571Reputation: 2571Reputation: 2571Reputation: 2571Reputation: 2571Reputation: 2571Reputation: 2571Reputation: 2571
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

Last edited by John VV; 09-17-2018 at 10:50 PM.
 
Old 09-18-2018, 12:44 AM   #5
Michael Uplawski
Member
 
Registered: Dec 2015
Location: Normandy, France
Distribution: Debian buster/sid
Posts: 691
Blog Entries: 22

Rep: Reputation: 422Reputation: 422Reputation: 422Reputation: 422Reputation: 422
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?

Last edited by Michael Uplawski; 09-18-2018 at 12:46 AM.
 
Old 09-18-2018, 04:02 AM   #6
grumpyskeptic
Member
 
Registered: Apr 2016
Posts: 222

Original Poster
Rep: Reputation: Disabled
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.

Last edited by grumpyskeptic; 09-18-2018 at 05:50 AM.
 
Old 09-18-2018, 06:00 AM   #7
grumpyskeptic
Member
 
Registered: Apr 2016
Posts: 222

Original Poster
Rep: Reputation: Disabled
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.
 
Old 09-20-2018, 02:09 AM   #8
AnanthaP
Member
 
Registered: Jul 2004
Location: Chennai, India
Distribution: UBUNTU 5.10 since Jul-18,2006 on Intel 820 DC
Posts: 877

Rep: Reputation: 208Reputation: 208Reputation: 208
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

Last edited by AnanthaP; 09-20-2018 at 02:13 AM.
 
Old 09-20-2018, 03:35 PM   #9
Field95
LQ Newbie
 
Registered: Sep 2018
Location: xmpp:zemri@dismail.de
Posts: 4

Rep: Reputation: Disabled
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
Attached Files
File Type: txt AAPL.txt (27.4 KB, 2 views)

Last edited by Field95; 09-20-2018 at 06:26 PM.
 
Old 09-22-2018, 05:25 AM   #10
grumpyskeptic
Member
 
Registered: Apr 2016
Posts: 222

Original Poster
Rep: Reputation: Disabled
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.

Last edited by grumpyskeptic; 09-22-2018 at 05:56 AM.
 
Old Yesterday, 03:37 PM   #11
Field95
LQ Newbie
 
Registered: Sep 2018
Location: xmpp:zemri@dismail.de
Posts: 4

Rep: Reputation: Disabled
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

Last edited by Field95; Yesterday at 03:39 PM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Libreoffice Calc vs Excel speed question redart Linux - Software 17 11-15-2015 07:25 PM
[SOLVED] where are macros stored in libre office calc 3.6.2+ knobby67 Linux - Software 3 04-25-2013 11:28 PM
Convert from XML in excel format to Excel XLS file CPAN module Perseus Programming 9 10-14-2011 12:39 PM
openoffice calc not running MS Excel macros VicRic Linux - Software 4 10-18-2010 10:19 PM
Macros in OO vs Excel sikandar Linux - Software 0 09-01-2003 12:47 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 12:28 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration