Getting data from the Ethereum blockchain with Excel? Is this possible?
In a way it is. I would just not call it a direct method 😊.
One the most popular Ethereum blockchain explorer is Etherscan. Actually, they keep adding more and more functionalities to the web and are probably acting more than a simple block explorer and more of an analytics platform for the Ethereum blockchain.
As all things data what we need is an “API”. An url where to connect and get data.
Etherscan provides this. Although not everything is free to use, but there is enough free stuff to play around with. Etherscan is a sort of middle man between users and the blockchain for data.
Here we will be looking at step by step how to get your transactions history from the Etherscan API using excel. It’s a basic and simple way to get your data with a common tool like excel.
First go to the APIs section on Etherescan.
The next thing you need to do is create your API key. This is needed for all the APIs calls you are going to make.
Click on the text “ClientPortal->MyApiKey”.
https://etherscan.io/login?cmd=last
Go to the sign up option and create your username and password. A conformation mail will be sent to you and your Etherescan account is created. Then log in.
Next click the Add button on the top to create your keys.
A pop up will come up with an AppName, that is optional, you may put something in or not. Click continue.
Your API key token is now created and available to use.
Now we are ready to get some data from Etherscan and the Ethereum blockchain.
For data we need in our case, go to the accounts section https://etherscan.io/apis#accounts.
https://etherscan.io/apis#accounts
From here we will be using the “Get a list of 'Normal' Transactions By Address” API.
You need to edit the API above and enter your:
- API key token
- Wallet address
The API key token goes right at the end of the url, after the = sign, and the wallet address need to be replaced starting from address=0x ….
When I updated my url it looks like this:
Note: Don’t try to use the url above, I will change my API key. Its simple and easy to create yours as described above.
Now that we have the url, go in excel in the Data section-> From Web, as show bellow.
The pop up window will come out, paste your url and click ok.
In the next dialog box from the Power Query Editor, in the Convert section click Into Table.
A List option will come up, click that one.
One more transformation To Table.
Expand the Record column clicking on the arrows.
Finally, on the Home tab, Close & Load.
Now you have all your transactions in excel table. You can play around and sort what you need.
Tip on the timestamp format. It is Unix based, you can convert it to standard date format using the formula:
(B2/86400)+DATE(1970,1,1)
Some of the more important columns will be the “from” and “to” column and the “value” column. The value column needs to be divided with 10 to the power of 18 to get the real eth value. You will have fields with empty values as well. Those are operation that are not transferring Eth.
All the best
@dalz