If you have any thoughts about minimum variance portfolios, or portfolio construction and management more broadly, I'd love to hear about those in the comments below
I found a book "The Sharpe Ratio Statistics and Applications" By Steven E. Pav it goes into more details on the subject than my mind can handle but I thought I reference it here as it may help you those who are interested with min variance portfolio.
never really worked with excel and never worked with share or the math behind, but was able to do all this for my own calculations. thanks alot man you saved my day!
@@summerspade5139 I'm doing mine on the exact some topic, would love to see yours because I'm kinda stuck for the best way to test lol. Rn I'm doing correlation of cryptos with each other and with traditional assets then I'm doing portfolio scenarios of equal weighting, min variance and not sure what else
hey ...I did exact all the steps you did with the exact same company and exact same time period, also I got all the values same untill i performed solver function, but after using solver it showed 0 in all the weight, please help
@@financemark Would you start with adjusted monthly close prices from Yahoo Finance then get returns using =LN(Month2/Month1)? I got returns using that method and then subtracted the risk free return of the 3 month T-Bill for the same period to get a real return. Is the next step to get the excess real return using the process that you mentioned in your video (Monthly Return / Average of Monthly Returns)?. To get the annualized real return I took the average of the monthly real return data *12 and then subtracted net expense ratio.
I'm not sure that I totally follow what you mean by using the index as a hedge (i.e., what exactly are you hedging against). Do you mean that you are trying to minimize tracking error? Or, That you are trying to obtain pure alpha by off-setting teh firm's beta?
@@financemark Yes, minimize the tracking error. Suppose a portfolio with n shares belonging to various sectors, indexes and countries (no fx risk), and you want to hedge the PnL exposure of that portfolio using the best possible index that minimizes the PnL volatility of your portfolio (i.e. you don't want to have +$10MM one month and -$10MM the next month). How would you go about finding the best index hedge? would you consider using the optimal hedge ratio or is there any other theory that can be used?
I'm having trouble pulling in data from Yahoo Finance! now :( I'm guessing they changed something on their end. Is there still a way to pull in the necessary data for this calculation in excel? I know excel has a built-in command for STOCKEXCHANGE but not sure if that is what we need.
Thanks for the video! Could you possibly tell me that how can I find standard deviation and sharp ration for the portfolio? Also, I was wondering if we can apply this method for portfolio with two assets?
Good question. This would be laborious and a bit annoying to do in excel (unless you have a macro). You would need to maximize the return for each individual set value of variance. One would then create a table and plot it. It would probably be easier in something like R or Stata where you could automate it with a for loop.
That sounds painful. I think it might be a matter of tediously constructing the portfolios by using solver multiple times (unless you write a macro to do it, that is)
first of all I appreciate your excellent explanation, I did it with 3 stocks for monthly returns, but at the end it gives me weight "1" for one of them. I double checked all of the parts and everything is correct.Is there any problem or it's usuall to have just 1 for a stock?
If you have any thoughts about minimum variance portfolios, or portfolio construction and management more broadly, I'd love to hear about those in the comments below
I found a book "The Sharpe Ratio
Statistics and Applications" By Steven E. Pav it goes into more details on the subject than my mind can handle but I thought I reference it here as it may help you those who are interested with min variance portfolio.
never really worked with excel and never worked with share or the math behind, but was able to do all this for my own calculations. thanks alot man you saved my day!
Thanks a lot! I'm happy it was helpful :)
you just saved me sir, thanks a lot, nothing could have helped me more than this!!!!!!!
You are the man, Mark
you've made my dissertation's econometric calculations x100 easy!!
Amazing! super happy that it helped!
Hey I'm doing a similar dissertation on portfolio optimization using cryptocurrencies. would love to know more about your dissertation.
@@summerspade5139 IG @mitrarishabh
@@summerspade5139 I'm doing mine on the exact some topic, would love to see yours because I'm kinda stuck for the best way to test lol. Rn I'm doing correlation of cryptos with each other and with traditional assets then I'm doing portfolio scenarios of equal weighting, min variance and not sure what else
you just saved my life with this video, I can never thank you enough sir!! ♥♥♥
I watch full 3 mins ads just to show support for his work in YT.
thank you so much! this is amazing, you are a saint
Thanks - I'm glad it was helpful!
It's very helpful, thank you so much
Thanks! I'm happy it was helpful
hey ...I did exact all the steps you did with the exact same company and exact same time period, also I got all the values same untill i performed solver function, but after using solver it showed 0 in all the weight, please help
5:05 Excess return is the (firms return - a risk-free rate), not minus the firm's average. But good video!
That's accurate. But, the reason you subtract the firm's average is because variance is the (1/n) Sum (x-ave(X))^2
@@financemark Would you start with adjusted monthly close prices from Yahoo Finance then get returns using =LN(Month2/Month1)? I got returns using that method and then subtracted the risk free return of the 3 month T-Bill for the same period to get a real return. Is the next step to get the excess real return using the process that you mentioned in your video (Monthly Return / Average of Monthly Returns)?. To get the annualized real return I took the average of the monthly real return data *12 and then subtracted net expense ratio.
COVARIANCE MATIRIX -DO WE NEED TO DIVIED BY N-1? NO OF OBSERVATIONS -1?
You are awesome 👌 wow 👏 amazing 👍
Thanks so much - I very much appreciate it
why does excel solver, despite everything exactly as you showed, always return zero and turns all my weights into zero
Can you use the same framework to optimize the Sharpe ratio?
Amazing Thank you
Thanks! Happy it was helpful
So would the standard deviation still be the same as in your other video of 2 assets? Just the sqrt of the minimum portfolio variance?
anybody has an idea how to do this same process but adding a risk free asset?
Thanks for your video. How would you minimize the P&L volatility of a portfolio using an index as a hedge?
I'm not sure that I totally follow what you mean by using the index as a hedge (i.e., what exactly are you hedging against). Do you mean that you are trying to minimize tracking error? Or, That you are trying to obtain pure alpha by off-setting teh firm's beta?
@@financemark Yes, minimize the tracking error. Suppose a portfolio with n shares belonging to various sectors, indexes and countries (no fx risk), and you want to hedge the PnL exposure of that portfolio using the best possible index that minimizes the PnL volatility of your portfolio (i.e. you don't want to have +$10MM one month and -$10MM the next month). How would you go about finding the best index hedge? would you consider using the optimal hedge ratio or is there any other theory that can be used?
I'm having trouble pulling in data from Yahoo Finance! now :( I'm guessing they changed something on their end. Is there still a way to pull in the necessary data for this calculation in excel? I know excel has a built-in command for STOCKEXCHANGE but not sure if that is what we need.
i know its too late, but you can try using python, R ou Power BI
Thanks for the video! Could you possibly tell me that how can I find standard deviation and sharp ration for the portfolio?
Also, I was wondering if we can apply this method for portfolio with two assets?
This video covers standard deviation and sharpe ratio optimisation
ruclips.net/video/OGhGz8trZtw/видео.html
How would you graph the minimum variance frontier using this information?
Good question. This would be laborious and a bit annoying to do in excel (unless you have a macro). You would need to maximize the return for each individual set value of variance. One would then create a table and plot it. It would probably be easier in something like R or Stata where you could automate it with a for loop.
@@financemark, Unfortunately, it's exactly what I have to do for my portfolio management project lol
That sounds painful. I think it might be a matter of tediously constructing the portfolios by using solver multiple times (unless you write a macro to do it, that is)
@@financemark I will look into it, thank you!
I remembered this to be much more complicated when my professor explained it lol
first of all I appreciate your excellent explanation, I did it with 3 stocks for monthly returns, but at the end it gives me weight "1" for one of them. I double checked all of the parts and everything is correct.Is there any problem or it's usuall to have just 1 for a stock?
Have the same problem. :(
@@julesepstone8679 i have this issue as well
what if I want select the portfolio as per my risk on efficient frontier how can I do that
I think you are looking or something like this: ruclips.net/video/3E5nE0fELnU/видео.html