Stock data type and beta calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-2089246%22%20slang%3D%22en-US%22%3EStock%20data%20type%20and%20beta%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2089246%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EExcel%20has%20a%20linked%20data%20type%20for%20stocks%20on%20the%20data%20tab.%20One%20of%20the%20fields%20available%20is%20called%20beta.%20there%20are%20many%20ways%20to%20calculate%20beta.%20Can%20you%20tell%20me%20how%20it%20is%20being%20calculated%20here%3F%20Thank%20you%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2089246%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2089261%22%20slang%3D%22en-US%22%3ERe%3A%20Stock%20data%20type%20and%20beta%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2089261%22%20slang%3D%22en-US%22%3E%3CP%3E%3CU%3E%3CSPAN%3EIt%20is%20often%20done%20by%20calculating%20the%20slope%20of%20a%20linear%20regression%20of%20stock%20returns%20against%20a%20reference%20index%20like%20the%20S%26amp%3BP%20500.%20I%20am%20very%20familiar%20with%20this%20calculation%20and%20cannot%20get%20anywhere%20close%20to%20what%20is%20being%20produced%20by%20this%20linked%20data%20type%3C%2FSPAN%3E%3C%2FU%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2090082%22%20slang%3D%22en-US%22%3ERe%3A%20Stock%20data%20type%20and%20beta%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2090082%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F939803%22%20target%3D%22_blank%22%3E%40sedelstein%3C%2FA%3E%26nbsp%3B%26nbsp%3B%20Completely%20useless%20Excel%20attachment.%26nbsp%3B%20Double-check%20to%20be%20sure%20that%20you%20attached%20what%20you%20intended.%26nbsp%3B%20For%20a%20general%20description%2C%20complete%20with%20a%20good%20example%2C%20see%20%3CA%20href%3D%22https%3A%2F%2Fwww.investopedia.com%2Fask%2Fanswers%2F102714%2Fhow-do-you-calculate-beta-excel.asp%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%22How%20do%20you%20calculate%20beta%20in%20Excel%3F%22%3C%2FA%3E%20(click%20on%20title).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2090099%22%20slang%3D%22en-US%22%3ERe%3A%20Stock%20data%20type%20and%20beta%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2090099%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F146717%22%20target%3D%22_blank%22%3E%40Joe%20User%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecompletely%20useless%20add%20in.%26nbsp%3B%20%26nbsp%3B%20I%20know%20how%20to%20calculate%20beta%20from%20return%20series.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attachment%20shows%20what%20the%20excel%20linked%20data%20type%20returns.%26nbsp%3B%20It%20gives%20you%20a%20beta.%26nbsp%3B%20Doesn't%20tell%20you%20the%20benchmark%20or%20the%20time%20period%20used%20in%20the%20calculation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2090270%22%20slang%3D%22en-US%22%3ERe%3A%20Stock%20data%20type%20and%20beta%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2090270%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F939803%22%20target%3D%22_blank%22%3E%40sedelstein%3C%2FA%3E%26nbsp%3B%26nbsp%3B%20Re%3A%20%60%60The%20attachment%20shows%20what%20the%20excel%20linked%20data%20type%20returns%60%60%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20I%20don't%20see%20the%20data%20or%20links%20because%20my%20older%20version%20of%20Excel%20does%20not%20support%20the%20feature%20that%20you%20depend%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20see%20in%20the%20original%20Excel%20attachment%20is%20%23VALUE%20in%20A1%2C%20%7B%20%3D_FV(A1%2C%22Price%22)%20%7D%20in%20B1%2C%20and%20%7B%20%3D_FV(A1%2C%22Beta%22)%20%7D%20in%20C1.%26nbsp%3B%20And%20there%20is%20no%20%22data%22%20tab%20%3CEM%3Eper%20se%20(sic)%3C%2FEM%3E%2C%20which%20led%20me%20to%20suspect%20that%20you%20attached%20the%20wrong%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20some%20other%20way%20that%20you%20can%20present%20the%20%22linked%20data%22%20that%20does%20not%20depend%20on%20new%20Excel%20features.%26nbsp%3B%20Perhaps%20a%20URL%20that%20would%20permit%20us%20(me)%20to%20access%20the%20source%20data%20manually.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-----%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERe%3A%20%60%60Doesn't%20tell%20you%20the%20benchmark%20or%20the%20time%20period%20used%20in%20the%20calculation%60%60%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoesn't%20sound%20like%20an%20__Excel__%20question%20%3CEM%3Eper%20se%3C%2FEM%3E.%26nbsp%3B%20Perhaps%20you%20should%20direct%20your%20question%20to%20the%20creator%20of%20the%20source%20of%20the%20data%20or%20in%20a%20forum%20that%20is%20specific%20to%20that%20website.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20would%20set%20low%20expectations%20for%20a%20dispositive%20answer.%26nbsp%3B%20When%20I%20asked%20Schwab%20about%20it%20some%20time%20ago%2C%20they%20said%20they%20simply%20rely%20on%20Morningstar%20data.%26nbsp%3B%20When%20I%20searched%20for%20a%20Morningstar%20explanation%2C%20I%20did%20find%20a%20Morningstar%20webpage.%26nbsp%3B%20But%20the%20explanation%20is%20vague.%26nbsp%3B%20(At%20least%20the%20quote%20page%20is%20clear%20about%20the%20time%20frame%2C%20namely%205%20years.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20luck!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Excel has a linked data type for stocks on the data tab. One of the fields available is called beta. there are many ways to calculate beta. Can you tell me how it is being calculated here? Thank you

9 Replies

It is often done by calculating the slope of a linear regression of stock returns against a reference index like the S&P 500. I am very familiar with this calculation and cannot get anywhere close to what is being produced by this linked data type

@sedelstein   Completely useless Excel attachment.  Double-check to be sure that you attached what you intended.  For a general description, complete with a good example, see "How do you calculate beta in Excel?" (click on title).

@Joe User 

 

completely useless add in.    I know how to calculate beta from return series.  

 

The attachment shows what the excel linked data type returns.  It gives you a beta.  Doesn't tell you the benchmark or the time period used in the calculation.

@sedelstein   Re: ``The attachment shows what the excel linked data type returns``

 

Perhaps I don't see the data or links because my older version of Excel does not support the feature that you depend on.

 

What I see in the original Excel attachment is #VALUE in A1, { =_FV(A1,"Price") } in B1, and { =_FV(A1,"Beta") } in C1.  And there is no "data" tab per se (sic), which led me to suspect that you attached the wrong file.

 

Is there some other way that you can present the "linked data" that does not depend on new Excel features.  Perhaps a URL that would permit us (me) to access the source data manually.

 

-----

 

Re: ``Doesn't tell you the benchmark or the time period used in the calculation``

 

Doesn't sound like an __Excel__ question per se.  Perhaps you should direct your question to the creator of the source of the data or in a forum that is specific to that website.

 

But I would set low expectations for a dispositive answer.  When I asked Schwab about it some time ago, they said they simply rely on Morningstar data.  When I searched for a Morningstar explanation, I did find a Morningstar webpage.  But the explanation is vague.  (At least the quote page is clear about the time frame, namely 5 years.)

 

Good luck!

@Joe User 

 

No I don't know how to view it in another version of excel.  I'm not sure who the provider is only that in comes with excel and accessed through the Data tab.  I do calculate my own betas for my own purposes.  The ones Excel provides are different enough that I thought I would post in the hopes someone might know.  Microsoft support was unsupportive.   It's a lottery ticket posting here.  I don't expect to get an answer but you never know.

@sedelstein  Re: ``I'm not sure who the provider is only that in comes with excel and accessed through the Data tab``

 

Okay, now I understand.  Sorry about the misdirection.

 

The source of Excel stock data is Refinitiv.  See "About stock financial data sources" (click here).

 

My guess is:  Morningstar is the source of the beta statistic (click here).

 

In my experience, questions like this can never be answered dispositively.  We have to get to the originating financial analysis team.  And not only are they shielded by many layers of human firewalls, but also these calculations were developed so long ago by third-party software that the current crop of "owners" themselves probably don't know how things are done.

 

Thanks Joe

 

I think you are right about Morningstar.  When you type a ticker symbol on their website you get some information on the risk tab.  The data there is very close to but not exactly the same as what Excel returns.  Five years is a pretty long time for a beta calculation and I think in the case of some symbols these measures are flawed and I prefer my own calculation which is also very easily calculated given historical stock data adjusted for dividends and corporate actions (splits, etc...)     

 

Best regards

Steve

@sedelstein Far from being an expert on the subject, but perhaps it helps knowing that the Stock data type in Excel get's its data from a company called Refinitiv. See picture below. Did some testing and compared some betas from Excel to ones I found on e.g. Yahoo Finance (5Y). They match exactly.

 

Screenshot 2021-01-23 at 08.42.30.png

@sedelstein   Re: ``I think you are right about Morningstar. [....] The data there is very close to but not exactly the same as what Excel returns.``

 

Schwab had told me that "Morningstar" revises its statistics only periodically -- perhaps every quarter(?).  I suspect they mean that Schwab pulls the statistics only periodically.  The same might be true of Refinitiv.  Or it might be that Morningstar had only recently updated its statistics based on Dec'20 data, and Refinitiv has not caught up yet.

 

In any case, I agree with doing your own calculations based on your own needs.  I, too, prefer to have control over the details.

 

PS.... In other forums, I have seen many complaints about the "Excel" (Refinitiv) stock data.  It does not sound very reliable -- at least, according to others.  I have no experience with it myself, obviously.