How To Get Your Ethereum TOKENS Transaction History In Excel?

in STEMGeeks3 months ago

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.

01.png

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.

02.png

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.

03.png

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.

04.png

The pop up window will come out, paste your url and click ok.

05.png

In the next dialog box from the Power Query Editor, in the Convert section click Into Table.

06.png

A List option will come up, click that one.

07.png

One more transformation To Table.

08.png

Expand the Record column clicking on the arrows.

09.png

Finally, on the Home tab, Close & Load.

10.png

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.

11.png

From here you can filter out tokens (example WLEO 😊), dates, wallets and get what you need.

All the best
@dalz

Posted with STEMGeeks

Sort:  

Great use of Power query, nice work

Posted Using LeoFinance Beta

Hey thanks!
I'm trying to diversify from Hive data :)

great. thanks for sharing.

Posted Using LeoFinance Beta

You are welcome!

Posted Using LeoFinance Beta

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