cmallwitz / financials-extension Goto Github PK

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.

Originally posted by @ikyquocked in #10 (comment)

Hi there,

How would you suggest to deal with error message concerning ssl certificate, as you may see on the picture below?

Would it be possible to draw turnover figures from some data source?

Hi, just tried installing the extension (version 2.1.0) and Libre Office returns the following :

(com.sun.star.uno.RuntimeException) { { Message = "<class 'SyntaxError'>: invalid syntax (financials.py, line 51), traceback follows\X000a File "C:\Program Files\LibreOffice\program\pythonloader.py", line 149, in writeRegistryInfo\X000a mod = self.getModuleFromUrl( locationUrl )\X000a File "C:\Program Files\LibreOffice\program\pythonloader.py", line 102, in getModuleFromUrl\X000a codeobject = compile( src, encfile(filename), "exec" )\X000a\X000a", Context = (com.sun.star.uno.XInterface) @0 } }

Running latest stable Libre Office 6.3.6.2 on Windows 10.

Using LO Windows 6.4.7.2 (x64)
Get GETREALTIME command works, but GETHISTORIC command doesn't work.
We received an error in the cell of:

Date format not supported: '2020-12-01'

We can not figure out why that happened. We tried alternate dates and symbols still the same error.
I wish I knew something about Python.
I did check the error message and I know it is about "dateutil" and I checked it is installed in LO.

We looked at the download on the page and that date format looks the same.
Is there something we can change? Thank you

Hi, I've just downloaded your extension and it worked! (Others I've tried have failed :( )

I have very few needs, but I was wondering if there are any plans to use Google Finance as a data source?

Reason I ask, is that I believe that Yahoo has in the past turned off their API which left me in the dark for quite a while.

I don't know about FT, but thought Google might be a good reliable data source.

I don't know how your extension works, but I suspect you're scraping the data from a page rather than using an API? Is that reliable?

Keep up the good work!

After successfully installing the extension some time ago, I wrote a spreadsheet to track investments in multiple accounts and visualize in charts. This extension works as designed-- in terms of pulling data from Yahoo. The issue is performance: I have 50 or so ticker symbols and 5 or 6 data points for each. I have a worksheet ("Prices") to populate all data pulled from Yahoo. Other worksheets (one per account) look up data that they need from this one Prices worksheet. Since multiple accounts may own same stock, I thought it best to make the call to Yahoo only once and then all accounts use this. Everything works as designed.
The issue is that anytime I do any edit in any spreadsheet tab-- in tabs for individual account tabs, not in the "Prices" tab (for example, change annotation in Pie charts), the spreadsheet updates the whole "Prices" worksheet. It takes more than 8 or 10 minutes! I sit and watch in log file individual calls to Yahoo go by in real time! For 50 symbols x 6 datapoint each=300 calls to Yahoo!

Perhaps a solution is to provide for specifying multiple datapoints for one ticker in one call and let the extension populate adjacent columns with all datapoints? Something like GETREALTIME(ticker, 104, 5, 6....., "YAHOO")?

Whenever I wan't to include a row or a value in calc it takes ages, till I can enter the value (document is loading in the background, obvisiously from at this moment not necessary background calculations of your plug-in). Can I disable this behaviour somehow?

.

When filling a spreadsheet with your yahoo/ FT functions I would like all the rows to be filled before the Columns. Is that the current method?

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.

I am getting an error :

Yahoo.getRealtime(ZAG.TO, 21) - urlopen: 'charmap' codec can't encode character '\lx84' in position 346446: character maps to

When trying to get the last price for ticker ZAG.TO and also for QQQ. It seems to work for every other ticker codes I tried so far.

Any idea what might be causing this?

Cheers !

Hello - I just found your extension and really appreciate your efforts. I've created a spreadsheet listing numerous stocks - how can I get the getrealtime cells to start auto-updating on a regular basis? Thank you!

Hi,
There is a data regarding the company's industry sector.
Of what I know for Yahoo with python-yahoo lib one can acquire it by using the code alike

