Video není dostupné.
Omlouváme se.
Using Excel to Create a Correlation Matrix || Correlation Matrix Excel
Vložit
- čas přidán 19. 08. 2024
- #excel #correlation
Please SUBSCRIBE:
www.youtube.co...
alphabench.com/...
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.
thanks for making a short and straight to the point video. This helped a ton.
Glad it helped
Exactly what I was looking for. Thank you
Glad it helped
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++💥
Thanks very much. Glad it helped
Thanks in a million. Where have you been all these years!
You are welcome. Glad it helped.
Helped me with university work, great simple video!
Glad it helped
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.
Glad it helped.
Thank you Matt. Great video we'll explained all around!
Thanks. Glad it helped.
Awsome man!! You saved my life
Thanks. Glad it helped.
Thanks, really came through for me.😎
Glad it helped.
start at 2:39
Glad it helped
I must thank you🙏🏼 for this great video.. helped me a lot for real 🙌🏼
Glad it helped
This video was very helpful, thanks!
Glad it helped
Many Thanks Matt. Further, how to know significance of the correlation
Thanks for your comment. Do you mean you would like to see a vid that covers hypothesis testing of correlations?
yeah for above type of multiple series of data..
Thank you so much..
Glad it helped
Thank you.
Glad it helped
huge help, thanks
Glad it helped!
Thank u, intrusting vedio
Glad it helped
Many thanks
Glad it helped
That was really helpful :)
Glad it helped
Amazing!
Glad it helped
THanks matt
Glad it helped
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)?
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
Do you have a video showing how to do this?@@MattMacarty
great video
Glad it helped
Thanks mate
Glad it helped
Hey Matt.... how did you selected the daily price data.. I mean is it in Ascending order or Descending order?
Sorry somehow I missed this question. The data is in ascending order (oldest observation first).
To compute the correlation matrix, mathematically, the order does not matter ;)
This is true. But depending on what you want to do next order can matter.
perfect
Glad it helped
what would be the highest correlation pair?
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.
Nice
Glad it helped
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?
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: czcams.com/video/DTiegJgxvlQ/video.html I think you would want to sample the data points since with a large sample pretty much anything will appear to be significant.
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!
Yes, to calculate correlation you need the same number of observations for each variable
@@MattMacarty what about the dates? Do they have to match too?
@@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.
@@MattMacarty thank u sir
How would I figure out the chronbach alpha with the information discovered from the correlation matrix
Here's a great reference: www.statisticshowto.com/probability-and-statistics/statistics-definitions/cronbachs-alpha-spss/
good
glad it helped
Hi There! It says “Correlation - Having Trouble to offset input/output reference” what will I do?
Are you trying to calculate an autocorrelation?
Sorry, Why using stock return instead of stock price?
This is trying to assess their tendency to move together directionally. Price won't really tell you much.
what is that data you have collected.is it periodic returns or excess returns.
These are "instantaneous" returns, close to close: LN(t/t-1)
where can I tern the Data Analyses on in 2020 on windows?
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
I don't understand. My teacher insists that the table need to be fully filled.
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.
@@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
@@karencuellar Sure I will try. Lots of software makes it easy to produce scatter matices too:
czcams.com/video/LoiVuDKxXBs/video.html
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.
Where is data from?
I think I used closing price data downloaded from Yahoo Finance.
@@MattMacarty so you calculated the daily return based on closing price?
@@googlerreviewer4368 Yes close-to-close LN(T/T-1)
@@MattMacarty I use for my excel =(Today's close - Yesterday's close)/Yesterday's close