search

cmallwitz / Financials-Extension

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

General feedback from users requested (create new issue for specific questions) #10

Open cmallwitz opened 3 years ago

cmallwitz commented 3 years ago

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.

omoknen commented 3 years ago

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.

ikyquocked commented 3 years ago

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 ",".

ikyquocked commented 3 years ago

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.

ikyquocked commented 3 years ago

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'"

ikyquocked commented 3 years ago

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

cat-herder commented 3 years ago

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.

cmallwitz commented 3 years ago

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

cmallwitz commented 3 years ago

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?

cat-herder commented 3 years ago

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.

cmallwitz commented 3 years ago

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.

cat-herder commented 3 years ago

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.

cat-herder commented 3 years ago

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!

cmallwitz commented 3 years ago

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

ruimnetob commented 3 years ago

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'
cmallwitz commented 3 years ago

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...

ruimnetob commented 3 years ago

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.

logos88 commented 2 years ago

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:

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&region=usa&culture=en-US

kastaldi commented 2 years ago

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.

cmallwitz commented 2 years ago

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)

jbourvic commented 2 years ago

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.

CBFR commented 2 years ago

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 .

CBFR commented 2 years ago

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.

cmallwitz commented 2 years ago

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

CBFR commented 2 years ago

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

cmallwitz commented 2 years ago

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)

CBFR commented 2 years ago

Please let me know if I can furnish any more info to help.....

cmallwitz commented 2 years ago

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...

CBFR commented 2 years ago

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

kastaldi commented 2 years ago

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.

CBFR commented 2 years ago

I have just upgraded from 3.0.5 to 3.0.6 and Yahoo and FT are both working well.

spacecatz03 commented 2 years ago

Hi, I've been using this extension for a while in a spreadsheet and it has been working great, thank you so much! This morning it was working fine as usual but now I'm getting an error, e.g. =GETREALTIME("IBM",21,"YAHOO") results in - Yahoo.getRealtime(IBM, 21) - urlopen: argument must be an int, or have a fileno() method. Usually, I would just need to try later or quit LibreOffice and re-open sheet. Now it doesn't seem to be pulling in the data. Using Win11, LibreOffice 7.3.3.2 and extension version 3.0.6.

cmallwitz commented 2 years ago

Confirmed - Yahoo not working for me neither. Let me take a look - hoping it is just a temporary glitch and not some web site re-design requiring more work...

spacecatz03 commented 2 years ago

Cool, thanks for the quick response.

cmallwitz commented 2 years ago

Try the new release 3.0.7

I had to refresh the Yahoo cookies again. Yahoo has some odd European Union GDPR cookie consent handling that is a bit cumbersome to deal with...

spacecatz03 commented 2 years ago

I'm getting this error when trying to install (com.sun.star.uno.RuntimeException) { { Message = "<class 'FileNotFoundError'>: [Errno 2] No such file or directory: 'C:\\Users\\robma\\AppData\\Roaming\\LibreOffice\\4\\user\\uno_packages\\cache\\unopackages\\lu603238whp.tmp\\Financials-Extension%20(1).oxt\\financials.py', traceback follows\X000a File \"C:\Program Files\LibreOffice\program\pythonloader.py\", line 146, in writeRegistryInfo\X000a mod = self.getModuleFromUrl( locationUrl )\X000a File \"C:\Program Files\LibreOffice\program\pythonloader.py\", line 93, in getModuleFromUrl\X000a with open( filename, encoding='utf_8' ) as fileHandle:\X000a\X000a", Context = (com.sun.star.uno.XInterface) @0 } }

spacecatz03 commented 2 years ago

Sorry, my bad. Once I completely uninstalled the previous version it worked! Thanks again, you da man!

cmallwitz commented 2 years ago

