Finki API

What is Finki API

Finki API is an API that gives us the ability to pull stock data into our spreadsheets. By using Finki API to do this we can automatically pull essential information on companies. With the help of Google Sheets of course.

Some examples of what information can be pulled using the API are as follows.

• Dividend Payout Date
• Dividend Ex-Date
• UK Dividend Amount
• US Dividend Amount

Finki API is being worked on all the time with new functions being added. You can see it in action on my Google Sheets Spreadsheet on Youtube. The spreadsheet is from 3:52. I shall do a more detailed video in time as the API develops.

Check out my guide if you are new Trading 212 For Beginners will help you open an account and get started today, adding Finki API to your sheet you will be well on your way to wealth.

Dividend Tracking Spreadsheet

For now, check how my dividend tracking spreadsheet looks and works, using Finki API, finviz and google finance to pull in the data I need. Subscribe and drop me a like if you find this useful.

You can see behind the scenes how finki works in my dividend tracking spreadsheet.

Want a nice way to track your investment and dividends? You can now get a copy of my Dividend Tracking Spreadsheet

I will show you a screenshot then provide some formulas so you can set up your own. Currently, I am using Finki API to pull dividend ex-dates and dividend pay dates only as these are the tricky ones to pull from normal websites often not working correctly.

My portfolio is held with Trading 212. My thought on Trading 212 dividend investing has been outlined in a previous post.

How to Use Finki API

At first, it looks a little complicated. Fear not, it’s pretty straight forward once you understand the basics.

As you can see from the image above the Finki API uses ISIN rather than ticker symbols. Don’t worry, all we have to do is convert them its takes a few seconds per holding.

All we have to do is visit https://finki.io/isinAPI.php?ticker=LSE:RIO

Notice how the end of the URL has LSE (London Stock Exchange) followed by a colon : RIO

RIO is the Ticker symbol for Rio Tinto. GB0007188757 is the ISIN it returns.

For US stocks just change the end of the URL to NYSE:O for example for Realty Income. NYSE being New York Stock Exchange.

So, what you need to do is just change the ticker at the end for all you positions and copy and paste the ISIN numbers into a new sheet

If you see at the bottom I have made a new sheet called API – Just click the plus in the lower left and right-click to rename. Once you are done with this sheet, later on, you can right-click and click hide to tidy it up.

Once you have pasted all the newly obtained ISIN next to your ticker. I highly recommend you do have the tickers on this page as well. If not you won’t know what is what just from the numbers.

At least with Ticker Symbols they mostly make sense and are easy to remember.

Next up you will need to obtain an API key from Finki API. It is free all you need to do is email [email protected] and ask for an API key.

Finki API Key

Now that you have your API key you are nearly there. You must click in the colomn that is headed “CallAPI” and click the first URL.

The URL will look like this.

=”https://www.finki.io/callAPI.php?isin=”&B85&”&function=”&D85&”&key=ENTERYOURKEY”

Simply replace ENTERYOURKEY with your newly acquired API key ensuring your quotation marks are present.

From here you can then drag the formula down using the little square in the corner. Doing this means we only have to edit the URL with our key once.

The same can then be done for any other information you wish to pull. All you need to do is change the function to one of the examples listed.

New Functions Update

Update – As of 04/05/20 The we can now drop the UK or US prefix in the functions as shown above. All we need to do now is :

“dividendExDate”, “dividendPayDate”, “dividendCurrency”, “dividendAmount”, “dividendFrequency” 

As some examples. Notice how the UK or US prefix has gone but the dividend now starts with a lower case. Note the functions are case sensative.

Having a prefix was removed as it is not needed when the ISIN are unique.

As stated I am currently just pulling Payout Dates and Ex-Dividend Dates via Finki. Since the update today I have also added dividendAmount for my UK stocks. This information is a little tricky to get hold. A lot of UK stocks will pay different dividend amounts each time. US are the same every payout.

All it mean it estimating UK annual payout is a bit of luck. Normally, I will just go off last years total.

