Weekly Re-balancing over 2017 (Was Mislabeled)

I should preface this by saying this data has not been verified 100%, although there were issues I did catch which I will mention below I am only human and I’ve had my fun with this. Use Data at your own risk.

Hey everyone,
With that out of the way,
So this was supposed to go up a few days ago but I ran into a few issues pulling Data from specific websites which I wont name for…reasons lol…I did however end up getting my scripts to work and pull data from the Historical API provided by Cryptocompare, the price data differs from coinmarketcaps historical data by a small margin, I’m not exactly sure why this is but It’s definitely caused at least in some part by the exchanges each website collects its data from and then how they average those prices overall.

I will proved URLs and a description of how I generated this data for those technically inclined that would like it for verification.
Meaning I did not enter these values manually, I entered at most 30 Values from coinmarketcaps historical data because of errors or a way to cut down on a pointless amount of code (These being, BCC Bitconnect only occurs about 4 times in the sheet however during that time the backend of the api not on my end seems to start think I’m looking for data on BCash or Bitcoin which it interperates all 3 as having at one point in time been called ‘BCC’, I have no reason to believe that this problem has occurred with any other coins in the data however I may be wrong.
For self verification from the cryptocompare api you can use these links

CryptoCompare: https://min-api.cryptocompare.com/data/pricehistorical?fsym=BCC&tsyms=USD&ts=1509235200
Date to Timestamp: http://www.convert-unix-time.com/

To use the url simply replace ‘BCC’ with any coin symbol you would like to check and enter the date into the Date to Timestamp url, which will generate, you guessed it a timestamp, which you will use to replace ‘1509235200’, for those who would like to use this at a later date but would like the BTC value of coins simply change ‘USD’ in the url to BTC, or if you would like both simply usd ‘USD,BTC’. This will give you the USD price at the given date.

The excel equivalent of this I used was
=MID(TEXT(IMPORTData(“https://min-api.cryptocompare.com/data/pricehistorical?fsym=”&ENTER COIN SYMBOL HERE &"&tsyms=USD&ts="&( ENTER DATE HERE -DATE(1970,1,1))*86400&"&extraParams=your_app_name"),0),12+LEN(ENTER COIN SYMBOL HERE),LEN(IMPORTData(“https://min-api.cryptocompare.com/data/pricehistorical?fsym=”& ENTER COIN SYMBOL HERE &"&tsyms=USD&ts="&( ENTER DATE HERE -DATE(1970,1,1))*86400&"&extraParams=your_app_name")) -13-LEN(ENTER COIN SYMBOL HERE))

Other than all that. Some interesting Data I thought was that these were all the coins that were ever in the top 10 in 2017.

ADA Cardano
BCC BitConnect
BCH Bitcoin Cash
BCN Bytecoin
BTC Bitcoin
BTG Bitcoin Gold
BTS BitShares
ETC Ethereum Classic
ETH Ethereum
GNT Golem
LTC Litecoin
MAID MaidSafeCoin
REP Augur
STRAT Stratis
XLM Stellar Lumens
XMR Monero
XRP Ripple

Donations are appreciated but not implied by any means @ https://walletlist.me/@KyuudousKintsugi

I’m sure I’ve forgotten to mention something as I’m yet to have breakfast but If i think of anything I’ll edit it below this point.

Oh Right So, a lot of people I’m sure will be curious how this data measures up to date from other cost averaging sheets such as the Monthly, Quarterly ,and Annually. Well I’m going to have to say you cant really compare them, as I have no prior knowledge as to what websites were used to create the initial data sets, I will however be redoing those with the same technique I used for this Weekly as to give a better representation of what the data should look like in comparison to eachother, It is quite obvious the profits from weekly are lower than that of the monthly of the previous data sheets however I really dont think they should be compared, I will rectify this within 48 hours

EDIT 2 WOOPS accidently deleted the sheet, reposting IT sorry!

If Coinmarketcap ever releases an API for their historical data I will redo it with that Data, Now that I thnk about It i’m sure people are wondering why I didn’t ImportHTML Tables from their website…Well I initially did, about 6 months worth of Data and then got IP banned from their website for a few hours… SORRY COINMARKETCAP!

I know its a lot of Data but if anyone even has a feeling something might be wrong, please let me know and I’ll look into it!


The way I was automatically generating the EOW Value which is the total usd value of the coins you bought that week after 7 days was using a VLOOKUP to search the next weeks data to see If ive already called for it, However as coins consistently drop in and out of the top 10 spots such as 10,9,8 there were many times the data couldn’t find what it needed so I would redo the call to grab the data from the API, this caused a lot of the initial problems, I mean i already had something like 520 calls to the API running, 1. Google Sheets was very upset, 2.CoinmarketCap was very upset, 3. I was very upset so Instead of recalling the values it couldn’t find I manually entered about 30 values, I mentioned this in the beginning of this post but got side tracked talking about something else.
The function I used for that was…

=IFERROR(VLOOKUP(G1,$B15:$D24,3,false)*J1,MID(TEXT(IMPORTData(“https://min-api.cryptocompare.com/data/pricehistorical?fsym="&H1&"&tsyms=USD&ts="&($B13-DATE(1970,1,1))*86400&"&extraParams=your_app_name"),0),12+LEN(H1),LEN(IMPORTData(“https://min-api.cryptocompare.com/data/pricehistorical?fsym=”&H1&"&tsyms=USD&ts="&($B13-DATE(1970,1,1))*86400&"&extraParams=your_app_name”)) -13-LEN(H1))*J1)




Ho ho! This is juicy stuff! Thanks!

1 Like

No Problem Peter, I’d like to reaffirm what I said in the Edits, This Data isn’t very useful on it’s own, in fact now that I think about it it’s probably worthless without the others, so I’ll be redoing the Monthly, Quarterly and Biannually with the same data source to give better comparison as it shouldn’t be compared with the Monthly and so on of sheets made through what I assume was coinmarketcap.

1 Like

Added Monthly For Comparison. And yep Monthly Comes out on top much like the Data of previous monthly data sheets.

Also, my plan to verify that I haven’t made any human errors is to completely redo the weekly. Which is a far smaller task than you’d imagine I’ll likely spend most of my time waiting.

1 Like

Update “So In my rush I called this cost averaging when its actually weekly re balancing…”

1 Like

THANK YOU! Great work.

Just sent you 5000 DOGE!

:doge: :doge: :doge: :doge: :doge: :doge:


A man of your word! Thank you <3


Some thoughts.

As we all know, things move at the speed of light in Crypto.

Something that is an issue now with the top 10 that wasn’t as much of an issue last year is the rapid appearance of shit coins into the Top 10. I’ll leave which ones are complete shitcoins…cough TRX…to you to decide.

That said, shitcoins can make you money if you catch the trajectory right. Where rebalancing weekly instead of monthly would come in useful is the ability to get into and out of a shitcoin quickly having less of a chance to harm your portfolio.

Even now, shitcoin TRON is still above ICX.


good LAWD! nice work there, thx!


I agree, even though it turns out that monthly gains you better profits you are right that you can get unlucky and get caught in a shit coin for a month, however this could be outbalanced by the positive coin investments(though theres not enough years of data to confirm that). As Weekly EOY is $953,826 and Monthly EOY is $1,106,060, I do agree weekly is likely more reliable however is the reliability going to out way the possibility of $150k, I’m not sure, there may not be enough years to accumulate data to test it, plus the weekly time invested into doing that re balancing every week, (Unless automated which I’d love to look into personally) People will likely take the less time intensive path and do monthly.

On your second point I mostly agree, I think 2017 has had a lot of unworthy coins in the top 10 however will 2018 have more? Maybe, but it’ll definitely have as much as 2017.

Your point of trajectory is interesting, perhaps a position 11 to 20 coins weekly re-balancing sheet would be worth doing as that’s likely the best place to get into those coins at the right trajectory. What are your thoughts on this?

1 Like

I’m already moving away from attempting to deal with more than 15 coins. It’s too much of a headache. There’s too much movement and considering the big slide we’ve been going through, most of our entry positions would put us in the negative if we had to liquidate simply due the fact that one of our coins slipped out of it’s position.

We spend all this time researching and selecting coins with good long term potential (long term = minimum 1 year) so it’s short sighted to sell it at a loss.

Bottom line I think it’s better to keep it to top 10, top 15 at most and re-balance bi-weekly at minimum but monthly would be more reasonable. Trade fees and transfer fees are killers.

Also, those that do fall out of your Top-X list, should possibly be put in a holding pattern when the entire market has taken a shit. Wait until the market has recovered then at least you can get out by breaking even.

1 Like

Add in variables such as fees and moving between wallets & exchanges, I’d like to see just how lean the process can be. Time will tell and this is the benefit of community.

1 Like

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