Using Excel to Create a Correlation Matrix || Correlation Matrix Excel

#excel #correlation
Please SUBSCRIBE:
ua-cam.com/users/subscription_...
alphabench.com/data/excel-corr...
The spreadsheet in the is example can be downloaded by visiting the link above.
This tutorial demonstrates how to create a correlation matrix in Excel. The example used in the video is for stock price changes over a one year period. Data in its original form was downloaded from a third party such as Google Finance.

[@tonynguyen1536]

thanks for making a short and straight to the point video. This helped a ton.

[@MattMacarty]

Glad it helped

[@yedidyarabinowitz8488]

Thank you Matt. Great video we'll explained all around!

[@MattMacarty]

Thanks. Glad it helped.

[@connormcmullan2926]

Exactly what I was looking for. Thank you

[@MattMacarty]

Glad it helped

[@johng5295]

Thanks in a million. Where have you been all these years!

[@MattMacarty]

You are welcome. Glad it helped.

[@jessica386m]

This video was very helpful, thanks!

[@MattMacarty]

Glad it helped

[@exoticculture9363]

Awsome man!! You saved my life

[@MattMacarty]

Thanks. Glad it helped.

[@alexandercoates8331]

Helped me with university work, great simple video!

[@MattMacarty]

Glad it helped

[@johng5295]

Thanks in a million. Great content. Awesome. Very well explained. I couldn't find this explanation--simply put anywhere else. Great teachers are hard to find. Grade: A++💥

[@MattMacarty]

Thanks very much. Glad it helped

[@incikara3210]

I must thank you🙏🏼 for this great video.. helped me a lot for real 🙌🏼

[@MattMacarty]

Glad it helped

[@11am]

Thanks, really came through for me.😎

[@MattMacarty]

Glad it helped.

[@zubairahmed5816]

That was really helpful :)

[@MattMacarty]

Glad it helped

[@michaelmarkham2102]

huge help, thanks

[@MattMacarty]

Glad it helped!

[@edison5237]

Amazing!

[@MattMacarty]

Glad it helped

[@BailarinaCaotica]

Super, thanks! I was doing it on my Mac and it would show all kinds of problems and errors until I put the correlation matrix into the same worksheet as the original data was (just like shown in the video), in case that helps anyone.

[@MattMacarty]

Glad it helped.

[@shehzadiyounis5368]

Thank u, intrusting vedio

[@MattMacarty]

Glad it helped

[@Chamblee057]

Thank you.

[@MattMacarty]

Glad it helped

[@alidabbagh1581]

Thank you so much..

[@MattMacarty]

Glad it helped

[@rileypears9419]

Thanks mate

[@MattMacarty]

Glad it helped

[@dr.shekhartrivedi8267]

Many Thanks Matt. Further, how to know significance of the correlation

[@MattMacarty]

Thanks for your comment. Do you mean you would like to see a vid that covers hypothesis testing of correlations?

[@dr.shekhartrivedi8267]

yeah for above type of multiple series of data..

[@JusdoinstuF]

great video

[@MattMacarty]

Glad it helped

[@SanjeeSen]

Hi Matt, great video, really useful!
How do you represent the correlation of your entire portfolio in a single number? Would you just take the average of the correlations between each stock (assuming it's equally weighted)?

[@MattMacarty]

Thanks. You need to correlate it with something.... And if mounts around depending on your time frame it can fluctuate a lot day to day. I would just collect closing prices of the portfolio and then calculate correlation with something like S&P 500

[@hawley2685]

Do you have a video showing how to do this?@@MattMacarty

[@Web3Future333]

THanks matt

[@MattMacarty]

Glad it helped

[@thefaith01]

Many thanks

[@MattMacarty]

Glad it helped

[@dennisarrindell2244]

Great Video! Very useful! Is there a way that I can add 'the P-value (statistical significance' and the number (amount of cases N) in it too like SPSS produces?

[@MattMacarty]

Thanks. Glad it helped. Excel doesn't have those outputs directly, so you would have to implement them in the spreadsheet formulaically. Observations is pretty straightforward, you just use COUNT. Then you calculate the t statistic for the correlation. With a t stat in hand you could use the TDIST function to determine p-vlaue: ua-cam.com/video/DTiegJgxvlQ/v-deo.html I think you would want to sample the data points since with a large sample pretty much anything will appear to be significant.

[@friscianviales7519]

Hey Matt, thanks for the info!
Does the number of observations have to match for every asset? For example, having more daily returns on some assets or it has to do the same time length? Hope that makes sense and thanks in advance!

[@MattMacarty]

Yes, to calculate correlation you need the same number of observations for each variable

[@friscianviales7519]

@@MattMacarty what about the dates? Do they have to match too?

[@MattMacarty]

@@friscianviales7519 Not necessarily. You could randomly sample from each variable, but if you are trying to calculate something like correlation for stock price movement it would probably be best if you randomly choose dates and then get the correlation for the stocks on those dates.

[@friscianviales7519]

@@MattMacarty thank u sir

[@jakebean7464]

How would I figure out the chronbach alpha with the information discovered from the correlation matrix

[@MattMacarty]

Here's a great reference: www.statisticshowto.com/probability-and-statistics/statistics-definitions/cronbachs-alpha-spss/

[@joe009ize]

Hey Matt.... how did you selected the daily price data.. I mean is it in Ascending order or Descending order?

[@MattMacarty]

Sorry somehow I missed this question. The data is in ascending order (oldest observation first).

[@mjumper]

To compute the correlation matrix, mathematically, the order does not matter ;)