No worries - the way LibreOffice handles extensions is a bit brittle - e.g. if your rename the downloaded extension file ie. from Financials-Extension.oxt to Financials-Extension-3.0.7.oxt the installation will fail :-(

TheSerapher commented 1 year ago

Hey @cmallwitz,

Just noticed that quotes are not refreshing and are showing N/A for my sheet. Using MacOS and not sure how to get you any logs but maybe something changed on Yahoo's end again?

EDIT: Went into the filesystem and edited this file and added new cookie strings I found when checking the site with a browser.

~/Library/Application Support/LibreOffice/4/user/uno_packages/cache/uno_packages/lu57407bpyjtq.tmp_/Financials-Extension.oxt replacing strings for A1, A1S, A3 and EuConsent cookie data. Re-loaded and it seems to have worked though I am not sure that actually do anything :D

cmallwitz commented 1 year ago

Thanks for the notification - yes - it is broken for me too! I would certainly hope they just have messed with there cookies again instead of breaking the web site for us - I will update the extension accordingly. Maybe I need to extend the code a bit to fetch/update cookies as required but I don't want to add additional network round trips for all Yahoo request all the time... Cheers!

TheSerapher commented 1 year ago

Can extensions be integrated to the Libreoffice settings? It may help to have a sane default that works at the time of writing but some ability to overwrite this setting by simply putting that new cookie string into the settings page. I have mine working again by adjusting the above cookies.

cmallwitz commented 1 year ago

Pushed new version 3.0.8

TheSerapher commented 1 year ago

Removed the old (modified) version 3.0.7 and installed the new 3.0.8: Works 👍

spacecatz03 commented 1 year ago

Thanks for the update, working great again!

SoftwareExplorerMe commented 1 year ago

Worked great for getting Yahoo last price for a couple dozen stocks into my calc spreadsheet. But when I tried to add a few new stock tickers by copy/pasting the formula of a working cell into the formula bar and editing just the stock sticker it fails and says #NAME? in the cell but the older cells still provide updated info. EXAMPLE: =ROUND(AutoAddIn.Financials.getRealtime("CHMI",21,"Yahoo"),4) ==> COPY CELL and PASTE becomes =ROUND(AutoAddIn.Financials.getRealtime("CHMI",21,"Yahoo"),4) and works in new cell too BUT =ROUND(AutoAddIn.Financials.getRealtime("CHMI",21,"Yahoo"),4) ==> COPY FORMULA and PASTE in new cell formula=> =ROUND(autoaddin.financials.getrealtime("CHMI",21,"Yahoo"),4) loses Capitalization and results in #NAME? in cell

Win 10 PRO 1H22 and 2H22 LibreOffice 7.4.2.3 64bit (recently updated)

I'm not sure but I thought the formulas said =GETREALTIME... until after the LibreOffice update that changed it to =AutoAddIn.Financials.getRealtime...

Any clues as to how to fix?

cmallwitz commented 1 year ago

I would usually assume lower/upper case function names doesn't make a difference. I haven't tested the very latest LibreOffice - maybe they have changed how functions are addressed in formulae but would be surprised they did and broke anything. Have you tried removing the 'AutoAddIn.Financials' bit and see if it still works?

TheSerapher commented 1 year ago

Looks like YT Financial changed something again, not getting any data. Replacing the cookies didn't seem to work this time (maybe wrong files or something) but it looks like a few cookies, including EuConsent, have new data.

TheSerapher commented 1 year ago

Since I felt that YT was unreliable and I liked having up-to-date quotes for my cryptos at least, I went ahead and did this: https://github.com/cmallwitz/Financials-Extension/pull/67

cmallwitz commented 1 year ago

Arrggghhh - I was planning to spend some time over Christmas to put in place a more long-term/dynamic solution for the Yahoo cookie issue but they seem to have decided to create a (pre-)Christmas present for us... I will take look.

cmallwitz commented 1 year ago

Published new version 3.0.9 in the meantime

spacecatz03 commented 1 year ago

Awesome, thanks for the quick fix much better than N/A# . Now just need an extension to keep the market from going down, lol.