{"id":570,"date":"2022-07-24T07:51:56","date_gmt":"2022-07-24T07:51:56","guid":{"rendered":"https:\/\/giuliopulino.com\/?p=570"},"modified":"2022-07-30T11:14:49","modified_gmt":"2022-07-30T11:14:49","slug":"crypto_project","status":"publish","type":"post","link":"https:\/\/giuliopulino.com\/index.php\/2022\/07\/24\/crypto_project\/","title":{"rendered":"Crypto Wallet Tracker"},"content":{"rendered":"\n

<\/p>\n\n\n\n

Step by Step tutorial on how to create a Crypto Wallet Dashboard with Excel and PowerBi using API from CoinMarketCap.<\/p>\n\n\n\n

<\/p>\n\n\n\n

The dashboard will show the price of the crypto in real-time (just click on refresh to have the data updated) so that we can keep track easily of our investments.<\/p>\n\n\n\n

Below my dashboard in PowerBi, if you wish you can scroll down and see the entire process step by step.<\/p>\n\n\n\n

<\/p>\n\n\n\n

The Dashboard it’s available at the following link:<\/p>\n\n\n\n View Dashbaord <\/a>\n\n\n\n

Preview of the Dashboard.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

Step by Step Tutorial.<\/strong><\/h3>\n\n\n\n

We’ll start with Excel, you can start directly in PowerBi, the process it’s the same, but in PowerBI it’s more tedious to update the data, that’s why I suggest you start in Excel.<\/p>\n\n\n\n

Let’s Start !!<\/strong><\/p>\n\n\n\n

First of all we have to create a FREE account on CoinMarketCap:<\/p>\n\n\n\n https:\/\/coinmarketcap.com\/api <\/a>\n\n\n\n

After that we can start to import and format the data and create our dashboard.<\/p>\n\n\n\n

Open Excel, go to the “Data”tab, click on “New Query” -> “From Other Sources” -> “From Web”<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Select “Advanced” and fill in as follows:<\/p>\n\n\n\n

URL parts: https:\/\/pro-api.coinmarketcap.com\/v1\/cryptocurrency\/quotes\/latest?symbol=<\/p>\n\n\n\n

After the equal sign, insert the symbols of the coin that you want, in my case BTC,ETH,ADA,CRO,DOT<\/p>\n\n\n\n

So the link will be: https:\/\/pro-api.coinmarketcap.com\/v1\/cryptocurrency\/quotes\/latest?symbol=BTC,ETH,ADA,CRO,DOT<\/p>\n\n\n\n

In the HTTP Request Header insert: X-CMC_PRO_API_KEY (you can find it in the documentation API page, at the following link: coinmarketcap.com\/api\/documentation <\/a>)

\n\n\n\n

And lastly your API Key, then click OK.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Select how to connect, I’ve selected Anonymous<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now we are into PowerQuery, go on the top right side menu, and click on source, on the center a new table appears, click on “Record” of the DATA row.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Then click “Into Table” on the top left.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now you have the data as follows, click on the 2 arrow icon<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

You’ll see a list of columns, uncheck everything and select what you need, in my case I selected name, symbol, max_supply, circulating_supply, total_supply and quote. (Quote is at the bottom of the list, remember to add it), then click OK.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

On the generated table, select the “value.quote” column, click on the arrow icon, and then OK. <\/p>\n\n\n\n

NB: the NULL values on max_supply are normal because some coins don’t have a fixed number of coins.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now reopen the same column clicking on the double arrow icon, and you can find new voices, I selected price, volume_24h and market_cap.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Let’s rename the columns, just double-click on the column name and rename it.<\/p>\n\n\n\n

For example replace NAME, with symbol, value.id with id, etc… <\/p>\n\n\n\n

If you find a duplicate column, right-click on it and remove it.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

We have to format all columns to the correct data type, for example symbol as TEXT, USD_price as decimal number, etc.. click on the icon at the left of the column name and select the desired data type.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

We need extra columns that we have to calculate ourselves, in the top menu, select “Add column” -> “column from example” -> “from all columns”<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The first column will be called “money_invested”, and add for each row how much money you invested in the specific coin, then click OK.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Repeat the process and create another new column called coins (enter the number of coins you have)<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now we need new columns but with calculated data, so we’ll add formulas.<\/p>\n\n\n\n

Let’s add the first one, “average_price”.<\/p>\n\n\n\n

This time click on “custom_column”, give a name to the column and from the right menu, select the voices to calculate the average price, just click one time on it and then click insert, in this case the formula is: ” money_spent \/ coins “, then click OK.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Add more custom columns, the process is always the same, just change the formula.<\/p>\n\n\n\n

current_value = current_price * coins<\/p>\n\n\n\n

profit = current_value – money_invested<\/p>\n\n\n\n

ROI = profit \/ money_invested<\/p>\n\n\n\n

<\/p>\n\n\n\n

Last step \ud83d\ude42 format all these new columns as decimal, except the ROI which will be percentage.<\/p>\n\n\n\n

That’s all, select HOME and click on Close & Apply.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

Now we have the data in the table, we have just to convert some columns to currency as per image.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

You can save the file and close it.<\/p>\n\n\n\n

Open PowerBI, and import the excel file.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

In the next menu, flag the sheet, and click LOAD.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Click on the TABLE icon on the left side menu, we have to convert some columns to currency as per the image.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

And that’s it, now go to the view tab and create your dashboard, here my design, what’s yours ?<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

Thanks for your time.<\/p>\n\n\n\n

<\/p>\n\n\n\n

Giulio Pulino.<\/p>\n","protected":false},"excerpt":{"rendered":"

Step by Step tutorial on how to create a Crypto Wallet Dashboard with Excel and PowerBi using API from CoinMarketCap. The dashboard will show the price of the crypto in real-time (just click on refresh to have the data updated) so that we can keep track easily of our investments. Below my dashboard in PowerBi, …<\/p>\n

Crypto Wallet Tracker<\/span> Read More »<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":""},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/posts\/570"}],"collection":[{"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/comments?post=570"}],"version-history":[{"count":22,"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/posts\/570\/revisions"}],"predecessor-version":[{"id":865,"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/posts\/570\/revisions\/865"}],"wp:attachment":[{"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/media?parent=570"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/categories?post=570"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/tags?post=570"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}