For the dividend amount and Forward P/E as seen in my spreadsheet on Youtube, I am using a different method as described below.

How to dynamically add the Dividend amount.

As the dividend amount is more stable and will hopefully only change once a year all being well (an increase!). I have opted to pull the data from a website instead.

I will be using finviz to obtain the data. As an example I shall use Realty Income ticker O.

Firstly, here is the formula you need to paste into your cell to return the Annual dividend.

=ABS(REGEXEXTRACT(INDEX(IMPORTHTML(“http://finviz.com/quote.ashx?t=”&$B11, “table”, 11), 7, 2), “/*[0-9.]+/*”))

Whilst at first it looks like gibberish. It will soon become clear.

Firstly, notice how in the formula you have a $B11 this is referencing the cell on my sheet where the ticker symbol is ( O ) in this case. You will need to change this to suit your sheet.

If you visit the finviz site and search for Realty Income it will give you a nice table of stats.

Next up, the clever part. It is pulling data from the table, then row 7 column 2. If you reference the finviz site you will see that the Dividend Amount is listed under row 7 column 2. Just look at the formula where the 7, 2 is.

How to dynamically add P/E Ratio

Secondly, if you wish to pull some other stat out of the table. I shall give forward P/E as an example as thats whats in my spreadsheet.

Again, go to the finviz site and search for Realty Income and look for Forward P/E. It is located in row 2, column 4.

Can you see what you need to do?

=ABS(REGEXEXTRACT(INDEX(IMPORTHTML(“http://finviz.com/quote.ashx?t=”&$B11, “table”, 11), 2, 4), “/*[0-9.]+/*”))

Here is the formula for forward P/E. The only difference is where it’s 7, 2 for the dividend payout it has become 2, 4.

Likewise, this formula can be dragged down across all your positions so you only need to do it once. Just ensure you change the cell refrence to work with your sheet.

Have a play around and pull a different stat from what I have shown to get your head around it. It’s not as intimidating as it first appears.

How to get the Stock Price

Next up, how to get the stock price. This is a nice easy one and one that google sheets can provide for us via a built-in function.

All we have to do is paste this formula into the cell

=GOOGLEFINANCE(B11,”price”)

Again, where B11 is referencing the cell with the ticker Symbol in it. In this case for simplicity’s sake Realty Income again ticker O.

From this, we can then add more columns for the number of shares and total equity. All we need to do is multiply the share price by the amount of shares we have to get the total value.

If you wish to work out the dividend yield. Simply divide the annual dividend payout you just pulled from finviz above by the share price. It then needs to expressed as a percentage by going to Format – Number – Percent in Google Sheets.

A combination of Finki API, Finviz and native Google Sheets function can lead to a very slick spread sheet capable of tracking all your dividends and investments. My own sheet is constantly under construction as I strive to improve it all the time.

For those of you who have never used Google Sheets at all before then, I suggest you watch my friends video at GenExDividendInvestor.

He outlines the basics and fundamentals as well as touches on some more advanced things like the sparkline.

=SPARKLINE(GOOGLEFINANCE($B10,”price”,TODAY()-365,TODAY(),”weekly”),{“charttype”,”line”;”linewidth”,3;”color”, if(googlefinance(B10,”price”) <index(googlefinance(B10, “price”, today()-365),2,2),”red”,”green”)})

The sparkline also references cell B10 Realty Income O. So if you copy formal then change all 3 B10 cell references to match your sheet.

Hopefully with this Finki API guide, I have inspired you to build your own dividend tracking spreadsheet.

Check out my guide if you are new Trading 212 For Beginners will help you open an account and get started today.

Trading 212 Free Share

Importantly, if you have not started your journey yet but are reading this article, you are in the right place.

Sign up with the link below to get started now.

Do you want to get a free stock share worth up to £100? You only need to deposit £1 to get the free share!

Create a Trading 212  account using this link

We both get a free share!

None of the above should be used as financial advice, I am not a professional. Always do your own research.

Thanks,