Hey @finki - looking forward to making the most of this. Unfortunately Iāll need to wait for your template as my Google Doc skills are limited.
Morning,
Because the finki API return is (mostly) a single line of text with no parsing required you can use the IMPORTDATA function n Googlesheets. So they syntax to get started is super simple.
example:
=importdata(āhttps://finki.io/callAPI.php?isin=GB00BH4HKS39&function=ukAskā)
or
=importdata(āhttps://finki.io/callAPI.php?isin=GB00BH4HKS39&function=ukDividendYieldā)
Then make the ISIN and function call variable and point your funtion towards the new variables.
example
=importdata(āhttps://finki.io/callAPI.php?isin=ā & [YOUR_CELL, eg A1" &function=" & [Your finki function] & ā)ā
Looks daunting. Really isnāt. Have a play. Iām running behind on making a GSheet and Excel template - sorry.
Check the API function call list for what you can return and howā¦
and for those that requested āDividend Coverāā¦ itās doneā¦ itās just not on the API call list yet.
Itās coming.
Patience.
And thanks for being so engaged. Only 50 more requests to run through! Thanks!
Really helpful finki! Great project here, thank you!
@finki this is a great project.
Google sheets throws #ERROR! if I try any of these, so I think I must be doing something wrong.
Quick demo here
It was the different quote āā marks in this forumā¦ they donāt work when copied to gSheetsā¦ you learn something new everydayā¦
Demo link above
doh should have thought to check that. Thanks.
Continuing the discussion from Finki:
ukDividendHistory added for ITs and equities (not ETFs yet, sorry)
Callable via an ISIN,the āukDividendHistoryā function, a startDate and endDate in YYYY-MM-DD format. So, if you wanted 10 years of Vodafone dividendsā¦
Or see the Welcome to FinKi function table
(Btw URL suggests itās yy-dd-mm?)
Good spot! Amended.
Please could we have a function to see what currency the stock is traded in? I know that thereās ukCurrency, but it would be good if it could be used to identify either GBP or USD. Hereās what happens if I look up Boeing:
https://finki.io/callAPI.php?isin=US0970231058&function=ukCurrency
At the moment Iāve got a link to the FreeTrade Sheet to get me the currency, but it would be good if I was just reliant on 1 data source (i.e Finki!).
Also, has anyone discovered a clever way of displaying the price in 1 cell regardless of if it is a UK or US listed stock? Iāve managed to do it, but Google Sheets canāt display the different currency formats (annoyingly the conditional formatting doesnāt seem to be as advanced as Excel).
This is my Google Sheets Formula if people are wondering:
=IF($F3="GBP",IMPORTDATA("https://www.finki.io/callAPI.php?isin="&$D3&"&function=ukBid")/100,IMPORTDATA("https://www.finki.io/callAPI.php?isin="&$D3&"&function=usLatest"))
So it first looks at F3, which is a cell I have my lookup to see if the stock is listed in GBP / USD (eventually Iāll need to add EUR etc too). Then $D3 is the cell where I have my lookup to the ISIN for that particular stock, which then calls the respective API function.
Good spot. Thanks.
Quick fix put in place.
for Boeing, ukCurrency will return āNo Dataā and a new functionName of āusCurrencyā will correctly return USD.
https://finki.io/callAPI.php?isin=US0970231058&function=usCurrency
I take your point about the distinction between āukā and āusā function calls. This has been pointed out by many people. Itās something I completely understand. It was always intended to be a short time problem that I would simplifyā¦ I just havenāt!.. Yet! But I will.
Cheers, Iāll have another play with it tonight!
Hey Finki, is there any chance of getting a batch of stock prices as a json?
So I could make a single call and get a list of all ISINās and Ask prices (or most recent close prices) in the market. Would this require to much resources on your end?
Completely do-able. But you are correct - there is a little more work and load. For example, I think (donāt hold me to this) the IEX API restricts batch price queries to 100. Since there are approx 350 securities in the FT universe Iād string 4 calls together. If I need to the calls to keep track of the real-time FT universe then I also need an initial call to query and establish the latest universe of ISINs. Completely 100% doable - not an issue. Just havenāt done it as didnāt think there was demand for it. Leave it with me.
Thanks Finki! I admit my request is probably relatively niche as Iām maintaining my own stocks database. Most of my data is input manually and Iāve been calling your api to fill in the gaps. Iām conscious of hammering the server though as I donāt want to create a āTragedy of the Commonsā situation.
Iām loving your work though, it has really encouraged me to broaden my skills! I didnāt know how to call an api or handle JSON in sql before your post. So thanks!
The ācall all FT pricesā API is nearly done - should be able to tweak it and have it out in the coming days.
Iām likely to trigger the data grabbing behind the scenes by cron. Currently thinking Iāll grab prices every 30 minutes? Is that too infrequent for you? Format is isin, bid, offer, currency. It outputs all 350 (ish) stocks on the FT platform via one call. Prices are therefore correct +/- 30 mins ā¦ so handy but not useful enough for any live trading decisions!
Itās done the way it is so the server load is reduced - so you can call it as much as you like - what youāre actually grabbing is the (now) static file Iāve stored from the last price grab cron job.
Either way. Should be with you soon.
Will also power a portfolio update/monitor page someone asked me to buildā¦so itās win/win.
Thanks @finki
Thats way better than I even requested. I tend to make my decisions after hours based on close prices. So again, this is beyond my personal needs. Canāt wait to try it out.
Yeahā¦ it included ācloseā too. Forgot to mention that. That way youāll have a choice between (semi) live during working day or ācloseā if out if hours. But the choice is yours.