import yfinance as yf

sbux = yf.Ticker("SBUX")
tlry = yf.Ticker("TLRY")

print(sbux.info['sector'])
print(tlry.info['sector'])

Could you please add that field in the available data codes? If only for Yahoo it will also be ok.

PS. HUGE THANKS for such a nice and powerful extension. I dono why LO does not have that as MS EXcel has it out of the box... and what for are spreadsheets anyway if not for financial markets.

Probably due to the used space character:
https://markets.ft.com/data/equities/tearsheet/summary?s=NOVO%20B:CPH

Btw: Any news on calculating market cap on the fly?

This site gives detailed information (see financials under any ticker): https://www.marketscreener.com/quote/stock/CISCO-SYSTEMS-INC-4862/?type_recherche=rapide&mots=cisco Where are they getting their data from?

When opening the example sheet, all GETREALTIME & GETHISTORIC formula cells return a 504 error. I have tried restarting libreoffice, and reinstalling the extension. There are no diagnostics on the "Support" sheet. The extension did create the ~/.financials-extension directory, but it is empty.

System specs:

ArchLinux 5.8.1-arch1-1 x86_64 GNU/Linux
LibreOffice 6.4.5.2
Financials-Extension 2.1.0
python 3.8.5
python-dateutil 2.8.1
pytz 2020.1
pyparsing 2.4.7

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

FYI: Installing LibreOffice 7 breaks installing PIP using "get-pip.py" on Windows 10 and therefore the extension will not work.
At least one person has mentioned it there, so everyone be careful.

I have managed to build and install your extension but the example document gives me Err:504 for every cell. I am not sure what to do and how to debug this. Any ideas?

Can you please indicate how I get the US:UK conversion rate in yahoo?
I have found the conversion rate page at https://uk.finance.yahoo.com/quote/GBPUSD%3DX?p=GBPUSD%3DX

Thank you for your work . I am using Windows 10 and Libre Office 7.1.2.2. I have a small problem with the Yield data being collected for ETFs. The Yield information shows on Yahoo Finance HTML but does to make it to the spreadsheet . For example, recently the HTML sheet for SCHD shows a yield of 2.87% but that data does not appear on the Realtime sheet.

I am getting a strange result from =GETREALTIME("BVC:LSE",22,"ft")
It results in 30 being the bid price. which is wrong.

Last price =GETREALTIME("bvc:lse",21,"ft") is 95.6

The problem is that the bid price should be an error code, if it is not available, so I can choose the market price in the absence of a valid bid price. (In working out current profits to date, I prefer to work with bid prices rather than mkt prices. )

The reason why the bid price is wrong is presumably connected with there being no bid price quote for it on the FT site, at the time of the pull.

The asking price #25 is also wrong at 137

I wonder where it is getting these strange prices from on the FT page.

Batm Advanced Communications Ltd
BVC:LSE
Actions
TechnologyTechnology Hardware & Equipment

Price (GBX)95.60Today's Change0.600 / 0.63%Shares traded232.85k1 Year change-28.12%Beta-0.1598

I notice that the FT also shows shares outstanding and Free float. If you have time could you please add them into the extension's options.

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

Originally posted by @ikyquocked in #10 (comment)

I am struggling to get the formula to work: =GETREALTIME("IHC:LSE",39,"ft") it produces #N/A. It fails for most of the shares I am listing.

Closed End Funds (CEFs) are stocks that act like mutual funds but trade like ETFs generally. However, they have one characteristic that is unique to them: a Premium/Discount % (this can be a positive or negative percentage).

This P/D value is determined by the Market Price divided by the Net Asset Value price. Stocks, mutual funds and ETFs all try to recalculate the NAV of their assets to keep the NAV as close to the current price of the asset. But CEFs do things differently. Investors can bid up the value of CEF stock such that you could be paying $1.10 for $1.00 of NAV; or $0.90 for $1.00.

It would be beneficial if there were a code to scrape the NAV value. We could then use Calc to determine the current P/D value. Would this be a possibility?

