Market Data Spreadsheet - Trying to average Daily and Monthly changes in market
I can handle alot of very basic excel spreadsheet, but I am clueless when it comes to doing formulas and or functions.
I am trying to have 4 sets of returns based upon the S&P 500 open and close for the past 60+ years. That correspond to a financial product.
The first is a daily average which will equal the sum of the market close value for the full year (ie July 26 2012 to July 26 2013 - or closest market day after the 26th) then the average is subtracted from the beginning index value and then the difference divided by the beginning value to result in a percentage. If this is a negative number 0% should be reflected.
The second is monthly average except done on a monthly basis - (ie July 26 2012 + August 26 2012....July 26 2013)/12 = X, X/beginning value = Percentage which is less than 0% should be reflected a 0%
Then MPP - which should be a basic calculation which calculates the percentage change from mos. to mos. (July 26 2012 to August 26 2012) for one year - the resulting percentage will be the sum of the 12 months (positive and negative) - i.e. if from July to July the figures are 1, 2, -3, 4, -5, 6, 7, -8, 9, 10, 11, -12 - the reflected MPP figure would be the sum of these percentage changes or 22%. The catch is there will be a cap on the month upside (none on the downside) - this changes and needs to be something I can manipulate.
The final is the APP which is something I can do fairly easily I think - this is simply the value of the market now-the value 1 year ago/beginning value = % change with a cap that will change.
Any advice will help for these 4 columns! I am attaching the value I have so you can see what I am talking about.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By drobotnik in forum Excel Formulas & Functions
Last Post: 07-25-2013, 05:52 AM
By keesberbee in forum Excel Programming / VBA / Macros
Last Post: 07-12-2013, 02:42 AM
By ganesh2cbe in forum Excel General
Last Post: 04-19-2012, 10:28 AM
By orangedays in forum Excel General
Last Post: 04-21-2006, 08:15 AM
By Nick Hebb in forum Excel Programming / VBA / Macros
Last Post: 02-21-2006, 03:15 PM
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1