💾 Download Free Excel File: ► Grab the file from this video here: ryanoconnellfinance.com/product/optimal-portfolio-calculation-excel-template/ 👨💼 Freelance Financial Modeling Services: ► Custom financial modeling solutions tailored for your needs: ryanoconnellfinance.com/freelance-finance-services/
@maximehardy36542 ай бұрын
Omg, jsut mind blowed that I didnt discover that earlier.
@RyanOConnellCFAАй бұрын
Glad you found it now!
@williamrivera1622 жыл бұрын
Hi Ryan. Finally, I was able to download the file. Thank you so much, for writing.
@RyanOConnellCFA2 жыл бұрын
You're welcome!
@aniruddhaganguly5699Ай бұрын
Thank you brother Ryan for this wonderful video . Please upload a video of Treynor ratio explaining its advantages
@RyanOConnellCFAАй бұрын
My pleasure! I have a video where I briefly discuss Treynor here from many years ago! I can make a newer version though in the future: kzfaq.info/get/bejne/pdCdYKeg2bvGaWg.html
@aniruddhaganguly5699Ай бұрын
Yes brother. I read it
@aniruddhaganguly5699Ай бұрын
@@RyanOConnellCFA Thank you brother
@aniruddhaganguly5699Ай бұрын
Welcome
@RyanOConnellCFAАй бұрын
@@aniruddhaganguly5699 My pleasure, and glad it helped!
@evangelineolaer84 ай бұрын
Interesting subject. It helps a lot. Thanks
@mohamedmadkour69268 ай бұрын
Thank you so much Ryan, much appreciated
@RyanOConnellCFA8 ай бұрын
My pleasure!
@ceyhunozdemir60573 ай бұрын
Very useful... Thanks
@RyanOConnellCFA3 ай бұрын
My pleasure!
@Bubblegun143 жыл бұрын
This is very fun! Thank you, I hope you keep sharing more content.
@RyanOConnellCFA3 жыл бұрын
I'm glad you enjoyed it Michael! I appreciate the feedback and have no plans of stopping haha
@KalyanisMusicandMelody3 ай бұрын
Thank you!🙏
@RyanOConnellCFA3 ай бұрын
My pleasure!
@kr1tical414 ай бұрын
Great video! You have made a small error in calculating the variance-covariance matrix. Instead of dividing through by N-1, you divided by N. Only managed to spot this while working through the theory :)
@leandrobaptista Жыл бұрын
OMG! This is amazing. Thank you so much!
@RyanOConnellCFA Жыл бұрын
You're so welcome!
@saharabdullah482 жыл бұрын
Very informative ! Thank you
@RyanOConnellCFA2 жыл бұрын
My pleasure!
@yi-news2 жыл бұрын
wonderful lesson,thanks
@RyanOConnellCFA2 жыл бұрын
You are welcome!
@johncarroll6910 Жыл бұрын
Thank you. Great job!
@RyanOConnellCFA Жыл бұрын
Thank you too John!
@sunnypj18922 жыл бұрын
Great videos. Thanks!
@RyanOConnellCFA2 жыл бұрын
Thanks for watching!
@patrickkevin70642 ай бұрын
Very helpful
@RoniCleveland112210 ай бұрын
Hi Ryan, great video. What is impact of covariance matrix and how to understand covariance matrix ?
@RyanOConnellCFA10 ай бұрын
Hi there! Thank you for your comment. The covariance matrix represents the relationship between different assets in a portfolio, capturing how they move relative to each other. Understanding it is crucial because it helps in assessing the combined risk of the portfolio and optimizing asset allocation for the best risk-reward trade-off.
@Persian5life2 ай бұрын
Ryan, what is the advantage of using Daily Data over Monthly Data?
@ucao41975 ай бұрын
How to arrive at 35% and 15%? Is there a reason behind this? Or we can choose the minimum and maximum weight for each constituent weight to our liking?
@knayak6062Ай бұрын
Yes. You can change them. It is the strategic decision.
@sairajshindexie7253Ай бұрын
How do we take max weight and min weights?
@octavearmand46792 ай бұрын
Hi I dont understand how you found the st dev and exp return for each asset, can you please explain
@RyanOConnellCFA2 ай бұрын
You can get more precise and scientific in this step by following my 5 minute video on this topic here: kzfaq.info/get/bejne/a5xzmql-1Me1hqM.html
@TheRocketmaster62 жыл бұрын
lifesaver! thanks :)
@RyanOConnellCFA2 жыл бұрын
You're welcome!
@murtalaa9 ай бұрын
Hi, could you do a video on optimal portfolio optimisation with value at risk constraint
@RyanOConnellCFA8 ай бұрын
I can look into this in the future!
@Ghaith77026 ай бұрын
Nice video the real question is how to find the stocks that maximize the sharp ratio if only solver can do that . or can it ?
@RyanOConnellCFA6 ай бұрын
Hello! I have a newer and more sophisticated video on Portfolio Optimization here: kzfaq.info/get/bejne/jreDZJqK18Dadac.html Please let me know if that helps you answer your question. It also uses the solver as portfolio optimization is an iterative math problem
@maksymnikulyak25805 ай бұрын
Hi, Ryan, thanks a lot for your very detailed solution! The only thing, I'm a bit confused with the behavior of the Expected Return, in cell C12, as it has actually *decreased* from 6.5% prior to the optimization, down to 6.3% thereafter. Please, if you could take a look and clarify?..
@user-is1xg7lq3k10 ай бұрын
I like this youtube, can you tell be why you inserted the number 252 at the end of the standard deviation formula? Thank you
@RyanOConnellCFA10 ай бұрын
252 is the default number of trading days in the year after removing weekends and holidays when the market isnt open. This number is used to annualize returns and standard deviations
@esraalpcoskun5936 Жыл бұрын
Hi Ryan, Thank you for the videos. Is there any video about CCAR and DFAST stress testing?
@RyanOConnellCFA Жыл бұрын
Hello Esra, thank you! I don't have any videos on those topics yet but I can certainly look into them in the future
@evangelineolaer84 ай бұрын
What if the constraint is a 15% standard deviation? Can you please advise? Thank you.
@alejadroigoyanes Жыл бұрын
u should put the standard deviation formula in the video description
@user-wh5zi2io7u Жыл бұрын
Hey, Ryan! Thanks for the interesting and well-explained video. Which gold (asset) did you use for this video and where is it being traded on?
@RyanOConnellCFA Жыл бұрын
It's my pleasure! I used the GLD ETF which tracks the price of gold. Basically, you are buying the rights to physical gold that is stored for you (typically an expense fee of 0.5% per year). It trades on the major stock exchanges like NYSE
@nickheyburn9 ай бұрын
Hello, In my own data set when I try to use the covariance function, excel keeps blocking it because it says my input range contains non-numeric data. I don't see how that could be true because my data only consists of percentages after doing the steps you demonstrated in this video. Do you know what I could be doing wrong? Your videos are super helpful thank you!
@RyanOConnellCFA9 ай бұрын
In the column next to your returns, use the formula =isnumber() and inside that function reference the cell that has the return. Drag it all the way down for each stock. Find the cell(s) where it says FALSE and you'll have your non-numeric data. Thanks!
@hansen_songАй бұрын
How do you determine what the min and max weights are for each security? Also if I wanted to include risk-free assets like US treasuries into the portfolio, would this still work?
@hybriddude00729 күн бұрын
min weight is how much you want to be invested in a certain stock, the lower it is the better for optimization
@saidakbarkhasanov83702 жыл бұрын
Hey, Ryan. Thank you for walking through the steps. Very clearly and well explained. 1) How can I set it to calculate optimal portfolio if going short is allowed too? 2) I can't find nor downloadable link neither link to your website where I can download file. Is there a chance that you post one of those in the description to video? Thanks.
@RyanOConnellCFA2 жыл бұрын
Thank you Saidakbar! 1. I'm not entirely certain on how you would do that. 2. I re-posted the file with a Github link (in the description). Please try that and let me know if it works better for you
@saidakbarkhasanov83702 жыл бұрын
@@RyanOConnellCFA Thank you very much, it worked.
@RyanOConnellCFA2 жыл бұрын
@@saidakbarkhasanov8370 You're welcome!
@crazyKiLLAKid29D2 жыл бұрын
How do you go about finding the St Dev (Risk) values? Overall great video!
@RyanOConnellCFA2 жыл бұрын
Thank you! Excel has an inbuilt standerd deviation formula. You just input the range of data into that formula
@tagtrenton8659 Жыл бұрын
@@RyanOConnellCFA Ryan, beautiful video. Easier to follow and comprehend than professor instruction. I have a question regarding the standard deviations you can obtain from the covariance matrix. By going diagonally from the top left to bottom right of the covariance matrix and taking the square root of each output, you can calculate the standard deviation of that factor. For instance, if you take the square root of the output from SP500 and SP500 you’ll obtain the SP500 input’s standard deviation. How is this standard deviation different from say using the standard deviation function on the SP500 historical data used in the sheet?
@RyanOConnellCFA Жыл бұрын
@@tagtrenton8659 Hey there! Great question! The standard deviation you obtain by taking the square root of the diagonal elements in the covariance matrix is actually the same as the one calculated using the standard deviation function (STDEV or STDEV.P) on the historical data. In the covariance matrix, the diagonal elements represent the variance of each individual asset, and taking the square root of the variance gives you the standard deviation. Both methods should yield the same results, and any minor discrepancies could be due to rounding or calculation differences in Excel. So, you can use either approach to calculate the standard deviation for each asset in your portfolio.
@tagtrenton8659 Жыл бұрын
@@RyanOConnellCFA Thank you for the fast reply! I was getting minor discrepancies like you said and could not figure out why. Finally, I’m at peace lol. Thanks again!
@RyanOConnellCFA Жыл бұрын
@@tagtrenton8659 Awesome, glad you could figure it out!
@tommypain Жыл бұрын
Ryan, just surfing around and stumbled on you and this video. Very interesting. Thank you. I'll play with it some more. BTW, I see there is some bad data at the bottom of your Raw Data tab on Row 253. Is this causing some wild error in the calculations? Just curious. Hey, I'm just down the road in Arlington.
@RyanOConnellCFA Жыл бұрын
Hey Tommy, great to hear from someone so close by! What do you mean by bad data in row 253? All the data looks fine to me. The returns (which our analysis is based on) stop in row 252
@tommypain Жыл бұрын
@@RyanOConnellCFA Hmmm, watching your video again, I don't see it. I think when I grabbed the data (using your template) for some other tickers, it had one extra row (Row 253) and that's where the extra line of "bad data" was. Disregard.
@RyanOConnellCFA Жыл бұрын
@@tommypain No problem Tommy! Also, I just released a more updated portfolio optimization video in excel here if you are interested: kzfaq.info/get/bejne/jreDZJqK18Dadac.html
@tommypain Жыл бұрын
@@RyanOConnellCFA Thanks, Ryan. I'll check it out.
@jakekennedy67182 жыл бұрын
Thanks for this. Really stupid question here but what hotkeys allow you to copy cells? I'm an excel luddite so I am just click and dragging the bottom right.
@RyanOConnellCFA2 жыл бұрын
We all start that way Jake! Ctrl + C allows you to copy Then go to the last column that has values, you hit Ctrl + down arrow to go to the bottom row with values Then you can go to the empty column where want to paste the values and hold Ctrl + Shift + up arrow. This will highlight the whole range. Then Ctrl + V to paste over the whole range
@davidardevolduran398 Жыл бұрын
Why do you only use historical data from the last year and not over a period of, say, 10 years?
@RyanOConnellCFA Жыл бұрын
You could definitely use 10 years worth of data and that would likely make your results more accurate and reflective of the true covariances
@noldy903 жыл бұрын
NIce...
@charliegriffin34912 жыл бұрын
Ryan - thanks for the video. Very simple explanation so appreciated that. In terms of assigning an expected return to a stock, would it be useful to use an analyst price target (and subsequent return) as the expected return? Also, do you have a parameter to determine max and min weights. It seems simple with a portfolio of 4 or 5 stocks but with say 16 I cannot really determine what a max or min should be? appreciate your help. Thanks
@RyanOConnellCFA2 жыл бұрын
Charlie, the consensus (or median) analyst expectation would probably be your best bet for expected return. As for min weights, its hard to say, I haven't done enough research on that topic
@sandmantheendless77803 жыл бұрын
Could you post the st. dev formula cell? I am having trouble access it
@RyanOConnellCFA3 жыл бұрын
Here is the formula exactly as it was in the video: =SQRT(MMULT(MMULT(TRANSPOSE(G5:G8),J5:M8),G5:G8*252))
@sandmantheendless77803 жыл бұрын
@@RyanOConnellCFA Thank you! I really appreciate your videos.
@donahuco2 жыл бұрын
@@RyanOConnellCFA Why "*252"?
@gheorghebarbulescu27872 жыл бұрын
@@donahuco 252 represents the average number of trading days in a year
@mcpeinventors60943 жыл бұрын
How exactly does the matrix multiplication work
@RyanOConnellCFA3 жыл бұрын
This is my favorite walk through of how this formula works: www.wallstreetmojo.com/mmult-in-excel/
@williamrivera1622 жыл бұрын
Ryan, very good video and explanation. I trying to download the file, but this message appear on the screen; You're seeing this error because you have DEBUG = True in your Django settings file. Change that to False, and Django will display a standard 404 page. I don't know if you can formatted the file in other form. Thank you
@RyanOConnellCFA2 жыл бұрын
Please try now William, I have added a Github link to the description
@GabrielaSanchez-qn9my2 жыл бұрын
@@RyanOConnellCFA Btw, I was wondering if you could share a link that is only uploaded to Google Drive? The direct download doesn't work for me because since I don't have the Excel program downloaded on my computer (only Excel online), it just downloads it directly onto "Pages" (i'm a macbook user) and the formatting comes out wrong. Thanks!
@eldersprig Жыл бұрын
isn't expected return of gold = zero?
@RyanOConnellCFA Жыл бұрын
This may be true if you consider real returns (accounting for inflation), but most returns are quoted on a nominal basis (not adjusted for inflation). A rule of thumb is that the return on gold should be similar to the inflation rate
@neilhanks Жыл бұрын
Why you didn’t use adjusted close?
@RyanOConnellCFA Жыл бұрын
In hindsight, adjusted close would have been better to use