A while ago I posted about How to get your ETH transactions in excel. The process described in that post can get you only the ETH transaction history.
As we know Ethereum has an abundance of tokens. Here we will take a look how to get your Ethereum tokens transactions history.
As in the previous post we will use Etherescan for this. Etherescan is a blockchain explorer for Ethereum, but they continuously update their web, and it serves as an analytics platform for the ETH blockchain.
To get our tokens transactions history we will use one of the Etherescan “API”.
To get data from https://etherscan.io you will need to create an account on their web and then get your API key. I have covered this process in the previous post in details. You can check there for the whole setup.
Once you got your etherscan account and your API keys you are ready to get your tokens transaction history.
For the data we need in our case, go to the accounts section https://etherscan.io/apis#accounts.
Scroll down a bit and find the “Get a list of "ERC20 - Token Transfer Events" by Address” API.
You need to edit the API above and enter yours:
- 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 …. , before the &startblock.
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:
You will get a sheet with a lot of columns but essentially you will need only the following
As mentioned, the timestamp needs to be converted using the formula above.
The other thing that needs some more work is the value column. You will need to divide the value in this column with the 10 to the power of the tokenDecimal.
For example, if the token has 18 decimals then you divide this with 10^18. If it has 9 decimal then its 10^9, etc.
When all is done you will get something like this.
From here you can filter out tokens (example WLEO 😊), dates, wallets and get what you need.
All the best
Posted with STEMGeeks