Financials-Extension
Financials-Extension copied to clipboard
cmallwitz
General feedback from users requested (create new issue for specific questions)
Please leave feedback here about what platforms you use the extension on, what you use it for, what symbols/exchanges are important to you and what you think is missing.
LibreOffice Windows 10 (used mortee instructions in another post). Using it for keeping track of some standard mutual funds which works great. Thanks for creating the extension.
Windows 7. LibreOffice 7.0.4.2 is autocorrecting any digit with the comma in the beginning ex. ",21" to "0,21" and then fx shows error:501. The symbol ";" works fine. Its better to correct the readme manual and write ";" instead ",".
Also Im notice the bug, =GETREALTIME("MCX:MOEX";21;"GOOGLE") returns the value 15726, this is result without comma, real value is 157,26 rub.
Also noticed with =IF(Y24="";"";GETREALTIME(V24;21;"yahoo")) Y24 - its the quantity of stocks, V24 - ticker. Sometimes fx returns error "Yahoo.getRealtime(TBIOA.ME, 21) - process: 'currency'"
Hi, its me again =) Noticed some strange auto-update behavior on specific sheet. When I edit any cage, even an empty one, the waiting-cursor appear for few minutes and all stocks data starting updating (I see it in trace.log). Even I click to the other sheet it starts update, quite unconfortable and very slow, about 40sec waiting every time. Of course, I am not pressing ctrl-shift-F9 but it works like some auto-update. Its more usable if its updating only by command... Attaching a video illustration and the table here. Strange sheet.zip
UPD: AutoCalulation is ON by default [Data - Calculate - AutoCalculate], and calc recalculating everytime all data in the sheet after editing any unrelated cage... I guess its the libre calc bug...
https://user-images.githubusercontent.com/63292793/106952967-e278c480-6742-11eb-94b8-9d82ec24419f.mp4
Thank you for this nice extension. I use it with LibreOfficeDev 7.1.2 x64 on Windows 10. One thing that would be great is that I see that Yahoo has Option data, so given a stock symbol, date, Call or Put, and strike price it would be possible to get a quote from Yahoo for an option. They structure the option symbol in this way: (here is one for Ford) F210219C00012000 (Stock Symbol, 2 digit year, 2 digit month, 2 digit day, C for call or P for put, then the strike price with varying zeroes before and after depending on the number of digits in the strike price. I do not know if you trade options but it would be nice to get some options data in an automated way such as Bid, Ask, Change%, Volume, Open Interest, and Implied Volatility. Thanks a lot.
I think it should be possible to support options at least a little. Looking at e.g. https://uk.finance.yahoo.com/quote/F210219C00012000 it seems the data is just in different fields. But I can't see interest and volatility details. Additionally, you as the user would need to pass the option symbol used by Yahoo to the extension function as it would be impossible to calculate them without a lot of additional data (starting e.g. trading calendars etc)
But interesting stuff nonetheless. Do you have another source of this information - as in a different web site? I have always looked at Bloomberg and Reuters web sites for comparison when working out what is valid/invalid but I can't seem to see any publicly available option data. Maybe it is time to add an optional paid-for data provider?
Christian
Funnily I see option data provided by one of the German banks I deal with occationaly: https://www.comdirect.de/inf/search/optionsscheine.html?IC_BYPASS=1&SEARCH_VALUE=FORD+CALL+02&SEARCH_REDIRECT=true&REDIRECT_TYPE=FULLTEXT
If you click on the links you see quite a few data points for these options (for various strikes). German as the language used by the web site wouldn't be deterrent to add stuff like this to the extension but the prices from Germany (in Euro) may not be of much use to you. Maybe you have a bank or brokerage web site in the US publishing data like this to entice people trading/brokering with them?
Yes, barchart.com is a site that provides option chains also https://www.barchart.com/stocks/quotes/F/options They have an API but it is not free sadly. A paid API is probably the best way to go for options data since it is so complex due to the varying option chains and dates. I usually just grab the info I need from my Brokerage and add it to my spreadsheet manually when it comes to options.
I added some support for options - have a look at the new version and the examples.ods - it has an example for an option too.
I added some support for options - have a look at the new version and the examples.ods - it has an example for an option too.
I will check it out here shortly. Appreciate your work on the extension.
I added some support for options - have a look at the new version and the examples.ods - it has an example for an option too.
I have implemented the new Financials_Extension into my options spreadsheet and it is working very well. I've built a formula to construct a properly formatted option symbol for Yahoo from the existing data in my spreadsheet and everything works nicely with the GETREALTIME function. A huge timesaver, thank you!
Little update for options (because I hadn't come accros this on Yahoo before): one can access an overview of available options e.g. for IBM like this: https://finance.yahoo.com/quote/IBM/options?p=IBM&straddle=true
Hi using this extension for personal usage. Just trying to get Beta from Yahoo and found that doesnt work properly on some symbols i.e. "XLF".
My system:
Libreoffice About: Version: 6.4.6.2 Build ID: 1:6.4.6-0ubuntu0.20.04.1 CPU threads: 12; OS: Linux 5.4; UI render: default; VCL: gtk3;
Python3: Python 3.8.5 (default, Jan 27 2021, 15:41:15) [GCC 9.3.0] on linux
Ubuntu 20.04.2 LTS
Followed the Readme but can't run the unittests as expected. Should I open an issue, or maybe someone has a tip? Thank you. Error follows below.
======================================================================
ERROR: test_ft (unittest.loader._FailedTest)
----------------------------------------------------------------------
ImportError: Failed to import test module: test_ft
Traceback (most recent call last):
File "/usr/lib/python3.8/unittest/loader.py", line 436, in _find_test_path
module = self._get_module_from_name(name)
File "/usr/lib/python3.8/unittest/loader.py", line 377, in _get_module_from_name
__import__(name)
File "/home/toor/Financials-Extension/src/test_ft.py", line 17, in <module>
import financials
File "/home/toor/Financials-Extension/src/financials.py", line 22, in <module>
import unohelper
File "/usr/lib/python3/dist-packages/unohelper.py", line 19, in <module>
import uno
File "/home/toor/.local/lib/python3.8/site-packages/uno/__init__.py", line 4, in <module>
from base import Element, Css, Payload, UnoBaseFeature, UnoBaseField
ModuleNotFoundError: No module named 'base'
======================================================================
ERROR: test_google (unittest.loader._FailedTest)
----------------------------------------------------------------------
ImportError: Failed to import test module: test_google
Traceback (most recent call last):
File "/usr/lib/python3.8/unittest/loader.py", line 436, in _find_test_path
module = self._get_module_from_name(name)
File "/usr/lib/python3.8/unittest/loader.py", line 377, in _get_module_from_name
__import__(name)
File "/home/toor/Financials-Extension/src/test_google.py", line 17, in <module>
import financials
File "/home/toor/Financials-Extension/src/financials.py", line 22, in <module>
import unohelper
File "/usr/lib/python3/dist-packages/unohelper.py", line 19, in <module>
import uno
File "/home/toor/.local/lib/python3.8/site-packages/uno/__init__.py", line 4, in <module>
from base import Element, Css, Payload, UnoBaseFeature, UnoBaseField
ModuleNotFoundError: No module named 'base'
======================================================================
ERROR: test_yahoo (unittest.loader._FailedTest)
----------------------------------------------------------------------
ImportError: Failed to import test module: test_yahoo
Traceback (most recent call last):
File "/usr/lib/python3.8/unittest/loader.py", line 436, in _find_test_path
module = self._get_module_from_name(name)
File "/usr/lib/python3.8/unittest/loader.py", line 377, in _get_module_from_name
__import__(name)
File "/home/toor/Financials-Extension/src/test_yahoo.py", line 19, in <module>
import financials
File "/home/toor/Financials-Extension/src/financials.py", line 22, in <module>
import unohelper
File "/usr/lib/python3/dist-packages/unohelper.py", line 19, in <module>
import uno
File "/home/toor/.local/lib/python3.8/site-packages/uno/__init__.py", line 4, in <module>
from base import Element, Css, Payload, UnoBaseFeature, UnoBaseField
ModuleNotFoundError: No module named 'base'
Maybe a installation problem of the LibeOffice SDK ? You seem to find the unohelper (in system path) but the uno package itself is coming from local directory (/home/toor/.local/) ?
Just a note: Google is broken - they change the whole website - all the Google tests will fail therefore...
Maybe a installation problem of the LibeOffice SDK ? You seem to find the unohelper (in system path) but the uno package itself is coming from local directory (/home/toor/.local/) ?
Just a note: Google is broken - they change the whole website - all the Google tests will fail therefore...
Hi @cmallwitz , thank you for the tip. I can confirm that the problem was I had done a
pip3 install uno
And the library was conflicting with the correct one. It then started throwing issues in librecalc itself and crashing with std:bad_alloc ...
Fixed.
Works perfectly fine in my setup.
Thank you for this great extension. I use it with LibreOffice 7.1.5.2 on Arch Linux, and mainly to get info to calculate intrinsic value and to compare stocks.
To calculate intrinsic value in different ways (FCF, Dividends, EPS) it would be useful if we could get this values:
- FCF TTM (ex: https://finance.yahoo.com/quote/MMM/cash-flow?p=MMM)
- Diluted EPS (ttm): Is more accurate than normal EPS (ex: https://finance.yahoo.com/quote/MMM/key-statistics?p=MMM)
- Trailing Annual Dividend Rate (ex: https://finance.yahoo.com/quote/MMM/key-statistics?p=MMM)
It would be interesting to have some growth rates like 5-Year Annual Average Net Income or 5-Year Annual Average Dividends from https://www.msn.com/en-us/money/stockdetails/analysis/fi-a1xroc
An finally, a 5 years P/E average would be also awesome to compare with present valuations. I have found MSN has P/E Ratio 5-Year High and P/E Ratio 5-Year Low so it could be easy to calculate the average: https://www.msn.com/en-us/money/stockdetails/analysis/fi-a1xroc
Morningstar also has 5Y Avg: http://financials.morningstar.com/valuation/price-ratio.html?t=MMM®ion=usa&culture=en-US
I'm using this extension on Windows 10 x64 and LibreOffice 7.2.0.4 x64, just simple realtime stock prices to keep an eye on my portfolio. No issues until now but I had to use ";" instead of "," in Usage github section to make it work. Thanks for the great job, very useful.
To calculate intrinsic value in different ways (FCF, Dividends, EPS) it would be useful if we could get this values: ...
I could certainly take a look to make more data points available but in general I try to have similar coverage between Yahoo and FT to keep them interchangable. Adding more providers has been suggested as well but the problem here is whether they have similar coverage fo rasset classes (FX, equity, funds, etc)
Just happened upon your post that you left to a search for Does LO support stock searches. Have a Win10 x64. Wasn't too sure about the proper loading of the extension (couldn't find 'Tools, Extensions' under Calc, but when I downloaded and ran your extension, it appeared to load and that was it. So hesitantly, I tried the command on a new page and BOOYAH! It worked! Even substituting a cell for the name. You are the MAN! I so appreciate it. I have been searching for so long for something like this. Thanks.
Hello, I am a retired accountant and for years have struggled finding a complete stock quote tool. I have always used windows throughout my professional life. I have spent ages withe Excel which gets more and more complicated in a quest for sophistication and have given up. I then settled for Google Sheets which is not comprehensive and not totally reliable. I have now the time to explore the open source world. Your SIMPLE and FAST and apparently COMPREHENSIVE solution is just what I have been looking for. Now I can rebuild my portfolio in Calc and I think it will be a great time-saver for me. I will update when I have finished my Portfolio. Thank you for this extension. A big plus for me is that I can even use this in privacy .
I have now reworked my portfolios and I had to write a few macros (which was challenging) since FT does not give historic info. It all works fine using the FT source. I cannot seem to get the Yahoo source to work. Here is the trace log extract which shows "r" which I guess must be the share price as "none". I also attach the ft version which correctly picks up the price: 2021-12-14 19:09:20.451205 getRealtime *args=('GSK:LSE', 21.0, 'ft') r='1598.0' 0.083 ms 2021-12-14 19:09:39.169863 getRealtime *args=('GSK:L', 21.0, 'yahoo') r='None' 673.045 ms I would be grateful for some help.
This just seems a typo - it should be (with ".L")
=GETREALTIME("GSK.L", 21, "YAHOO")
See https://github.com/cmallwitz/Financials-Extension/blob/master/Yahoo%20Finance%20-%20Exchanges%20and%20data%20provider.pdf
Thank you for the prompt reply but correcting the : to . still appears not to work. I must be missing something.
2021-12-14 22:12:44.762468 getRealtime *args=('GSK.L', 21.0, 'yahoo') r='None' 197.849 ms
Hmm - not sure what is happening here... Using =GETREALTIME("GSK.L", 21, "YAHOO") creates the same log line on my side just with a proper value (instead on None)
Can you try to put the formula in an otherwise empty spread sheet or open the file examples.ods ? The later has Yahoo and FT examples - just to make sure it is working there...
Thank you for your prompt reply I loaded examples.ods and still had same error - All works with FT but with Yahoo I get "#N/D" message Similarly when I opened a blank file and tried.
I notice in the trace log below that the middle term "21" is in fact picked up as "21.0". This also happens with requests for FT but FT requests are all working fine nonetheless.
I am running on Windows 10 in France. I have tried changing the country and regional settings in Windows from France to Great Britain and changing the decimal point from "," (as in France) to ".". However there is no change - I still have FT working and Yahoo showing #N/D.
2021-12-16 21:18:20.196447 getRealtime *args=('IBM', 21.0, 'Yahoo') r='None' 576.701 ms 2021-12-13 13:29:32.737615 getRealtime *args=('EURGBP', 21.0, 'ft') r='0.8504' 392.884 ms
Thanks for the extension. Prices weren't loading anymore but I upgraded from 3.0.3 to 3.0.6 and the problems were gone.
I have just upgraded from 3.0.5 to 3.0.6 and Yahoo and FT are both working well.