Hi Fabian. Love this and really enjoying many of your tutorials. Thank you. Small top tip, trying to give back what little I can: Automatically name the columns by selecting the range (including the labels) > Control+Shift+F3 > Top Rows. ;-) Thanks again - great channel!
I am a non finance guy and was trying to build a portfolio optimiser all by myself - this is easily the best video on this subject on YT for folks like me. What will change if I try to use daily returns instead of monthly returns?I am using data for the past 5 years to build a 20 stock portfolio.
I think everything will remain same except you need to use 252 instead of 12 to annualise the Return and Risk variables. Assuming 252 are the trading days in a year. Of course, Fabian can correct me here if it is not true what I said.
Just brilliant! A huge thanks, some much knowledge transmitted in so little time :-) straight to the point! You show a good way of understanding how we can manage our portfolios efficiently.
Super like. Thats really great and clear. Would you mind also putting a vidoe on how hedge fund managers decides when to sell a position and replace it with another one using the same mechanism in this video ? Thanks
@@FabianMoa has there been a part 2 for this? Would love to figure out how to align this data into potential scatter plots... like an alternative efficient frontier
Thank you for this informative but also very simple to understand video. Would it be possible to make a video regarding GARCH model for analyzing and predicting the volatility?
Hi Sir, I don't get it the rationale behind the formula @ 6:45 for the variance part. It is different from the normal 2 stock variance formula that we normally used?
@@FabianMoa thanks for your reply sir. Actually I don't quite get the matrix part (my math not good), may I know where can I further study/research to get the rationale behind the matrix implementation part? What topic should I search? 🙏 Appreciate your guidance
Great Tutorial on portfolio optimisation. I am just wondering why you multiplied monthly return and monthly Std deviation with 12 to annualise them? Okay, there are 12 months in a year so it makes sense. However, in many other videos of other experts, they multiply with 252 when they have daily returns data, not with 360 because there are only 252 trading days in a year approximately. In monthly returns data case, multiplying with 12 means you are compounding returns over weekends and holidays as well. Should not it be any lower number, let's say 8 or 8.4, rather than 12 to account for non-trading period in a year? Sorry for this lengthy comment but it requires clarity. What is the consensus of academicians and practitioners on this matter? Thank you for producing high quality content on the UA-cam Fabian.
The mean and variance of returns are i.i.d., so the means and variances can be summed up respectively, which is seen as the effect of multiplication. So if a weekly return (that consists of 5 trading days) is 2% per week, we assume that based on i.i.d.: Week 1 return = 2% Week 2 return = 2% Week 3 return = 2% Week 4 return = 2% So a monthly mean return = 2% + 2% + 2% + 2% = 4 × 2% = 8% The same concept applies to variance. And standard deviation is multiplied by square root of time.
@@FabianMoa That's great explanation, Thank you. One further query regarding this tutorial. Using Indirect function to calculate var-cov matrix is efficient, that's wonderful. Could you please explain why you first locked the Row only and second time you locked the Column only within the formula of Covariance calculation using Indirect function? Why locking is important here and why row in the first instance and column the second time within the same formula?
Thank you, Fabian. Lets say I have the returns in daily format, how do I annualize the variance and std deviation? If the daily data is for example 252 days per trading year.
I have 1,245 ETF files using Cummulative Abnormal Return and BEhavioral ETFS? I got all these ETFs from the New York Stock Exchange and I am using the New York Composite Index as my market index. I need to know the most optimal portfolio using 1245 ETF lists.
Great video. Can you show you to calculate the month return using the daily price? I can't find the monthly return so I have to use daily price for it. Thanks!
You can work with daily prices. Use it to calculate daily returns. To annualize the daily returns, multiply by 250 or 252 (based on number of trading days in the year). To annualize daily variance, multiply by square root of 250 or 252.
@@FabianMoa I found the option to download the historical price by month. The data is sorted in asc order, so calc the return by (month / previous month) -1. And the numbers come out close, but not exactly like you have on your sample data. Is that because your data took dividend and stock split into consideration?
Sir, how did you derive the return data? as an example I checked AAPL 12/01/2018 Price of $39.44, 01/01/2019 price of $41.61. So LN monthly return would be 5.36% but on your data table shows 1.86%. what am I missing here?
Great video. I have seen others about var-covar and solver. This one was very clear. I have daily returns that are negative (0.4, -0.2, for example) and you cannot have a negative log: =ln(.4/-2). What do you do in this case?
Hi Stephen, the LN() is applied to the ratio of prices, not on returns. So if you have the daily returns, you can proceed to generate the covariance matrix
Can you please explain the meaning of the Std Dev that you get for the portofolio? Why in order to have it you multiply the weights with the covariance matrix? I'm struggling to understand why is it still called std dev... Moreover, if you calculate the std dev of every single asset, you can't compare it with the portfolio's std. dev, because values are too different. Please let me know
Regarding to the Risk Free Rate, I have a 5-year data of asset classes and the US 5 year treasuary note annual data, do I just make an assumption as you said in the video or use the average 5-year annual rate or the annual rate for the 2020 calendar year? Thanks in advance, Fabian.
If you want to include short positions, then do not set a constraint in Solver. If you want to short a specific stock, you can set the constraint in Solver with e negative weight
Hi could you show a quick example of how you calculated the returns using the continuously compounded formula? I've tried following your example for cell A2 (AAPL 1/01/2015) and I got ln(118.05/111.39) = 0.05807 = 5.8% and not 9.208% like you got. Thank you.
As a rule of thumb in as much as the number of observations or the returns in this case is at least ten times the number of stocks. Though more computing power is required.
Hi! A quick question. What's the difference between the Markowitz portfolio optimization and risk parity portfolio optimization? Is it the same process in Excel?
For risk parity, we ignore expected return. The fund manager will have a target standard deviation for the portfolio, and each asset class will contribute the same amount to total portfolio volatility. For example, if you take target a portfolio volatility of 20% and you have 5 asset classes, then the optimizer will select the asset class weights such that the contribution of each asset class to the overall portfolio volatility is 4%. For Markowitz, we look at the mean return and variance/std deviation.
@@FabianMoa so risk parity considers just risk, while Markowitz considers risk and also reward? Said like that, Markowitz seems to be much better? Is that correct?
" so risk parity considers just risk, while Markowitz considers risk and also reward?" Yes "Markowitz seems to be much better? Is that correct?" Not really. It depends on investors' objectives. Some wants to minimize risk, some want to maximize risk-adjusted return, etc. The models are chosen based on investor's objectives and concerns/preferences
yes sir, i name my cell, APPLE, BA, NFLX, TSLA. Then entered equal sign>covariance>indirect>APPLE(f4).indirect>APPLE(F4))>Enter . Then my Average returns are:0.015626, 0.018168, 0.027699, 0.083233 respectively i use LN function to generate my return, the same data from Yahoo finance, 1/1/ 2015 to 1/12/2019(monthly) my return for TSLA is completely different to yours
Akinola, I'm sharing the source file to you for the 4 stocks. Can you check if the returns computed in my files are the same as the ones you have? Link: drive.google.com/open?id=1XXnchyeibkNFrBJSLop2lVanOVKZ-fvz A few things to check on your side is, are the prices sorted according to the dates in ascending order (i.e. oldest to newest). And I'm calculating based on Adjusted Closing Price. Let me know.
@@akinolaolatubosun4120 did you use the ticker AAPL for Apple when you called the data? Also, did you use this as your reference in the spreadsheet? Instead of spelling out Apple, just use the ticker.
@@FabianMoa How about if you have a weekly dataset over 15 years. I compute the average returns for the data per year, but also for over the full set of 15 years per week. The yearly per year averages I can compute by doing *52 assuming IID. Can I do the same for the average over the full dataset? Or do i have to muliply that weekly everage by 52*15? Answer 1: FULL DATA SET AV R*52 Answer 2: FULL DATA SET AV R*52*15 Thanks!
I am indeed thankful to Prof. Fabian with such knowledgeable and hands-on session in such an easy manner. Thanks and keep the great work going on!
Hi Fabian. Love this and really enjoying many of your tutorials. Thank you. Small top tip, trying to give back what little I can: Automatically name the columns by selecting the range (including the labels) > Control+Shift+F3 > Top Rows. ;-) Thanks again - great channel!
Great tip! Will try it out
While reading for Corporate Finance for my MBA, this video cleared many doubts of mine.
Thank you so much for sharing this video!
Glad it was helpful, Ankur!
Great and efficient explanation! Was looking for this.
Lifesaver! Ver clear - thanks Fabian!
Thank you for making it perfectly clear! Not everybody is able to do that.
You're welcome!
I am a non finance guy and was trying to build a portfolio optimiser all by myself - this is easily the best video on this subject on YT for folks like me. What will change if I try to use daily returns instead of monthly returns?I am using data for the past 5 years to build a 20 stock portfolio.
I think everything will remain same except you need to use 252 instead of 12 to annualise the Return and Risk variables. Assuming 252 are the trading days in a year. Of course, Fabian can correct me here if it is not true what I said.
Just brilliant! A huge thanks, some much knowledge transmitted in so little time :-) straight to the point! You show a good way of understanding how we can manage our portfolios efficiently.
Thanks, Perceval. Glad you found it useful
Excellent tutorial. Thank you very much!
Super like. Thats really great and clear. Would you mind also putting a vidoe on how hedge fund managers decides when to sell a position and replace it with another one using the same mechanism in this video ? Thanks
Great question, hope we can get answer of this.
Please, can you expatiate further on the continuously compounding formula bit? Do you have another video showing that in detail?
Good
You can also add CML to this along with charts for clarity EF
Thanks for the input, Vivek. That would be for a Part 2 video
@@FabianMoa great look forward for 2nd part
@@FabianMoa has there been a part 2 for this? Would love to figure out how to align this data into potential scatter plots... like an alternative efficient frontier
🙏 Thanks for the great tutorial 👍
Glad it was helpful!
Thank you for this informative but also very simple to understand video. Would it be possible to make a video regarding GARCH model for analyzing and predicting the volatility?
Hi Sir, I don't get it the rationale behind the formula @ 6:45 for the variance part. It is different from the normal 2 stock variance formula that we normally used?
It's similar but the implementation in Excel is based on matrix algebra, which is more efficient.
@@FabianMoa thanks for your reply sir. Actually I don't quite get the matrix part (my math not good), may I know where can I further study/research to get the rationale behind the matrix implementation part? What topic should I search? 🙏 Appreciate your guidance
Going to apply it in TASI market.
Great Tutorial on portfolio optimisation. I am just wondering why you multiplied monthly return and monthly Std deviation with 12 to annualise them? Okay, there are 12 months in a year so it makes sense. However, in many other videos of other experts, they multiply with 252 when they have daily returns data, not with 360 because there are only 252 trading days in a year approximately. In monthly returns data case, multiplying with 12 means you are compounding returns over weekends and holidays as well. Should not it be any lower number, let's say 8 or 8.4, rather than 12 to account for non-trading period in a year? Sorry for this lengthy comment but it requires clarity. What is the consensus of academicians and practitioners on this matter? Thank you for producing high quality content on the UA-cam Fabian.
The mean and variance of returns are i.i.d., so the means and variances can be summed up respectively, which is seen as the effect of multiplication.
So if a weekly return (that consists of 5 trading days) is 2% per week, we assume that based on i.i.d.:
Week 1 return = 2%
Week 2 return = 2%
Week 3 return = 2%
Week 4 return = 2%
So a monthly mean return
= 2% + 2% + 2% + 2%
= 4 × 2%
= 8%
The same concept applies to variance.
And standard deviation is multiplied by square root of time.
@@FabianMoa That's great explanation, Thank you. One further query regarding this tutorial. Using Indirect function to calculate var-cov matrix is efficient, that's wonderful. Could you please explain why you first locked the Row only and second time you locked the Column only within the formula of Covariance calculation using Indirect function? Why locking is important here and why row in the first instance and column the second time within the same formula?
Hey cool excel …. I did this staff in the late 90 :)👍 do you know if there is an app we can use on Mac or iOS ?
Great video, fair play
Thank you, Fabian. Lets say I have the returns in daily format, how do I annualize the variance and std deviation? If the daily data is for example 252 days per trading year.
For daily variance, multiply by 252. For daily standard deviation, multiply by square root of 252
Very good, but i think it would be better if you could speaker slowly, considering some people are not good at excel, we want to follow the steps
Great video. How would you integrate dividend yield + compound interest from that into this optimization problem in terms of total/expected returns?
Great video
I have 1,245 ETF files using Cummulative Abnormal Return and BEhavioral ETFS? I got all these ETFs from the New York Stock Exchange and I am using the New York Composite Index as my market index. I need to know the most optimal portfolio using 1245 ETF lists.
Great video. Can you show you to calculate the month return using the daily price? I can't find the monthly return so I have to use daily price for it. Thanks!
You can work with daily prices. Use it to calculate daily returns. To annualize the daily returns, multiply by 250 or 252 (based on number of trading days in the year). To annualize daily variance, multiply by square root of 250 or 252.
@@FabianMoa I found the option to download the historical price by month. The data is sorted in asc order, so calc the return by (month / previous month) -1. And the numbers come out close, but not exactly like you have on your sample data. Is that because your data took dividend and stock split into consideration?
@@jonathant1797 I might be wrong, but he took LN return. would appreciate if he could clarify it for us.
Which way we import this data to excel?
Sir, how did you derive the return data? as an example I checked AAPL 12/01/2018 Price of $39.44, 01/01/2019 price of $41.61. So LN monthly return would be 5.36% but on your data table shows 1.86%. what am I missing here?
Great video. I have seen others about var-covar and solver. This one was very clear. I have daily returns that are negative (0.4, -0.2, for example) and you cannot have a negative log: =ln(.4/-2). What do you do in this case?
Hi Stephen, the LN() is applied to the ratio of prices, not on returns. So if you have the daily returns, you can proceed to generate the covariance matrix
Can you please explain the meaning of the Std Dev that you get for the portofolio? Why in order to have it you multiply the weights with the covariance matrix? I'm struggling to understand why is it still called std dev...
Moreover, if you calculate the std dev of every single asset, you can't compare it with the portfolio's std. dev, because values are too different. Please let me know
i’m 1 year late but it’s just the formula, portfolio risk formula. standard deviation just reflects risk levels
Regarding to the Risk Free Rate, I have a 5-year data of asset classes and the US 5 year treasuary note annual data, do I just make an assumption as you said in the video or use the average 5-year annual rate or the annual rate for the 2020 calendar year? Thanks in advance, Fabian.
The risk-free rate can be current risk-free rate (at the point of running the MVO)
If I both long and short, how do I calculate portfolio optimization? Thanks!
If you want to include short positions, then do not set a constraint in Solver.
If you want to short a specific stock, you can set the constraint in Solver with e negative weight
Thank you very much
Hi could you show a quick example of how you calculated the returns using the continuously compounded formula? I've tried following your example for cell A2 (AAPL 1/01/2015) and I got ln(118.05/111.39) = 0.05807 = 5.8% and not 9.208% like you got. Thank you.
I figured it out - it's the adjusted close price on Yahoo for anyone else wondering.
@@kierancook3397 Good catch! I was wondering this myself but may have missed the obvious.
Hi, How can we optamize for a big portfolio. Say Portfolio with more than 40 stocks
Better to do in Python/R/VBA
@@FabianMoa I have learned basic R from my current university program. Should I take a course including Python and Data Analysis?
This is probably the best tutorial for portfolio optimization. Can I used this on a 10 stock portfolio? Thank you.
Yes, you can
As a rule of thumb in as much as the number of observations or the returns in this case is at least ten times the number of stocks. Though more computing power is required.
why the covariance between the stocks with itself it´s not equal to 1?
Covariance between stock A with itself is the variance of stock A.
Correlation between stock A with itself is 1.
@@FabianMoa you are right, I confuse the terms. Thanks for your fast answer, new suscriber!
Hi! A quick question. What's the difference between the Markowitz portfolio optimization and risk parity portfolio optimization? Is it the same process in Excel?
For risk parity, we ignore expected return. The fund manager will have a target standard deviation for the portfolio, and each asset class will contribute the same amount to total portfolio volatility.
For example, if you take target a portfolio volatility of 20% and you have 5 asset classes, then the optimizer will select the asset class weights such that the contribution of each asset class to the overall portfolio volatility is 4%.
For Markowitz, we look at the mean return and variance/std deviation.
@@FabianMoa so risk parity considers just risk, while Markowitz considers risk and also reward? Said like that, Markowitz seems to be much better? Is that correct?
" so risk parity considers just risk, while Markowitz considers risk and also reward?"
Yes
"Markowitz seems to be much better? Is that correct?"
Not really. It depends on investors' objectives. Some wants to minimize risk, some want to maximize risk-adjusted return, etc. The models are chosen based on investor's objectives and concerns/preferences
Video is blurr, cant see excel calculation
You can increase the video resolution to 720p/1080p
Pls i am unable to solve variance covariance through this method , pls i need your help
Did you follow all the steps shown in the video?
yes sir, i name my cell, APPLE, BA, NFLX, TSLA. Then entered equal sign>covariance>indirect>APPLE(f4).indirect>APPLE(F4))>Enter .
Then my Average returns are:0.015626, 0.018168, 0.027699, 0.083233 respectively
i use LN function to generate my return, the same data from Yahoo finance, 1/1/ 2015 to 1/12/2019(monthly) my return for TSLA is completely different to yours
note: > means then, i like the method, the most simple method online. thanks
Akinola, I'm sharing the source file to you for the 4 stocks. Can you check if the returns computed in my files are the same as the ones you have? Link: drive.google.com/open?id=1XXnchyeibkNFrBJSLop2lVanOVKZ-fvz
A few things to check on your side is, are the prices sorted according to the dates in ascending order (i.e. oldest to newest). And I'm calculating based on Adjusted Closing Price.
Let me know.
@@akinolaolatubosun4120 did you use the ticker AAPL for Apple when you called the data? Also, did you use this as your reference in the spreadsheet? Instead of spelling out Apple, just use the ticker.
Thankssssssssssss. Life saver
You can't do *12 for the yearly returns right? You have to do =((L15+1)^52)-1, right?
We can *12 if the returns are assumed to be i.i.d. (independent and identically distributed)
And ^52 is for weekly returns, not monthly returns
Yes I just read a paper written by Lo
@@FabianMoa How about if you have a weekly dataset over 15 years.
I compute the average returns for the data per year, but also for over the full set of 15 years per week.
The yearly per year averages I can compute by doing *52 assuming IID.
Can I do the same for the average over the full dataset? Or do i have to muliply that weekly everage by 52*15?
Answer 1: FULL DATA SET AV R*52
Answer 2: FULL DATA SET AV R*52*15
Thanks!
Answer 1 will do
@@FabianMoa Thanks a lot! I thought the same!
Have a nice day sir!
Thanks
Why does it recommend me to always put 100% of my portfolio in a single stock?
Probably the return is much higher than the rest of the stocks, relative to the standard deviation
is this markowitz?
Yes, it's based on Markowitz's modern portfolio theory
Subscribed
Perfect!
Thanks, Joao!
standard deviation is not a percentage
wait... this is the standard deviation of a percentage.