CodeX
Published in

CodeX

Extracting Financial Data using Google Sheets Add-In

A no-code solution to get historical, intraday, fundamental, and options data

Image by Tima Miroshnichenko on Pexels

Introduction

One of the prominent difficulties that still majorly exists in using financial APIs is the programming barriers for people who are just getting their feet dirty trying out the APIs. Experienced developers or programmers can easily accustom themselves to the changing environment of financial APIs and has the ability to use the APIs with ease but it may not be possible for beginners.

However, the present scenario is favoring amateurs as the financial API market is getting more innovative day by day. No code solutions are popping up to an exponential level to fill the gap between experienced and beginner programmers.

Today, we are going to explore a no-code feature that helps newbies to get their desired financial information and everything done inside Google Sheets. Without further ado, let’s dive into the article!

EODHD’s Google Sheets Add-in

EOD Historical Data APIs (EODHD APIs) is a leading financial data provider offering tons and tons of APIs with various usages. One of their newest innovations includes a no-code solution to get market data inside Google Sheets itself.

There are some no-code platforms that allow their users to extract data without coding but everyone uses Google Sheets and being able to get any type of market data inside the environment itself is a huge sigh of relief.

In this article, we will take a look at the working of this specific add-in by extracting different types of data such as historical, intraday, fundamental, and options data.

Note: This article is intensely based on the services provided by EOD Historical Data. So to follow up with the upcoming content without any distress, make sure that you have an account for yourself as it enables you to access your private API key (a vital part of this article). You can create an account using the link here. Also, the feature mentioned in this article is not offered for free but a subscription is needed. View the pricing of all subscriptions here and choose what best fits you.

Extracting Data

This section of the article can be classified into two parts: installing the add-in and registering the API key, and using the add-in to get historical, intraday, fundamental, ETF, and bulk fundamental data.

Installation and Registration

Starting off with the installation, there are multiple ways of doing it. The one I prefer to follow is installing the add-in from the Google Workspace Marketplace. First, head on to the Google Workspace Marketplace website, search for EOD Financial Add-In and download the one which is highlighted in the following image:

Image by Author

You can follow this link to directly install the app on the Google marketplace. Now that we have successfully installed the add-in, it’s time to register the API key in order to use it. Firstly, head over to Google Sheets and create a new sheet (name it as you like). Then, click the EODHD APIs’ logo on the right bottom of the page which reveals itself only when the add-in is installed.

Images by Author

In the above image panel, the one on the left is the interface that pops up post clicking the EODHD APIs’ icon and the image on the right is the actual add-in interface that emerges after registering the API key.

In order to register the API key, select the text input box with the label ‘Insert your API Key here’ as seen in the above image (on the left), then type in your secret API key provided to you after creating an EODHD APIs account. After mentioning your API key, click the “Get Data” button to proceed further. If the specified API key is invalid, an error message is shown to bring the issue to your knowledge.

If you don’t have an account, you can type in “demo” in the input box to test the features but only with a limited range of tickers at your disposal (available tickers: AAPL.US, TSLA.US, VTI.US, AMZN.US, BTC-USD, EUR-USD). So it is highly recommended to create a free account (limited to 20 API calls per day) or upgrade the package according to your needs to get the full experience of the add-in.

With this, the installation and registration procedures come to an end. Now that we’re all set, it’s time to extract some data!

Getting Data using the Add-in

There are six types of data that can be extracted using the add-in: historical, intraday, fundamental, ETF, bulk fundamental, and options data. All types of data can be extracted easily and require pretty much the same steps to be followed for each and every one of them. For a better understanding of the working of the add-in, let’s use it to extract the end-of-day historical data of Apple.

The image on the right side shows the parameters of the add-in and the one on the left side is the resulting output of the respective input. The parameters are not that complicated and include only the basic ones starting from Ticker (the symbol of the stock), Period (the time interval between the data points), and From and To (the starting and ending date of the dataframe).

Finally, you can check the “Chart” tickbox to make a nice candlestick plot out of your data. This function is available on both the end-of-day historical data and the intraday historical data features. You can expect the chart to be something like this:

Image by Author

After filling out the necessary information, the desired result will be opened in a new sheet by clicking the ‘Get’ button.

This same procedure is applicable to all the other types of data but small changes to the parameters are possible. For example, the fundamental data would only have the Ticker parameter whereas the options data would require additional information such as the Trade Date From and Trade Date To parameter. Examples of intraday, fundamental, ETF, and bulk fundamental data are given below with the output:

1. Intraday Historical Data:

Images by Author

2. Fundamental Data:

Images by Author

3. ETF Data:

Images by Author

4. Bulk Fundamental Data:

Images by Author

Apart from using the parameters interface, one can also make use of the native Google Sheets function IMPORTDATA to retrieve data from an EOD Historical Data API endpoint. For example, in order to get the last traded price of MCD, all you have to do is just type the following command in any one of the spreadsheet cells:

=IMPORTDATA("https://eodhistoricaldata.com/api/eod/MCD.US?api_token={YOUR_API_KEY}&fmt=json&filter=last_close")

The above command will reveal the last traded price of MCD but make sure that you have replaced {YOUR_API_KEY} with your private EODHD API key.

Closing Notes

Apart from the features that are mentioned in this article, there are also other enticing ones. Firstly, the integration of EODHD APIs with Apipheny which allows users to automate the process of data pulling from API endpoints inside of Google Sheets itself. Then comes the integration with Cryptosheets which almost provides the same service as Apipheny but with a subscription.

Overall, this tool is fantastic for beginners who are struggling to code and at the same time want to save some time on programming. Financial APIs that are once reserved for exclusive institutions and hedge funds becoming more accessible to any interested people is remarkable and it is only possible through innovative projects like this.

With that being said, you’ve reached the end of the article. Hope you learned something new and useful. Also, if you want to see the original documentation for more information, view it using this link: https://eodhistoricaldata.com/financial-apis/google-sheets-financial-add-in-for-eod-fundamentals-data/

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Nikhil Adithyan

Nikhil Adithyan

2.3K Followers

Founder @CodeX (medium.com/codex), a medium publication connected with code and technology | Top Writer | Connect with me on LinkedIn: https://bit.ly/3yNuwCJ