Ok, I have downloaded 15 stocks' history and I need to find the portfolio returns (both equally weighted and price weighted), and run regression on them against the S&P 500 returns to find Beta (for both price weighted & equally weighted). I have all the data I need (Closing Stock Price, and % change) for the stocks and the S&P 500. What I don't know how to do is find the returns. Also I know where to go to run the regression model but I'm not sure what to plug in for X and Y. What it comes down to is, I have to run regression with the equally weighted returns against the S&P 500 returns to find Beta 1, and then run regression with the price weighted returns against the S&P 500 returns to find Beta 2. Anybody know how?
Hello!
I am a new member and am also relatively new to the more advanced aspects of Excel.
I would very much appreciate your help with something:
Each month I analyze and Excel spreadsheet containing data of a stock portfolio.
This spreadsheet - an example of which is attached - contains the following columns:
CUSIP Number
ISIN Number
Ticker
These are all essentially random identifiers for stocks (though usually there is some logic to them)
Investment Type
Security Name
Number of Shares
Base Price
Base Market Value
Part 1
An investment in any one company can be made up of several types of shares in that company.
For example, and investment in BP may be comprised (hypothetically) of ordinary shares, preferred shares, some of which are listed in London and some of which are listed in the US. Whilst this would show as four individual lines in the spreadsheet I receive, they should be considered as a single exposure to the one company - BP.
As such, what I would like your help with (please) is finding a way that I can automatically collate the data on this basis to work out what the largest exposures of the portfolio are.
In the example I have attached, US$21,000 is invested in CompanyB Ordinary Shares A and US$11,000 is invested in CompanyB Ordinary Shares. The total exposure to CompanyB is therefore US$32,000.
[Assuming that the only identifiers that can be used to collate this data automatically are the Tickers - in this case hypothetically B and V respectively (or perhaps a part cell search that picks up on the "CompanyB" part?) I could create a database (but where and how) of securities that should be associated with one another - if it helps].
Could you please kindly suggest how I can use Excel (and or other MS Office programs) to collate the data of the whole portfolio in such a fashion - ensuring that all (not just Company B) positions that comprise a larger exposure are combined and the Values added together - to form a calculated value for the exposure? Where a single position comprises the full exposure, it would be great if the formula could just input the value for that position (perhaps in the proposed Exposure column in the example spreadsheet).
Part 2
As is shown in the attached example, by inputting the Price 2 column, I am able to ascertain the performance of each position in the column Performance.
I would also very much appreciate if you could please help me understand how I could use Excel to calculate the Weighted Performance (in the named column, for example) for the overall exposure.
Re-taking the example, of CompanyB the two positions have both returned 5.26%.
The larger position is US$21,000 and the smaller position is US$11,000 - making the total US$32,000.
On this basis, the larger position is 66% of the overall exposure and the smaller position is 34% of the overall exposure.
In this case, the calculation (I would use, but not necessarily the best) for weighted performance would have an effective calculation of (0.66*5.26%)+(0.34*5.26%). Of course prior to this (and if possible integrated into the formula would be the weighting.
In the Weighted Performance column would be the performance of the exposure (where many positions combine to create this) or of the position (if there are no other positions in the same company to make a larger exposure).
Summary
I have tried to be as detailed as possible to be helpful, but am always happy to answer any questions.
I also want to note that each month a new spreadsheet arrives - and so if there was a way to automate this process each month (maybe some automated macro?) it would be really amazing!
I would like to thank you for all your help on this in advance - I really appreciate it.
Thank you!!!
I have some stock funds that I want to track. It looks like:
Column A
Name of Fund
Column B
Symbol
Column C
% of the total portfolio that this fund represents
Column D through Z
I will plug in the weekly close price going forward
One of my funds has the potential to be extremely volatile and my hope is to chart the weekly price and be able to identify if that fund is dragging the total portfolio down disproportionally to the percentage of the portfolio that it represents. In other words, one fund represents 15% of the portfolio and if it drags the total portfolio down by 40% then I want to be able to see that.
One of the columns that I don't have that I can add is the # of shares that I own in each if necessary.
Suggestions?
TIA,
Robert
Dear Most Amazing Excel Team,
I have data for calculating the Standard Deviation for a Portfolio of stocks. My data is in cells A1 to E6 and looks like this:
....... Weight 0.3 0.4 0.3
....... P( ) E(R) A E(R) B E(R) C
Boom 0.15 0.3 0.45 0.33
Good 0.45 0.12 0.1 0.15
Poor 0.35 0.01 -0.15 -0.05
Bust 0.05 -0.2 -0.3 -0.09
My Array formula, that is entered in cell B8, looks like this:
{=SQRT(((SUM(C1:E1*C3:E3)-SUM(C3:E6*C1:E$1*B3:B6))^2)*B3+((SUM(C1:E1*C4:E4)-SUM(C3:E6*C1:E1*B3:B6))^2)*B4+((SUM(C1:E1*C5:E5)-SUM(C3:E6*C1:E1*B3:B6))^2)*B5+((SUM(C1:E1*C6:E6)-SUM(C3:E6*C$1:E$1*B3:B6))^2)*B6)}
This works, but there must be a more elegant and compact way to do this?
Hey guys
I'm creating a spreadsheet to keep track of my stock holdings.
I've got several portfolios. Each of these portfolios is in its own "sheet"
I'm wanting to create a summary sheet which will total up my holdings in each stock.
eg
Portfolio 1 - Sheet 1
ABC 2000 shares
XXX 1500 shares
XZY 1000 shares
Portfolio 2 - Sheet 2
ABC 1000 shares
XZY 8000 shares
Portfolio 3 - Sheet 3
TTT 500 shares
So the summary sheet would collate the holdings over multiple sheets and produce something that looks like this
Summary - Sheet 4
ABC 3000 shares
XXX 1500 shares
TTT 500 shares
XYZ 9000 shares
Obviously if I add a new stock to one of the individual portfolios this needs to be automatically updated in the summary.
Can anyone give me any guidance on the excel formula to do this?
Cheers
Help Needed in Building a Logic to distribute any given amount "X" for "N" number of Stocks with different Prices.
Dear Forum,
I have a situation where I need to develop a scientific and mathematical approach which involves a lot of business sense before I put my money in Stocks.
While Investing in Stocks, we come across a situation where we need to diversify and put our money in specific stocks....Now this step is already been done...
Now, I have already selected the number of Stocks and also the Names of the Stocks...Each Stock has a different price and each is expected to rise or fall differently..
I would like to know how much money I need to put in each Stock so as to earn more profits with that combination.
Ex:-
Lets say If I have $ 10000 and I have selected three Stocks to be bought with this amount..
STOCK-NAME--PRICE------PROFIT
Stock I---------$ 32--------$ 37
Stock II--------$ 55--------$ 62
Stock III-------$ 77--------$ 81
I want to get a Logic which gives me which utilises the Investin amount and buys Stocks in such a way that can yield the maximum Profit...
This is a concept and the No of Stocks can increase or decrease as well as the Investing amount, the logic should be strong enough to be able to expand ...
When I add a new series to an Excel stock chart, the added series has stretched some of the high-low lines of the stock H-L-C bars. (they are stretched so as to meet the value of the new series)
Bob Peltier describes on a webpage how to deal with this for some circumstances. However, the example he shows to create a chart with H -L-C bars with an added series is not detailed well on this webpage so I don't understand what he did to get the H-L-C chart with the Index that he shows. It is not clear to me how to fix the problem. The webpage is:
http://peltiertech.com/Excel/Charts/StockChartPlus.html
Maybe I missed something.
Can anyone in this forum offer a suggestion?
Thanks,
Art
Hello,
This post is not about syntax or code. It's more about how to address a problem. I have several workbooks. Since I work for an equity trading firm, all my workbooks have a list of stocks. They follow two forms. I'll post the forms in a following post to make it easy to read. There were my first Excel Workbooks; so I'm wondering if there is a more effecient way to solve the problem.
Basically at the end of the day I need a list of stocks. I usually either use the autofilter (form A) or use a formula in another sheet to display the value of column A if flags are set or empty if not. Then I use an autofilter on that to print out.
Any ideas?
Hello;
1) I need to calculate M43:: sum[m=1 to m=9] G(m)*sin(m*x) for each value of tabulated x in column L.
The 9 values of "m" are tabulated in $D$21:$D$29
The 9 values of G(m) are tabulated in $C$21:$C$29
The first x value is in cell L43
Can someone please help in deriving the array formula to be entered in M43 ??
For other values of x in L44, L45, ... one would simply copy M43 and paste in M44, M45, ...
2) If it's not too much trouble, how about:
N43:: -sum[m=1 to m=9] G(m)*cos(m*x)/m for each value of tabulated x
Your help would be greatly appreciated.
Regards.
