How to plot efficient frontier from sets of data containing expected return, average return, variance, standard deviation, covariance and correlation ? Subscribe for Weekly Excel Tips and TricksHelpful tutorials delivered to your email!Similar Topics
I want to add data points to the scatter plot that are not part of the data ie. add points to find their concentration on a standard curve. Hey everyone, I'm really hoping someone can help me with this.. I need to plot percentages over time in a line graph in excel. I don't want to have to do a percentage equation in the spreadsheet, I just want excel to take two sets of values and display the percentage in the chart. For example, I need B1 as a percentage of B2 for week 1, C1 as a percentage of C2 for week 2, etc.. Can someone please offer a suggestion for how to do this? I would really appreciate it. Also, would it be possible to link data from other sheets in the workbook into one single chart?
Hello All I wonder if you could help with this one, I have a list of data like this (no spaces or other characters) JN551122B What I need to do is split each of the characters in to its own cell if JN551122B was in cell A1 I'd want to return: B2 C2 D2 E2 F2 G2 H2 I2 J2 J N 5 5 1 1 2 2 B The nearest I have got is the right function, but it brings over the preceeding characters, and I need each character in its own cell. Hopefully I could copy/paste a formula and change the reference for each cell value, i.e. 1 would return J, 2 = N, 3 = 5, 4 = 5 etc. Thanks for your help! I often use if statements to return empty cells, for example: =IF(a1=0,'ERROR',') The trailing ' returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one 'ERROR' cell to the next by hitting Ctrl arrow-down because it seems to think that these empty cells have contents. Is there a way to designate, in such a formula as above, to return a TRULY empty cell? Thanks
I have a UserForm with several fields on it: 5 are 'required' and the other 6 are 'optional'. If a user doesn't enter a value for ALL of the 5 'required' fields, how do I get the code to return the user to the UserForm (with, I assume, the values they already entered, still showing)? I have the AutoOpen code that starts things off, and then, in turn, loads the UserForm with the fields that need to be populated. I can't figure out how to route the user back to the UserForm, to enter the 'missed' data. I have a list of 5 buildings each with different unit counts, and an average price sold for each building. How can I get the average price of all units sold? A B 312 $1000 54 $1010 35 $1835 32 $1028 53 $1937
I have data with time in standard fromat i.e. 3:00PM but I need it to be in 24 hour format i.e. 15:00. How can I do this? I am trying to calculate the average Talk Time into Minutes and Seconds. Every time I have tried, I get the wrong average. For example, if I have a total of 53 calls and the total talk time is 4:19:05, what would the average talk per call be? I tried doing everything in seconds (this would give me a total of 15545 seconds) but the total I was getting is 4:89. What would the formula be to get the correct average time? Is there a way to calculate the average days between a column of dates? Basically, I've got a sheet that contains a table of entries, and each entry gets a date stamp in the first column. I would like to have the sheet display the average time, in days, between entries. Is this possible? Thank you in advance! ~cp Hi I have a table with various percentages, and I would like a formula to have an average of these. Please see attached file to get more of an idea. Thanks G
Good afternoon, Is there a way to enter a colon into a standard number to create a value that can be formatted into a 24 hour time value ? eg a time is listed as 1345 with a general number format, and I want it returned as 13:45 witha custom format of hh:mm. Other than creating a table and using a vlookup function, I am hoping there is a better way? Darren Hello, I have two columns of data: column A contains the date and time in the form dd/mm/yyyy. Column B contains a number value. All the times are in order, so column A looks like: 01/01/2007 02/01/2007 03/01/2007 I have data from 2007 to 2010. I need to calculate an average daily value (in column B) for each month, and display it in column C. So, for January, I need to calculate the average of 31 days, February, 28 days, etc. Is there a function I can use to do this? I've been trying the AVERAGEIF formula but can't get it to work. Any ideas would be much appreciated! Many thanks, Caitlin
I have an excel worksheet that adds two other worksheets in a data triangle. I copied it to create a new data set and used find & replace to change the worksheet references to the new ones. The cells still contain the result of the old formula referring to the previous worksheets. The only way I can get the formula to return the correct result is to edit (F2) each cell and press enter. Calc now (F9) does nothing. I've seen this before, but this time, I need to calculate many thousands of cells and don't have time for this workaround. Any ideas? Thanks. Don S
I need a formula to automatically calculate if the Job was completed in the date range that was provided, and to return Early, Late or Ok accordingly. Is there a way to use the new conditional formats (data bars, icon sets, etc) in PowerPoint? Basically, I'd like to create a table that shows icons based on data, like in Excel? I know I could paste a pre-made Excel table as a picture, but I'd like to avoid that if possible. Thanks! Hi, How to calculate average handling time.
Good mornng - I am new to the forum - my name is Jena My question is this - I have a large spreadsheet with multiple columns of information. I want to sort it by a certain column. I know how to do this. My question is, once I'm done with my work I want to sort it back to the original 'sort' but I'm not sure how they have it sorted. I've tried to figure out how they have it sorted but can't. Is there some way to go back to the original sort? Thank you for your anticipated help. Jena
I am trying to establish a formula, but not too sure how. For example ' If Cell A5 has been marked with ' X ', then copy what is in Cell A6 to cell C25 '
Hi folks, Hoping someone can help me quickly as I'm pulling my hair out and have a deadline getting too close. Been trailing the net and this board but can't work out how to do a simple variance. How do I work out the difference between two numbers - including negatives. If the numbers are all positive, it's fine as it's simply a case of A - B = C which is your difference. However, that doesn't work if B is negative. I need a formula that would give the following: First Number / Second Number / Difference 1 / 5 / 4 1 / -5 / -6 -1 / 5 / 6 -1 / -5 / -4 -5 / -1 / 4 Hopefully there's a simple function I've been missing. Thanks in advance, R
Hi, can anyone help me with the below.. Im looking for a vlookup formula which will give me the maximum number from a list which contains multiple matches, i.e. Lookup number 1 from column A, then give me the largest number from column B example table: A B ----- 1 5 2 2 1 11 3 2 4 5 the result would be 11 thanks all!
I am using the following formula to calculate the number of days between a date and the current date: =DATEDIF(A1,TODAY(),'d') What do I add to the formula to leave cell blank if no date is entered in the cell for calculation.
I am setting up a PivotTable and only want to show the actual raw data values in the PivotTable field and NOT any sum, count, average, max, min, etc etc. How can I do this? I only seem to have the options for sums, counts, etc. There are no options for just presenting the data. So for example, rather than a PivotTable that results in counts of 1, 1, 1 in each cell, I want it to show Compay1, Company2, Company3, etc. Good afternoon! I'm working with Excel 2003 sp 3 on Windows xp. I have been banging my head trying to jar loose the formula to calculate man days where 1 equals 8 hours. I have one that was given to me by a coworker: =INT(Q3/8)+(((Q3/8)-(INT(Q3/8)))*0.8) That calculates man days from total hours. I need a formula that will total man days and return the man day answer. Example: weekending 12/25 = 16.2 which is 16 man days and 2 hours weekending 1/1 = 24.7 which is 24 man days and 7 hours Total = 41 man days and 1 hour I have also been asked if there was a way for a number to be entered into a spreadsheet and automatically put in order without a macro or 'pushing a button'. Any advice is greatly appreciated! Happy New Year! ~ Brenda ~ I have a problem sometimes. I will click on a cell to add information. The cell turns blue and then wherever I move, it highlights those to. No matter where I go on the page. If I Alt-Tab and work in another program on my computer, that excel page keeps highlighting wherever I move even in those other programs (I know this sounds confusing). When I return to excel thousands and thousands of cells are blue. The biggest problem is that the highlighting won't turn off, no matter what. I can't select anything from the tool bars, do any work on the sheet or close the program. I can close it only with the task manager but when I open it again, the cursor is still stuck in the highlighting mode and won't perform any other functions. Do you think this is a problem with my computer, the excel program? I have changed my mouse and this didn't help. Is there some shortcut to turn off this highlight feature other than restarting my computer. Which is the only current way I can get rid of it. Thanks for any advice,
Hi All, If I drag a formula to the right it adapts the column letter in the formula from A to column B, which is standard Excel functionality. However, if I drag the formula to the right I want Excel to skip a column each time. In other words: =A1 ; = B1 ; = C1 Should be =A1 ; = C1 ; = E1 etc.. Is there an easy trick to do this? Thanks, Michiel |
- How To Graph Efficient Frontier In Excel For Mac 2016
- How To Graph Efficient Frontier In Excel For Mac Download
- How To Graph Efficient Frontier In Excel For Mac Windows 10
- How To Plot Efficient Frontier In Excel
- Graphing Efficient Frontier In Excel
I mentioned in a previous post on how to get (nearly) live stock data from Google Finance. However, if you start pulling data from different markets, daily historical rates won’t make sense as different markets are closed on different days. This causes problems when trying to figure out the correlation between stocks. A solution to this is to pull monthly rates as the adjusted stock price for each month will be a better indicator of correlation.
How To Graph Efficient Frontier In Excel For Mac 2016
If we find two efficient portfolios, we can plot the entire efficient frontier by varying the weight of capital applied to each of these portfolios. I can use Excel’s Solver to find two efficient portfolios; the way I choose to do this is to maximize the value of the portfolio mean by changing the portfolio weights, subject to the constraint.
The efficient frontier is the set of portfolios that gets us the highest expected return for any given risk level. Or from another perspective, the minimum amount of risk for an expected return. To trace this line, we can define a variable frontiery. Going back to the chart above, we can see the maximum return doesn’t go much higher than 0.3. I need to plot percentages over time in a line graph in excel. I don't want to have to do a percentage equation in the spreadsheet, I just want excel to take two sets of values and display the percentage in the chart. For example, I need B1 as a percentage of B2 for week 1.
Python has a great library called pandas_datareader that allows you to pull in historical information right into a pandas dataframe. The only downside is if an API is deprecated, your code breaks. Hence, I’ve elected to create this tutorial using Yahoo Finance’s Historical data download function. The objective of this article is to pull a large amount of worldwide historical data (over 20 years worth) and then use Modern Portfolio Theory (Mean Variance Optimization) to create an efficient frontier. The efficient frontier can help decide asset allocations in your portfolio based on a given risk tolerance and expected return.
Overview
The goal of the portfolio optimization is to retrieve an annualized expected return for a given risk tolerance. The return is associated with a portfolio of weightages (asset-allocation) to help decide investment strategies. The optimization strategy that will be used in this analysis is Modern Portfolio Theory (Markowitz, 1952), commonly known as Mean Variance Optimization (MVO) introduced by Harry Markowitz in 1952. The MVO model only takes into consideration historical results and thus is limited to that. It will not be able to account for other factors that could affect a model such as ‘views’ or insight into future market forecasts. A more robust model that could be incorporated in future optimizations is the Black-Litterman Model (Black & Litterman, 1992) that enhances the asset-allocation process by introducing user input for specific opinions on market returns.
Data Collection
Throughout this post, I will guide you through an excel workbook so you can understand exactly what is happening. If you’d like, you can download the excel workbook here.
In order to avoid high active management costs, I’ve elected to use Index Funds as the selected assets for the optimization. Market indices have the longest history of stock information and won’t have the risks of individual company stocks. Historically markets have grown and by using market indices, the expected return will be at the market average, which is far better than trying to outperform the market year after year. Pdf file resizer software, free download. In addition, indices helped provide greater exposure, both in terms of geographic location and in terms of type of asset. The following indices were chosen for the optimization (Table 1).
[Excel Workbook sheet: Names]
Index | Name | Country | Type |
^DJI | Dow Jones Industrial Exchange | US | Equity |
^IXIC | Nasdaq | US | Equity |
GSPTSE | Toronto Stock Exchange | Canada | Equity |
N225 | Nikkei 225 | International | Equity |
GSPC | S&P500 | US | Equity |
XOI | NYSE Arca Oil Index | Worldwide | Commodity |
HUI | HUI Gold Index | Worldwide | Commodity |
HIS | Hong Kong Stock Exchange | International | Equity |
FBIDX | Fidelity® U.S. Bond Index Fund Investor Class | US | Fixed Income |
VEIEX | Vanguard Emerging Mkts Stock Idx Inv | International | Equity |
FBNDX | Fidelity Investment Grade Bond | US | Fixed Income |
TYX | Treasury Yield 30 Years | US | Fixed Income |
VBMFX | Vanguard Total Bond Market Index Inv | US | Fixed Income |
VEURX | Vanguard European Stock Index Investor | Europe | Equity |
Table 1. Breakdown of assets chosen for optimization
Since the investors will be Canadians, I look at the geographic outlook in three different areas: Canada, the US, and International. Also, by type of assets, a mixture of equity, fixed income, and commodities were chosen.
Since the optimization would look at upwards of 30 years in the future, the more data collected, the better. Data was collected from July 1st, 1996 to March 1st, 2018 to account for approximately 22 years of data. It was a trade-off to either get more years of data or exclude the fixed income funds due to the limited data available for those assets. To avoid inconsistent dates for data collection due to different market closures worldwide, monthly data was collected. This time frame covers a few market crashes including the Asian Financial Crisis (Michael & John, n.d.) in 1997, the Dotcom Crash (Beattie, Market Crashes: The Dotcom Crash (2000-2002), n.d.) from 2000-2002, and the Housing Bubble/Credit Crisis (Beattie, Market Crashes: Housing Bubble and Credit Crisis (2007-2009), n.d.) of 2007-2009.
Adjusted monthly close price was pulled from Yahoo Finance (Historical Stock Data) by applying filters to pull from the following parameters:
- Time Period: Jul 01, 1996 – Mar 1, 2018
- Show: Historical Prices
- Frequency: Monthly
Data for each asset was collected in that indices’ respective currency and outputted to a csv file. Currency conversion was not required as the percentage return was calculated for returns.
Optimization Process
Monthly Returns
[Excel Workbook sheet: Adjusted Closing]
The adjusted closing prices were taken from each index and then collated into a table.
[Excel Workbook sheet: Returns]
After that the monthly returns were calculated as a percentage by subtracting the closing price of a previous month form the closing price of the current month and then dividing that by the closing price of the previous month.
Ri = (ClosingPricei – Closing Pricei-1) / Closing Pricei-1
An average of the all the returns was taken to get the average monthly return for each asset. Then those averages were multiplied by 12 to get an annualized average return.
Covariance and Standard Deviation
[Excel Workbook sheet: Cov-STD-Cor]
A covariance matrix was created by comparing each asset with all the other respective assets. This matrix is essential in understanding the risk of each asset and how it relates to the others.
The standard deviation for each asset was taken by looking at the monthly returns. This was then used to calculate the correlation matrix.
The top part of this formula is the Covariance matrix in the excel workbook and the bottom part is the D Matrix. By dividing these two, you get the Correlation Matrix.
Correlation
![How To Graph Efficient Frontier In Excel For Mac How To Graph Efficient Frontier In Excel For Mac](https://financetrain.com/wp-content/uploads/2010/06/Efficient-Frontier.gif)
Correlation is important in understand how similar two assets are. The higher the correlation, the more likely they are to move in the same direction. Negative correlation implies that if one asset goes up, the other will go down and vice versa.
As expected, assets have 100% correlation with themselves. In addition, the S&P500 (GSPC) is 95% correlated with the Dow Jones Industrial Exchange (DJI). This is intuitive as there is a lot of overlap between the two indices. Another interesting finding is that Treasury Bills are heavily negatively correlated with corporate bond indices.
Efficient Frontier
[Excel Workbook sheet: Efficient Frontier]
In order to create an efficient frontier, the expected return E(Rp) was maximized while constraining the standard deviation σp to specific values. The weights of each asset i, is wi. The correlation coefficient ρij is the correlation between assets i and j.
How To Graph Efficient Frontier In Excel For Mac Download
Before we do anything, it is important to remember to annualize the Expected Returns and Standard Deviations. Since we took monthly closing prices, multiple them by 12 as seen by cell A2 in this worksheet.
Excel’s Solver plugin was used to calculate these maximum points. If you don’t already have solver added in, go to File -> Options -> Add-Ins -> Manage: Excel Add-Ins Go -> Click Solver Add-in and select OK. If done correctly, you should now see the Solver option in the Data pane on excel.
How To Graph Efficient Frontier In Excel For Mac Windows 10
The weightage of each asset was constrained to positive values to avoid short selling and with an upper bound of 20% to ensure diversification. The solver was run for multiple different iterations of Standard Deviation for the portfolio (STD_P) to get different points for the efficient frontier. After about 15 scenarios, the Expected Return of the Portfolio (E(R_P)) was graphed against the STD_P to get your efficient frontier. To ensure you get a nice frontier, be sure you maximize the Expected Return, but also minimize it. Of course you’ll only care about the maximum expected return because why would you want less money?
As expected, portfolios with a higher return were exclusively in equities which have higher risk and portfolios with the least amount of risk were heavily weighted into the fixed-income assets.
The optimal point with minimal risk is an annual Expected Return of 4.55% and a portfolio consisting of the following assets:
Ticker | Weightage |
DJI | 0.11 |
GSPTSE | 0.08 |
HUI | 0.01 |
N225 | 0.03 |
FBIDX | 0.20 |
FBNDX | 0.20 |
TYX | 0.16 |
VBMFX | 0.20 |
Works Cited
How To Plot Efficient Frontier In Excel
Beattie, A. (n.d.). Market Crashes: Housing Bubble and Credit Crisis (2007-2009). Retrieved from Investopedia: https://www.investopedia.com/features/crashes/crashes9.asp
Beattie, A. (n.d.). Market Crashes: The Dotcom Crash (2000-2002). Retrieved from Investopedia: https://www.investopedia.com/features/crashes/crashes8.asp
Black, F., & Litterman, R. (1992). Global Portfolio Optimization. Financial Analysis Journal, 28-43.
Historical Stock Data. (n.d.). Retrieved 03 16, 2017, from Yahoo Finance: https://finance.yahoo.com/quote/%5EN225/history?period1=975646800&period2=1521086400&interval=1mo&filter=history&frequency=1mo
Markowitz, H. (1952). Portfolio Selection. The Journal of Finance, 77-91.
Michael, C., & John, C. (n.d.). Asian Financial Crisis. Retrieved from Federal Reserve History: https://www.federalreservehistory.org/essays/asian_financial_crisis
Graphing Efficient Frontier In Excel
Disclaimer — The material in this article is provided for informational purposes only. It is not a recommendation to buy or sell any security or implement any investment strategy.