Do you see a way to add number of outstanding shares?

I moved the spreadsheets using Financial-Extensions (F-E) to a new location.

All my entries in my spreadsheets using the extension now show the function expanded and they no longer work for example:

=IF(G24="cash",0,com.financials.getinfo.python.financialsimpl.getrealtime($G24,39,"ft"))/1000

The extension manager is showing no extensions.

I have tried to reinstall F-E by adding in F-E.oxt to libreoffice calc. I get the error message:

/home/robin/.config/libreoffice/4/user/uno_packages/cache/ -uno_packages/lu1327wv315c.tmp_/Financials-Extensions.qxt does not exist

I thought I had it installed by changing the oxt file quality, but it has disappeared.

I have checked my backups and nothing is being restored for config/libreoffice...

On starting a new spreadsheet I have checked the Basic Macro Organiser. It does not show that the F-E macro has been installed in the library

I can confirm that this extension also works nicely in Arch Linux. I just installed

• python-pyparsing
• python-dateutil
• python-pytzdata
• python-pytz

like so
sudo pacman -S python-pyparsing python-dateutil python-pytzdata python-pytz
and then the OXT file installed without issues.
A simple test without loading the rather large example file is this formula:
=GETREALTIME("IBM:NYQ";"OPEN";"FT")

Versions: LibreOffice 7.1.0.3 10(Build:3); Financials-Extension 2.6.0

The .oxt file works fine in Arch Linux, but the data doesn’t update very often. Is there a setting for update frequency?

I am having problems with 2 shares and one ETF that will not bring down the data into a Libreoffice SS. Can you please look at why this is happening:

1. BATM andvanced Comms: LSE BVC
   My call entered as =GETREALTIME($B12,"prev_close","ft")
   Note: B12=BVC

(error message #N/A)

2. NORTHERN DYNASTY MINERALS LTD COM
   Per Web address: quote on NYSE with code NAK. I checked with NYSE web site.
   My call entered as =GETREALTIME("NYQ:NDM:","prev_close","ft")
   Error message =#N/A

ETF
I have an etf from Invesco under the epic of SSLV. Is there a way of pulling down its price?
For more detail about the ways of describing the ETF see https://etf.invesco.com/gb/private/en/product/invesco-physical-silver-etc/trading-information
The etf is quoted on the LSE in $
Call entered as =GETREALTIME($B26,"prev_close","ft")
B26 = SSLV
Error = #N/A

Also How do I get the exchange rate for dollars to pounds in the SS automatically?

See above. Did your endeavours to look for a reliable provider (life market cap, etc) yield any results?

The extension works well for me for pulling the data offered.

Is there a way to pull historical data to compare with the current data?

For example, I'm wanting to compare stock prices (current) to a specific date in the recent past (14 December 2020, or the closest trading day to it).

At 31/8/21 at 9:12 BST I am not getting any prices on this link. How do you suggest I work around it?

Hello - Your extension got me playing the python scripting and I ultimately made a python script that calls livecoinwatch's api to give me crypto prices. So, I no longer need your extension.
So I uninstalled it and removed all the calls to =GetRealTime()

But strangely your extension still works even though I uninstalled it. I forgot about remove a call to GetRealTime() and it was automatically changed to:
=com.financials.getinfo.python.financialsimpl.getrealtime()

just an fyi in case there is something related to the uninstall process you can/want to fix.
take care and thanks again for your extension!

It seems that EX_DIV_DATE doesn't come in a readable calc date format to run formulas on it. In my case it would be neat to run some conditional formatting on it (I'd like to highlight the upcoming EX_DIV_DATES).

Should be straightforward to change. Any plans?

Couldn't access any price data of this one, although the data seems there: https://markets.ft.com/data/equities/tearsheet/summary?s=DELT:TLV

Do you have a solution to converting a price from dollars into pounds?

When getting data with the yahoo provider I get a #N/A value.

For instance:
=GETREALTIME(AAPL,"last_price","yahoo") --> #N/A

All values I requested so far get the same result.

