I just created a spreadsheet in Calc to keep track of the stocks I’ve purchased. I’d like to create a column that allows me to monitor a stock’s price in real time. Is it possible to link to a live stock quote web site? I’m pretty sure there’s a way to do it but I can’t figure it out. I found a video using Google and the person said to use the Insert > Link to External Data function but it’s not there. Perhaps it was an older version of Calc in the video from what I have(?). Could someone please explain the procedure (if it’s possible) and also what web site link would I enter to pull a stock’s live quote price? For example, what link would give me a live stock quote for Amazon? TIA
I think you want insert-hyperlink, but you’ll need to be sure that the external data is in, or can be manipulated into a form you want.
You’ll find Link to External Data under the Sheet menu, not Insert (that was some versions ago). See also the help.
I have found a way. First, create an account on https://iexcloud.io. There are various levels of membership, one of which is free which allows 50,000 API calls per month. If you want a lot of data you can pay for an individual membership which gives you 1,000,000 API calls per month for $9 per month. Once you have an account they give you an API token which is needed to use the API. Use the secret token, not the publishable one. In LibreOffice, you then put this into the cell (for Amazon) to get the latest price (substituting your API token).
=NUMBERVALUE(WEBSERVICE("https://cloud.iexapis.com/stable/stock/amzn/quote/latestPrice?token=YOUR_TOKEN_HERE"),".")
More complete documentation for the API can be found at:
Note: this does not give you continuous live quotes but it will refresh when you recalculate the spreadsheet.
I used this information, so thanks, but it wasn’t quite right.
- I went and signed up at IEX then confirmed email, then logged back in and had to select free basic starter plan before I could get the token.
- In Calc: “Sheets | Link To External Data | URL of External Data Source” I entered: https://cloud.iexapis.com/stable/stock/aapl/quote/latestPrice?token=<YOUR_TOKEN_HERE>
- When I entered the URL exactly and without quotes, a modal window “Text Import” opened asking me how I wanted to interpret the data coming from IEX. The only value shown was the price, so I selected [OK]
- The “External Data” modal window now showed in the “Available Tables/Ranges” field, “CVS_all”, again I selected [OK] (I didn’t change the auto-update interval from 0)
- Now when I refresh the sheet I get the current stock price (up to 15 minutes old)
I was able to get it to work with a googlesheet using the GOOGLEFINANCE function. Then to get it in calc, I chose my sheet to . I then copied that web link in the following menu path: insert>link to external data.
And it worked!
I’m the creator of an LO Calc extension that provides stock quotes using web scraping by defining a new extension function in Calc. All the details are here: https://github.com/cmallwitz/Financials-Extension
This may help some but maybe not all users. If you spot something that isn’t working as expected you can raise an issue there too.
3 Likes
Hi Chris,
Just to let you know this works well for Australian ASX share!
I’ve been using Yahoo as the source and adding .ax to the regular ASX ticker eg =GETREALTIME(“vhy.ax”,21,“yahoo”).
I can also use it to track crypto in AUD =GETREALTIME(“aave-aud”,21,“yahoo”)
Great resource, thanks heaps!
Gordon
Ah yes, just what I needed. Just to refine that in case anyone is Googling,
=GETREALTIME(CONCAT(A1,".ax"),21,“yahoo”)
where A1 being where your stock ticker is.