How to Import Crypto APIs into Excel

One of the aspects that have evolved with the blockchain but especially with cryptocurrencies is the possibility of using these assets to trade or buy and sell them to derive the maximum possible profit from them.

Obviously, tools have been developed that provide data, information, indicators, and anything else that simplify the average user from having to create the graph himself and take the various data, which in this sector are essentially the reference period and the price, which to get a better data, use the OHLC (Open, High, Low, Close), prices of the asset that reaches during the day, i.e. the opening price, the maximum, the minimum and the closing price.

This convenience is paid both in economic terms and in experience because if it is very simple to use a ready-to-use platform that costs money, on the other hand, we will not actually know how everything works, especially if we find ourselves in difficulty if this, for one reason or another, it went out of order.

And this is why it is better to start from the basics and also know the "traditional" systems in order to import the price data offline so as to build our formulas, indicators, and strategies in a completely autonomous and independent way.

In this case, we will explain how to import OHLC data starting from public APIs (the APIs are not always public and free) related to crypto, and we will insert it in a spreadsheet so with the data in hand, we can build the graphs both of the price than with other formulas that we would like to use.

Checklist

Before proceeding with the guide, we will need some basic things:

• API related to crypto / token with the OHLC value
• A spreadsheet
• A predetermined asset that interests us

In our case, we will use CoinGecko's public and free API, as a spreadsheet we will use Excel, and finally, as a crypto asset, we will use EOS and precisely the EOS / USD pair.

How to get OHLC data

First, we must go to the CoinGecko API page and once connected we must find the item "Get / coins / {id} / ohlc" and press the "Try it out" button:

At this point, all we have to do is enter the ID of the token we want to use, in our case EOS, the reference fiat currency, in our case USD, and finally, the days relating to the time frame we want to control, in our case 180 and press the "Execute" button:

We now have both the data on the site and an address that we will need to import it into our spreadsheet, which in our case is the following:

https://api.coingecko.com/api/v3/coins/eos/ohlc?vs_currency=usd&days=180

How to import data into the spreadsheet

Now let's open a new document and a new spreadsheet and then from the "Data" menu we have to click the third button that says "From Web":

Once pressed, on the next screen we must paste the address that CoinGecko previously provided us and press "OK":

Now comes the most problematic part which is to arrange the data in a readable format and to do this we must first of all press the first button with the words "With the table":

The program will arrange everything in a single column and we must proceed to divide the various data and to do so we must press the double arrow next to the "Column1" item:

Now we choose the second option which is called "Extract values ​​...":

In the next screen, we have to choose a value and for convenience, we choose the semicolon symbol ";" and we give the OK:

As we can see now we have all the information divided only by a simple semicolon and the same must be separated into different columns:

To extract and sort the various values, we need to click on the "Divide column" button and choose the "By delimiter" option:

In the next screen we choose the semicolon ";" and press OK:

And now all the columns are arranged and almost ready to be used as now we only have to fix the date column which is in UNIX format:

To do this, we need to add a new column via the menu of the same name and click on the "Custom Column" button:

In the next screen we need to set up a formula which is the following:

#datetime(1970,1,1,0,0,0)+#duration(0,0,0,[Column1.1]/1000)

Once we have given the OK, we will see the new column with the date updated correctly:

Now we just have to delete the old starting column, put the date column at the beginning and transform it into a date value from the "Transform" menu and under "Data type:" select "Date":

And only now can we go to the "Home" menu and click on the "Close and load" button:

And here we finally have our data imported into our spreadsheet and ready to be analyzed and inserted in various graphs such as that of the price and to have other information that can be calculated as you want and go to import them on the graph itself or make different graphs for each formula and indicator we want to use:

Finally, we have seen how it is not easy to import something dynamic, such as the price of a crypto asset, into a static platform, but once all the steps have been done, anyone is then able to import other data and exploit them as they see fit and have formulas, data and indicators at your fingertips and without limits, since not all platforms have the same indicators or it is possible to put several on the same chart, and by doing everything from us we have not only learned something new and interesting but also useful and above all, it saves us money since with the data in our possession we can exploit it as often as we want.

For the Italian readers, I made a video with all the steps: