Home

Helping find the Beta (Variance and Covariance) between sets of data based on date and category

%3CLINGO-SUB%20id%3D%22lingo-sub-718706%22%20slang%3D%22en-US%22%3EHelping%20find%20the%20Beta%20(Variance%20and%20Covariance)%20between%20sets%20of%20data%20based%20on%20date%20and%20category%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-718706%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20large%20data%20set%20which%20involves%20the%20return%20for%20a%20category%20as%20well%20as%20its%20benchmark.%20I%20wish%20to%20find%20the%20correlation%20or%20beta%20between%20the%20two.%20The%20given%20formula%20is%26nbsp%3BCOVARIANCE.P(stock%20return%2C%20index%20return)%2FVAR.P%20(Index%20Return)%2C%20however%20as%20i%20have%20large%20number%20of%20stocks%20(categories)%2C%20i%20also%20have%20mutiple%20benchmark%20returns%2C%20which%20apply%20to%20different%20stocks.%20Is%20there%20a%20formula%20which%20will%20select%20the%20data%20for%20this%20formula%20by%20matching%20the%20two%20categories.%20Eg%20a%20domestic%20share%20to%20the%20domestic%20share%20index.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20an%20example%20and%20looking%20for%20any%20suggestions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20kindly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-718706%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Frequent Contributor

Hi Everyone,

 

I have a large data set which involves the return for a category as well as its benchmark. I wish to find the correlation or beta between the two. The given formula is COVARIANCE.P(stock return, index return)/VAR.P (Index Return), however as i have large number of stocks (categories), i also have mutiple benchmark returns, which apply to different stocks. Is there a formula which will select the data for this formula by matching the two categories. Eg a domestic share to the domestic share index.

 

I have attached an example and looking for any suggestions.

 

Thank you kindly.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies