My Poly Address: PLQ1iADcKhkZgKJ13a265cVfqihFxieS6n
Updated 1/14/2018 -
-API Calls are only made once per coin (Fetched Data), this will lessen the chance you run out of Google's API Call limit per day.
-Current Holdings and Gain / Loss on the Main Sheet now grabs the coin symbol/names from the Symbol field, you will not have to edit the formula for new coins, simply add a new symbol
-Corrected totals for Row 63 and Row 97 on the Main Sheet. Remember when you add new coins to change the totals formula.
-Added query formula to pull API from Crypto-Bridge.org for Polytimos price. (This site seems more stable then previous coinsmarkets)
Hey guys, I made some adjustments on the Sheet, I just noticed randomly the CoinsMarket data fetching error's out. It's probably from cloud flare, or maybe just CoinsMarket's stability.
I made a function called "CopyForError()" which would copy the value of the price (I35) to a new field (J35). It will copy only when value is greater then 0, this way if the site ever errors out, it still has old data and once the new data is finally fetched, it'll update to the newest price.
It's time driven to check every 30 mins.
Also remember to make sure script triggers are set! Inside Script Editor , goto Edit --> Current Project Triggers
I believe you have to open the script editor once the file is copied, and ran once, It may prompt you to allow script to run (ImportJSON). Just select function and hit the "Play" button , do this for both RefreshHouly and CopyForError
I guess Final edit (1/4/2017):
on to the MAIN section:
Only formula that needs to be changed is "Market Price"
This time, just edit the very top one, and get the formula, it's gonna be long best thing to do is just DOUBLE CLICK it, so you can see the edit box right on top of the sheet:
So we have...
=IF(COUNTBLANK(B5:D5)=0, IFS(B5="BTC",$N$4,B5="ETH",$N$5,B5="LTC",$N$6,B5="VTC",$N$7,B5="XRP",$N$8,B5="XMR",$N$9,B5="DASH",$N$10,B5="XEM",$N$11,B5="ZEC",$N$12,B5="HUSH",$N$13,B5="PXC",$N$14,B5="MONA",$N$15,B5="BTG",$N$16,B5="GRS",$N$17,B5="TZC",$N$18,B5="HYP",$N$19,B5="RAIN",$N$20,B5="DOGE",$N$21,B5="ALTCOM",$N$22,B5="SIA",$N$23,B5="VIVO",$N$24,B5="ENT",$N$25,B5="POLY",$N$26), "")
It's pretty straight forward, we just need to add the new coin symbol so it grabs the data from the Currency Data section
the part we need to watch for is here:
remember there's comma in front of B5. Since it's gonna be added as the last line of formula. We are going to be adding after that $N$26:
Basically we are reference if there is POWR in B5, get the price from N27
then hit ENTER.
It will save the formula and you should still be seeing the data for the first line.
now we have to copy in series, so best thing to do is grab the bottom right block handle and drag it all the way down, a good amount of cells past the last data row.
when I add "POWR" having 1000 coins, It gives me the current value of having 1000 of them.
At the same time, i can see that 14% of my total value is POWR, and I'm holding total 1000, with detailed info
And at gains section is for when you actually have a book value
And that should do it!
For GAINS AND LOSSES section:
You have to edit all 3 fields on this one
do the same Copy and Pasting of last data we have, and change the symbols in all 3 Fields "Book Value" "Market Value" "Net Profit"
(I have Book Value, because my original Book has a seperate sheet for 'investments' where I had actually purchased coins with money at certain prices, so I can see both loss and gains)
For CURRENT HOLDINGS section:
(I'm not copying and pasting the POLY line because it's only USD value data that's coming from CoinsMarket instead of the more detailed CoinMarketCap)
Copy the Previous data again
This time we need to change the Symbol, but will also need to edit the cell formulas. I changed the name from ENT to POWR , and also if you look at M61, I've changed the =SUMIFS($C$5:$C$300,$B$5:$B$300,"=ENT") to =SUMIFS($C$5:$C$300,$B$5:$B$300,"=POWR")
rest of the field will calculate automatically.