Bitcoin MCAP Logarithm Trend Line Trouble

[ThomasPepperz]

btc_mcap_log.xlsx1.JPG

Excel community, I am a R and Python user who has been requested to create a logarithmic trend line for the market cap of Bitcoin. However, the logarithm trend line is not behaving as I have expected it to and could use advice regarding how to proceed. Please see the attached document for data and a chart I have produced. I have also attached an image of what I am supposed to attempt to recreate. In the attached photograph, the trend line begins at the data's beginning point and fits the data seemingly well. In my Excel document I have attached, the trend line appears to begin at an arbitrary point and doesn't fit the data very well at all.

A few notes. I have not transformed the data but have only adjusted the y-axis to be on a logarithmic scale 1, 10, 100, 1000, et cetera.

Do I need to actually transform the data?
Is a logarithmic trend line even appropriate in this situation?

I produced a linear trend line and it fit the data quite well with a r^2 value, but I am being told to produce a log trend line that fits the data.

[MrShorty]

Other than a poor fit of this data set, is there something else about your logarithmic trendline that is not behaving as you expect? As near as I can tell, the regression is "correct", and the function's behavior is consistent with a logarithmic trendline.Some things I have done so far:

1) First was to select your trendline -> format trendline -> display equation in chart -> format with 14 decimal places. Just so I could see the actual equation Excel was getting from the regression. y=a*ln(t)+b where a~1.57E12 and b~-1.66E13.
2) Recreate regression in spreadsheet using LINEST() function. I get the same as the chart trendline -- suggesting that the regression (according to the linear regression algorithms built into Excel is "correct").
3) I note that y=aln(x)+b has no constraint to force y to be greater than 0. Computing estimated y's from the regression equation shows that there are many points where y is negative. A logarithmic axis (like your y axis) cannot display values below 0, so the chart truncates the trendline where it goes below 0. I think that should explain why the trendline seems truncated.

At this point, I am not sure why you are being asked to use a logarithmic [y=aln(x)+b], but it doesn't seem very well suited to the data.
4) Where you are trying to use a semi-log plot, that suggests to me that you want a trendline that is constrained so that y>0. As a guess, I tried a power trendline [ln(y)=aln(x)+b -- which would yield a straight line on a log-log plot], and the fit wasn't too bad. However, this is getting away from the logarithmic trendline you were asked to use.
5) Looking at your semi-log plot, y kind of has a logarithmic shape on a semi log plot, suggesting that I try ln(y)=aln(ln(x))+b. That also seemed to give a reasonable fit to the data. This is getting further away from the logarithmic trendline you were asked to use. I would note that, while it might not be strictly necessary to transform the data before performing the regression, I tend to prefer to add the helper columns in the spreadsheet for ln(x), ln(y), or whatever other transformation I choose to use.

At this point, my conclusion is that a logarithmic trendline (y=aln(x)+b) fit using Excel's built in linear regression algorithms (if the choice of regression algorithm makes a difference) is not a good fit for the data. It could be possible that your people had something else in mind when they said logarithmic trendline, or maybe they are open to other regressions, but I cannot answer those questions for you.

[ThomasPepperz]

Mr.Shorty,

Thank you for the lengthy and detailed reply. I was not expecting such thoroughness from a random forum. I can tell this is a good one.

1.) First of all, let me say I agree with your analysis for the most part. I told my client that this is a semi-log transformation and that the data was mostly linearized and the R-squared value for it was good ~0.86. Also, I explained to him that r^2 is not an appropriate metric to use to evaluate a non-linear regression model's goodness of fit for a data set.

2.) My client is not interested in the fact I am a data scientist or anything like that. Simply, all he wants is to be able to reproduce the previous "statistician's" chart. Were you able to view the image I posted? That is not my chart, but the previous guy's chart. It's the same dataset except with a few months of recently-added data. I don't know how he created that trend line, but it begins at the beginning of the data and moves smoothly through the data. I know he created it using Excel. Do you think he could have drawn it or manipulated the data somehow? Do you think it's possible he played around with the equation until it did exactly what he wanted and repeated each time he used updated data?

