Finki

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.

1 Like

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ā€¦

2 Likes

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!

1 Like

@finki this is a great project.

:cry: Google sheets throws #ERROR! if I try any of these, so I think I must be doing something wrong.

1 Like

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

1 Like

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ā€¦

https://finki.io/callAPI.php?isin=GB00BH4HKS39&function=ukDividendHistory&startDate=2009-01-01&endDate=2019-12-31

Or see the Welcome to FinKi function table

(Btw URL suggests itā€™s yy-dd-mm?)

Good spot! Amended.
:+1:

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.

1 Like

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.

1 Like

Cheers, Iā€™ll have another play with it tonight!

1 Like

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!

1 Like

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 :+1:
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.

1 Like