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.
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
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.
BCH Bitcoin Cash
BTG Bitcoin Gold
ETC Ethereum Classic
XLM Stellar Lumens
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…
UPDATED AND ADDED MONTHLY FOR COMPARISON DATA.!!!
HAD THIS MISLABELED AS COST AVERAGING AS I WAS IN A RUSH TO WRITE THIS ALL WHERE IT IS ACTUALLY WEEKLY RE-BALANCING.