How To Get Your Ethereum TOKENS Transaction History In Excel?
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”.
Preconditions
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.
https://etherscan.io/login?cmd=last
Once you got your etherscan account and your API keys you are ready to get your tokens transaction history.
Next steps
For the data we need in our case, go to the accounts section https://etherscan.io/apis#accounts.
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:
(B2/86400)+DATE(1970,1,1)
You will get a sheet with a lot of columns but essentially you will need only the following
- timeStamp
- from
- to
- value
- tokenName
- tokenSymbol
- tokenDecimal
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
@dalz
Posted with STEMGeeks
https://twitter.com/Dalz19631657/status/1338988255325327368
Or you can just copy and paste :)
Copy paste from the api?
from here: https://etherscan.io/address/0x0000000000000000000000000000000000000000
(or your own), or you can download the csv. I think this is fine for most uses like just tracking.
Your way is great for building something complex or perhaps running a business, especially filtering certain erc20 or 721 tokens.
Yes, you can enter you address in the explorer but the data will not be presented as in the case above ... especially for the eth tokens ... in most cases it just shows 0 ETH ....
The little drop-down menu is annoying especially for altcoin enthusiasts, but there is a little box beside it that takes you to this:
https://etherscan.io/tokenholdings?a=0x0000000000000000000000000000000000000000
and at the top it says:
VALUE IN ETH
917,114,326,859.928539
VALUE IN USD
$536,401,827,493,835.00
I read this post and understand what to do now reading your other post
Not sure what you mean...
Sorry misspelled I'm going to read your api post
great. thanks for sharing.
Posted Using LeoFinance Beta
You are welcome!
Posted Using LeoFinance Beta
Great use of Power query, nice work
Posted Using LeoFinance Beta
Hey thanks!
I'm trying to diversify from Hive data :)