search

cmallwitz / Financials-Extension

Extension for LibreOffice Calc to access stock market data
Other
137 stars 17 forks source link

Data does not exist #29

Open R7914T opened 3 years ago

R7914T commented 3 years ago

https://uk.finance.yahoo.com/quote/SLP.L/history?period1=1622073600&period2=1622073600&interval=1d&filter=history&frequency=1d&includeAdjustedClose=true

For some reason there is no data on 2021-05-27 for LSC.L at yahoo. The absence of data causes a long delay when loading my spreadsheet. It seems to stop loading whilst trying to get the data and it takes a long time before it gives up. Is there a way to reduce the number of times that the formula keeps repeating when no data is available?

I think the request is still valid because although the web site shows no data, whilst I was drafting this request at least 10m after the sheet stopped trying to get the data a price of 126 appeared in the cell. The formula I am using to get the data is GETHISTORIC("slp.l",90,CD1,"YAHOO"). I see no justification on the yahoo page for the price of 126

R7914T commented 3 years ago

I do not wish to take up your time unnecessarily and I have been thinking a lot as to why I had this problem. I am currently testing my set up to see if splitting the existing spreadsheet into two separate ones will make a difference. The first one was very large with a lot of complex formulas. I will report back if my original request is still valid.

cmallwitz commented 3 years ago

One of the problems with LibreOffice extensions is that it just declares functions as entry points and has no control over when and how it is invoked.So in our case, I assume if I receive a symbol I need to make a network call. This (slowly) fetches the data. If there is another call for the same symbol (but maybe different data point) within the next minute, I will reuse the data retrieved in first call. A spreadsheet fetching price/name/currency/etc for same symbol will therefore only do one network call per symbol. There will be always be at least one network call per symbol. Even if the web site would allow me to retrieve data for multiple symbols (they don't) the extension would only see the symbols used in spreadsheet one after another.

You can see this when pressing CTRL-SHIFT-F9 - if you do it twice, the second time will be quick but not change anything. If you wait a little, it will be slow again but possibly update the cell data.

Now to incorrect symbols: they are not cached. So if you have a few calls for price/name/currency/etc on a symbol that is not recognized, all these calls will trigger a (slow) network call. Issues on the web site (e.g. data for an otherwise correct symbol is temporarily not available) will cause the same slow down when refreshing a spreadsheet.

Fundamentally I can't force data to become available if the web site has server-side issues but maybe I could look into temporarily cache the fact that data is unavailable and prevent LibreOffice seem to block/hang for a while.

casper43 commented 3 years ago

"One of the problems with LibreOffice extensions is that it just declares functions as entry points and has no control over when and how it is invoked." Did you talk with the LO-devs about this issue?

cmallwitz commented 3 years ago

I haven't but this based on the documentation and examples I was able to find on the web.

casper43 commented 3 years ago

At least let them know there is a problem, maybe it's just not documented. There is a helpful guy on their libreoffice IRC channel (mike kaginski or similar) you might talk to.

Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ cmallwitz @.***> schrieb am Mittwoch, 30. Juni 2021 um 15:02:

I haven't but this based on the documentation and examples I was able to find on the web.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub, or unsubscribe.