[@MattMacarty]

This is true. But depending on what you want to do next order can matter.

[@jeremypickup9400]

perfect

[@MattMacarty]

Glad it helped

[@ryanheffernan305]

what would be the highest correlation pair?

[@MattMacarty]

Values closer to 1 are higher positive correlations while this close to -1 are stronger negative relationships. In this example it looks like GOOG, AMZN and FB are fairly strongly correlated and I wouldn't call the differences seen in the example very meaningful.

[@naymashuvra3465]

Nice

[@MattMacarty]

Glad it helped

[@Matematica_Aplicada]

start at 2:39

[@MattMacarty]

Glad it helped

[@ankitsharma-lc7vq]

what is that data you have collected.is it periodic returns or excess returns.

[@MattMacarty]

These are "instantaneous" returns, close to close: LN(t/t-1)

[@countess8917]

Hi There! It says “Correlation - Having Trouble to offset input/output reference” what will I do?

[@MattMacarty]

Are you trying to calculate an autocorrelation?

[@kayacosar6994]

where can I tern the Data Analyses on in 2020 on windows?

[@MattMacarty]

In Windows hold Alt and type T then I, opens add-ins manager. Check Analysis Toolpak. In MAC go to Tools menu and select Excel Add-ins

[@damientchakoute2565]

good

[@MattMacarty]

glad it helped

[@geou9467]

Sorry, Why using stock return instead of stock price?

[@MattMacarty]

This is trying to assess their tendency to move together directionally. Price won't really tell you much.

[@mshoy84]

Where is data from?

[@MattMacarty]

I think I used closing price data downloaded from Yahoo Finance.

[@googlerreviewer4368]

@@MattMacarty so you calculated the daily return based on closing price?

[@MattMacarty]

@@googlerreviewer4368 Yes close-to-close LN(T/T-1)

[@googlerreviewer4368]

@@MattMacarty I use for my excel =(Today's close - Yesterday's close)/Yesterday's close

[@karencuellar]

I don't understand. My teacher insists that the table need to be fully filled.

[@MattMacarty]

He/She is not technically wrong, most software will generate a filled matrix, but having filled matrix doesn't make the matrix more correct or somehow better. Excel is really doing us a favor here by only filling in half of the matrix since the rest of the data is merely duplicated. Correlation doesn't care which variable is on the X vs. Y axes. However, if you produce scatter plots you will get different looking output depending on which variable you put on the X and Y axes. Maybe this is what they are referring to.

[@karencuellar]

@@MattMacarty yes! He does want scatters at the end of my whole task. Is there any way to contact you for regarding some questions? If I may ask. And thank you in advance

[@MattMacarty]

@@karencuellar Sure I will try. Lots of software makes it easy to produce scatter matices too:
ua-cam.com/video/LoiVuDKxXBs/v-deo.html

[@intredastingvideos9365]

There are lots of useful libraries in python or R, that can produce correlation matrices heatmaps, or scatterplot matrices. i think it's inbuilt in R, but R is a pain to learn. Using python, you can use the pandas library or the seaborn library and get some decent looking scatter graphs. Another alternative is to perform an anova on all of your different variable combinations and manually place each result into a matrix yourself. That way you can learn how to do it without all the automation.