HODL Back Tester Google Spreadsheet


#143

For those who use google spreadsheet to track crypto! Here is a good resource that I found! Really useful!
https://jbuty.com/how-to-get-crypto-currencies-rates-and-more-in-google-sheet-1a57e571bc14


#144

Glad you are enjoying it!


#145

I believe you are talking about the MY PORTFOLIO tab?
If so, I made some updates just now that enhance it. Grab a new copy of the HODL Back Tester

Also, there is a stand alone copy of My Portfolio Tracker too: https://docs.google.com/spreadsheets/d/1b5udXvmfE_L-XityethpZQ6VFZlCXqO4fTxRuhst-Rs/edit?usp=sharing#null


#146

@cryptoBrad How are you pulling in current prices?

I saw this post on Medium, and figured I’d see if it might be helpful.


#147

@CryptoMoon
Hi,
I’m pulling them in from CoinmarketCap.com using a Table import. into a Google Sheet, then use that sheet to feed the HODL back tester.
I get the top 400 coins for the current prices.

the way MEDIUM describes is useful on smaller things like a portfolio…but not in scale.
Plus it’s hard to capture the total market cap size.

i.e. the major difference is:

On CMC…get top 400 coins by marketsize, and tell me the symbol and price
On the Meium method; For the coin name I already know, get me the price or market cap.

So if you don;t know the “top 400”, then you have more effort to exert.


#148

Yes, thats what I was thinking of. Instead of having to constantly import fresh tables it seems like it would be nice to have it automatically pulling the latest data.

My other concern is giving the Google account data access to a 3rd party. Not sure if thats much of a security issue or not.


#149

you raise a good point @CryptoMoon
In the sheet I’m using, the formulas are all local and transparent.
one could reproduce it themself if they so choose.

When you install an Add-On…the Author can update it at any time.
Mostly, you could expect it won;t do any harm, (BUT, there is no guarantee either!)

I like making things more “open source” so you are 100% certain of what’s going on.

If i did make a macro or custom formula, you(& the world) can at least look at the code and know it’s intent)


#150

Your hard work is MUCH appreciated. Will help spread the word about BitCoinPub and this HODL Back Tester Google Spreadsheet. Once again, thanks cryptoBrad.


#151

Thanks a ton for this. Great spreadsheet! It looks like the best years for TOP 10 might already to be over. Maybe I did something worng but it seems this year has been quite bad in comparison to 2016. I back tested from Jan 16 to Jan 17 in top 10 coins and returns were 1423%! huge
I then back test this year only - Jan 2017 - Oct 2017 and returns were 395%. More than 3 times less than 2016.

Perhaps it wiser to invest partially in top 5 coins and then 5 in strong up and coming coins?


#152

Woops this was suppose to go in another thread.


#153

#154

One thing to take into consideration is the hold time.
Looking at 2015 and 2016…you in essence are holding those coins until today (12+ months later)

doing research in 2017 kinda means you really need to wait until 2018 to really see the comparison between 2015, 2016 and 2017 growth.

It can be a bit deceiving, since years do not repeat them self, however, I feel that the market is always moving upwards, of course with spikes and dips in between.

Short story: HODL


#155

Yes I was quite amazed about the topic and was just about to invest into the top ten then thought I’d better just check how this year (so far) compare to last. Of course we don’t know how next year will be but I would think the percentage gains of the Top 10 would probably not be as dramatic as the previous years. Hard seeing Ethereum doing etc doing the same percentage gains.
Just thought I should point this out.
Wonder if investing in the Top 10-20 coins would be more profitable???


#156

So, for fun…use the COMPARE tab and enter a few scenarios…then click RUN button at top.

It’s a bit easier to see scenarios side by side.


#157

When I changed the 400 value in the current prices tab to 1000 I was expecting the list of current prices to go to 1000 coins but it didnt ? Does the coinmarket cap API only include the top 400?


#158

Yes that is a limitation right now up to 400. I have a new API in testing that does up to 2000. Pushing an update this weekend. Stay tuned!


#159

Thanks Brad!

I just mined about 10000 ETN coins which is position ~965 on coinmarket cap so would be good to get auto price updates on this.

Please post here when the update is ready so I can update my sheet.

Also i’ve heavily modified your sheet with graphs more tabs etc. Instructions on how to drop in the new API into an existing version of your portfolio tracking sheet would be much appreciated.


#160

Will do! test has been working steady for this week so I’ll post it Saturday and reply here


#161

2017-11-08 - Update to HODL back tester.
The current Tab now gets up to 2000 coins (it was limited to 400 before)
This will be good for things like the portfolio tab.

It’s not required to get a new file, but if you wanted, just get a new copy of the file (use link at top)

if you have customized the sheet, on the tab called CURRENT should now show over 1,000 coins

@Russell_Goulter on the CURRENT tab, change the range to
=IMPORTRANGE(“1MvKa6jdqaETgqoFDvENVVU4EkeKRmP1MjL7jEn4LGm0”,“Current!A1:H”)
(This was F before, change it to H and this will expose 2 new columns (timestamp and BTC price))


#162

Oh man thats Awesome. I have updated my portfolio sheets easy as.

Thanks Brad you’re the best !!