Crypto Profit Tracker Ver 0.1 - Track your USD profits on your investments

Crypto Profit Tracker v0.1

https://drive.google.com/file/d/0B1OO1bPJSFxzXzZnTDZNRS0wN0E/view?usp=sharing

I’ve created the following spreadsheet utility so that users can easily track their live USD/Filat profits and losses. Unlike Blockfolio or coingy that only capture the profits/losses between your buy in price and current price, this utility will factor in everything - coinbase fees from fiat to bitcoin, credit card fees, network transfer fees, and exchanges fees.

The result of this is that you can an accurate look of your portfolio value as well as an exact amount for your profits or losses.

What’s in version 0.1
Version 0.1 is the intial beta release of this tool. It includes the following features:

portfolio tab

The portfolio tab captures all the information need to track your crypto investments. The top fileds have the Coin name, your total amount of shares in that coin, the 1 hour change, and total value of those shares you own in USD.

There is then a total overview of your portfolio:
-Portfolio Value: The USD value of all your holdings of all your coins
-Investment: The total USD amount spent to purchase your coins, including Tx fees
-Withdrawls: the toal USD amount you cashed out into your bank accounts
-Profit/Loss: The total amout in USD you have in profits or losses, updated live

This tab updates every 60 seconds for a live look.

Buys Tab

The Buys/Deposits tab tracks the USD amount you spent to buy into crypto. This would be the amount you transfered from your bank account or credit card into bitcoin/ethereum/litecoin through coinbose or other exchange. It tracks the date of your purchase, what coin you bought/deposited into (allows only BTC/ETH/LTC) and the USD cost to make that deposit including fees. The notes filed is for your own personal tracking.

It is important to enter exact amounts here, as the portfolio values are calculated on these amounts

Sells Tab

Similar to the buys tab, the sells tab tracks all of the coins you have cashed out into USD depositing/transfering them back to your bank account via coinboase or other exchange. The values are the same as the buys tab and like the buys tab it is important this information be accurate as the portfolio values are caluclated off of these fields.

Trades Tab

The trades tab tracks all of your trades and all of your alt coin purchases. the coin on the lefthand side is the tethered coin you are trading/selling out of (typically bitcoin). The coin filed on the right is the alt coin you are purchasing or trading for. the quantities are all in satoshi. You do not need to worry about USD value here as that is automatically calculated. The notes filed is for your own personal notes.

Like the other tabs, the portfolio values are calulated off of these fields, so they need to be accurate.

Ticker Tab

The ticker tab imports all of the live coin data from coinmarketcap.com through their API’s. The sheets us auto updated every 60 seconds. These sheet should not be touched or edited, as any manual changes will effect or break the formulas in the spreadsheet.

Data

The data tab has several cells were values are calulated. this data is all hidden. this tab should not be touched or edited. doing so will break the forumulas.

Version updates

I will be updating this tool over time. This is beta release. please post any issues or bugs you may find. the following enhancements will be added to the next version:

-more alt coins
-a tab to enter in coins aquired at no cost (via mining, profitsharing, air drops, etc)
-bug fixes

An example of how to use these sheet will be posted below.

27 Likes

Example on entering data and tracking your portfolio

IMPORTANT!!! The example fields on the buy/sell/trade tabs should not be overwritten as the values on those rows will not be calculated. you can delete them if you’d like but start your first transactions on the next row.

Im going to run through entering data into the portfolio to track your investments from start to finish. I’ll use some real transactions ive made. The first thing you need to track, is the US dollars you deposit into crypto wallets.

In this example, I transfered $500 from my credit card to my coinbase BitCoin Wallet:

Now, coinbase charges a 4% fee on credit card transfers, but we dont need to worry about that.

We are going to enter the full amount we paid ($500) and the bitcoin amount purchased into the spreadsheet on the Buys Tab:

If we check the portfolio Tab, we will see that it has updated our total amount of bitcoin owned, shows the USD value of the shares we own, and updated our investments and profits. We are currently at a profit, since we invested $500 on the 9/15 when bitcoin was much lower then it is now. So the sheet automatically accounts for that, making historical transactions supported.

image