3.)I've tried to load screenshot of a graph I produced with a linear trend line through the data I referenced in point one. Not sure if loaded properly. If not, I'll try to edit my original post and place it there.

4.) If you could provide me with an Excel sheet or a method to produce a curved trend line that reflects the one from the screenshot in the original post, then I'd be happy to send you a tip via PayPal or if you're a crypto guy I could send you some ETH. I know it's madness, but this guy wants what he wants.

[ThomasPepperz]

I don't have forum privileges yet to post attachments or images in a reply yet but if you want to contact me at one of the following I'll send you what I'm referring to: Thomas.pepperz (at) southernanalytica (dot) com or my Twitter user name is ThomasPepperz or Skype is tpepperz

[MrShorty]

I can see your picture. If I am correct, a quick internet search found a user named Awe and Wonder (see credit in your chart) who seems to be the source of the image/algorithm. An article at medium: https://medium.com/coinmonks/bitcoin...s-22d8acaaa801 I can find stuff by him/her, but I cannot find a detailed description of what this logarithmic regression actually looks like.

It is not at all clear to me what he/she means by "logarithmic regression". It seems clear to me that it is something other than linear regression on y=a*ln(x)+b. I note that your chart suggests maybe 3 parameters in the regression. Your picture has an MC parameter (~226E9), a trend parameter (~180E9), and a growth rate (6.1%/month). I have no idea what these parameters mean, or how they are obtained. If your client is not only expected a pretty picture, but also expecting meaningful regression parameters, I see no way to help you until someone can identify the actual regression equation being used here.

If all you need is a pretty picture with some kind of curve that looks vaguely like the picture in the OP, the "double log" regression formula seemed to generate a similar shaped curve. I cannot upload a spreadsheet at the moment, but I wouldn't be at all surprised if you could generate a ln(y)=a*ln(ln(x))+b regression in R or Python as fast as I can put it together in a spreadsheet. Of course, a and b have no real meaning (other than empirical parameters). Are you required to use Excel for the chart?

I am not aware of anything that should prevent you from uploading a picture or other attachment. I know that the "paper clip" icon (I don't see it in my browsers) doesn't work, but you can always use the Manage Attachments link from the main post editing window (from either the "Reply to Thread" button/link or the "Go Advanced" button/link).

[ThomasPepperz]

Yeah, I'm aware of the Medium article and Twitter account. I've determined that there is no logarithmic regression algorithm implemented. I'm not sure if the curve was drawn using a tool, and I have no idea what is meant by logarithmic regression either as the trend line is in no way a logarithmic line, there is no use of the natural log, the y-axis has been transformed using a 10 LOG, et cetera. The MC parameter is the current market capitalization of Bitcoin at that time, derived by taking the current Volume-Weighted Average Price of BTC at the time of the graph's creation (I presume) and multiplying that figure by the circulating supply of BTC at the time. The growth rate must refer to the increase in MCAP, which is both a function of potential increases in BTC's trade value but also the fact that new BTC is introduced into circulation steadily through mining.

I wasn't required to use Excel, but I was urged strongly to use it for aesthetic purposes. Since seeing your messages, I have since used LoESS to create a curved trend line that matches the one seen fairly well. I had to smooth out all of the local trend lines from the curve to make it continuous or more of a global trend indicator rather than the local trends that LOESS usually produces, but the client has the curve trend line they've demanded.

I see what you mean about the loading of images. I was trying to use the attachment button. I also think this website isn't playing nicely with my Safari browser potentially.

I've attached that earlier attempt I made with Excel where I used a linear trend line and thought it fit the data much better than the curve and especially the log regression line. I've also attached a photo of the chart I produced in R using the LoESS I was telling you about after I manipulated it just for a follow up.

I really appreciate your feed back as it largely confirmed what I suspected. I just didn't know if I were going crazy because I was told that chart had been produced with a logarithmic regression and my visual inspection and use of Excel was telling me "no." Should've just followed my instincts.

[shg]

Here are two regressions of bitcoin price from inception to the end of 2017, both using LINEST, with the second also using Solver to optimize an offset term.