I recently uninstalled/reinstalled python 3. Now I'm getting an error
this is what I put in a cell:
=GETREALTIME("BTC-USD",21,"YAHOO")

this is what the cell shows:
Yahoo.getRealtime(BTC-USD,21) - urlopen: argument must be an int, or have a fileno() method.

But I dont get this error when I open this spreadsheet on my linux box so its gotta be something messed on in python?

How do I enter a LSE quoted co into the pull formula yahoo 90. (It did not give the correct price)

Am I correct in assuming that Yahoo does not have a history of UK LSE quoted companies?

Could you add FCF?

I use FCF ttm for my Discounted Cash Flow (DCF) spreadsheets and I would find this data very useful. It can be reached from most webs like morningstar or yahoo.

I am new to github and a non-programmer for all practical purposes. Landed here trying to read an investment portfolio tracking spreadsheet (.xls) into LibreOffice Calc because I don't want to buy MS Office any more and Google Sheet is not private.

Under the Usage heading it says: "Under 'Releases' on GitHub there is downloadable Financials-Extension.oxt file - load it into Calc under menu item: Tools, Extension Manager..."

I can't find this extension file, or even 'Releases' on GitHub! Could someone please post a version for Windows 10 and post a download link here? Thank you.

(my programming background is programming complicated math in FORTRAN. I know I am an old foagie...)

I have had to stop using FT prices. Some of the shares are showing unusable results. For some shares the results are unreliable and do not match that shown at the London Stock exchange or LSE. I also prefer to work on bid prices rather than last price, as I am unable to check if the last price was a sale or a purchase.

As an example Centamin shows:

Ask 107, Bid 90, Last price 99. The actual spread is 2.1%. I could not work that out from the FT listing.

I now realise that Yahoo prices are more reliable and up to date.

To have them work, your function has to be completed as in this example:
GETREALTIME("ASTO.L",25,"YAHOO")
I suspect that the actual yahoo code has to be checked if it does not work. In non London Stock Exchange quotes the extension will be different.

Note the .L
In your code example on site you leave that off.

I just discoverd that =GETREALTIME(Ticker;"MARKET_CAP";Source) for TOM:FRA get's a faulty result. This might have to do with currency conversion. You can compare it with TM:NYQ (Japan).

As I have not found the above in Yahoo I use FT as the price source:

Today the extension is not getting the price from FT, instead of the price it reports:

Datacode.timestamp.999

These are the calc entries that were working yesterday:
=GETREALTIME(C$10,"21","ft")

C10 is GB00B5ZNJ896:GBP

Are you able to replicate the problem?

When installing the extension v1.0.4, I got the following error:

(com.sun.star.uno.RuntimeException) { { Message = "<class 'ImportError'>: No module named 'dateutil', traceback follows\X000a File "C:\Program Files\LibreOffice\program\pythonloader.py", line 149, in writeRegistryInfo\X000a mod = self.getModuleFromUrl( locationUrl )\X000a File "C:\Program Files\LibreOffice\program\pythonloader.py", line 104, in getModuleFromUrl\X000a exec(codeobject, mod.dict)\X000a File "C:\Users\mortee\AppData\Roaming\LibreOffice\4\user\uno_packages\cache\uno_packages\lu5516wl77np.tmp_\Financials-Extension-1.0.4.oxt\financials.py", line 11, in \X000a import dateutil.parser\X000a File "C:\Program Files\LibreOffice\program\uno.py", line 356, in _uno_import\X000a return _builtin_import(name, *optargs, **kwargs)\X000a\X000a", Context = (com.sun.star.uno.XInterface) @0 } }

Windows 10 64 bit
LibreOffice 6.3.4.2 (x64)
Python 3.6.4

I tried to put this into the cell to get the price of the stock on the specified day:

=GETHISTORIC("AAPL", "LAST_PRICE", "2020-10-13", "YAHOO")

And this returned the result:

Data doesn't exist - 21

But on https://finance.yahoo.com/quote/AAPL there is some data for the stock price on the specified day.

GETREALTIME function, however, works.