Next, Lets say we wanted to buy some NEO. We purchase/trade for it through bittrex with our bitcoin we just got through coinbase. A max buy gives u 27 shares of NEO

We now need to enter this alt coin purchase/trade on the trades tab. The information is all in the bittrex orders shown above making it very easy to enter.

-for the first coin field, we will chose bitcoing from the drop down menu, since we are using our bitcoin to buy NEO

-For the quantity field, we enter the amount in the cost/proceeds field in the bitrex order. this is how much bitcoin we spent to buy the NEO

-in the second coin field, we choose NEO from the drop down menu. since this is coin we are trading for.

-in the quantitiy field we enter 27. the total amount of NEO we are purchasing.

Now that we entered our trade we can go back to the portfolio tab which has been automatically updated:

image

As you can see our total bitcoin has been reduced after the transaction and the 27 shares of NEO we bought were automatically updated into our NEO field. Since NEO has dropped since 9/15, the date of the trade, we see that our profits have taken a hit.

Now, Lets say NEO goes on a run, and the price jumps:

image

As you can see, when the NEO price went up, the USD value of our NEO holdings was automatically updated as well and our Profits increased.

Ok. now that we profited, we want to cash out. So lets say we sell off 10 shares of NEO netting us .07000000 Bitcoin. We would need to enter this trade amount in trades tab:

This time, NEO is on the left side, since we are selling/trading it back for bitcoin, and Bitcoin is on the right. once we enter this, our portfolio tab is auto updated:

image

And there it is, our bitcoin has been increased and our NEO decreased.

Lets say we move our .07 bitcoin back to coinbase. we then withdraw our bitcoin and transfer the USD value of it back to our bank account. after the transaction fees, our bank account shows a credit of $398. even though the .07 BTC = $406, we only track the actual dollar amount that htis out bank account. we enter this in the sells tab:

And now that we have entered the sell, the portfolio is again auto updated:

image

So some good stuff happening here. We now see that the withdrawl fee shows $398. the amount of money we sent to our bank account. and although our current live total portfolio value shows as just $177 more then the total amount we invested, our prodits show the correct amount of $575… taking into account the profits we cashed out.

I hope thats all clear. Peter may do a video on it. Also, please bring any bugs to my attention or anything you would like to see added in the next release. this is an expanded version of the sheet i used but what works for me may not be the best possible version we can make

7 Likes

this is fantastic jason. … you know what’s coming…

3 Likes

Congrats man! You’ve earned another one.

12 Likes

can i save this into apple numbers? or should i just drag a link to my desktop? this is great, thank you!!

2 Likes

Double Doge!

ill update the coinipedia this weekend to to hopefully get the triple doge… i need to get away from the screen for a while!

5 Likes

Great work! This is excellent!

3 Likes

You totally deserve that BADGE!!! Thank you so much for Generously sharing with us!!! YOU ROCK :sunglasses:

3 Likes

Thanks…

by the way when you open it just ignore that macro warning that steals all your private keys and sends all your cryto to my wallets muwahahahahahah :slight_smile:

3 Likes

opened it then opened in google sheets but the price doesnt seem to be refreshing? am i just using it completely wrong?

2 Likes

im not sure if it will work in google sheets, since it uses several excel functions and APIs.

you may need to download it and use it in excel

3 Likes

Google sheet scripts are a little different @Teddy

4 Likes

ok, when i “downloaded as” then excel Numbers auto ran it. don’t think refresh is working for me but i can still plug it in to see where i’m at. great work man, this is fantastic!

2 Likes

Man, thank you so much for this!! everyone should download this.

2 Likes

when you first open excel it may prompt you on the top ribbon to allow externabl content. you want to click enable/allow on that.

2 Likes

This is awesome! Now I don’t have to create my own heh :grin:

3 Likes

i didn’t see that, this is what it looks like when opened… is there a way to turn that on in preferences? thanks for helping me try to figure it out. still dont mind plugging in numbers lol

2 Likes

then its this after i open it in google sheets

2 Likes

to manually enable external data connections

3 Likes

💰 YEN · DCTV ·️ Bitcoin Lambo · 10 Days of Bitcoin ·️ CEO's Brainpan 🧠