Sensitivity analysis issue

[culpepper]

Hi,

I'm trying to do a sensitivity analysis of historical S&P Prices. Basically I'm trying to find the Max, Min, and Avg of historical prices via financial quarter. I've set up my sheet with individual dates dates since 9/2/1989 in the first column and all of the closing prices in the next column, along with columns for min, max, and avg to store the results.

Basically the trouble I've been having is I'm not sure how to segment the dates into financial quarters and only find the max, min, and avg of just those dates. For example between 6/1/1989-9/31/1989 I can find the S&P closed at its highest of $377.73. I can't, however, find a formula that I can pull through the entire list, that will find the max, min, and avg for the quarters that follow it.

Any help that could be offered would be greatly appreciated. Thanks

[duncandhu]

Hi culpepper,

I would add another column for quarter, you can use the formula:

Please Login or Register  to view this content.

Also a column for the year:

Please Login or Register  to view this content.

Now make a Pivot Table out of your data.
Under the "Row labels" category, drag your Year column, then your Quarter column below it.

Now drag the "Price" column into the "Values" section of the table. You can change the aggregation method to Max/Min/Avg by choosing "Value Field Settings" and then choosing the aggregation type.

Since you want to use the same column (Price) for the 3 different aggregation methods (Min/Max/Avg), just drag it into the values section 2 more times, then change the value field settings.

If you need help with the Pivot Table part, I would suggest googling it for a while, if you need more help, just ask.

Thanks,
Duncan

[culpepper]

Thanks for the help I really appreciate it

[culpepper]

Quick question: I need to pull data from the newly created pivot table (thanks by the way) based on financial quarter AND year (ex: Q1 of 2010). Is it possible to incorporate both aspects into the lookup value of a vlookup? Is there some other way to do this?

Any help would be greatly appreciated.

[protonLeah]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[duncandhu]

Hi culpepper,

I will reply with a solution when you comply with the moderator's above request.

Thanks,
Duncan

[duncandhu]

I see the thread title has changed:

If you need to pull the data from the Pivot table, you can use the GETPIVOTDATA formula, which allows you to specify more than one criteria (and thus making it more useful than VLOOKUP for these things).

Create a temporary formula in a cell by pressing "=" then clicking on one of the value cells in the pivot table. This should give you something like this:

Please Login or Register  to view this content.

Change the parts of the formula that correspond to the criteria (the "2014" part and the "Q1") and replace them with references to cells that contain the criteria you want.

Now copy the formula into whichever cells you need.

Please add reputation if this helps you.

Thanks,
Duncan

[culpepper]

Any idea how to automate it, so that I can pull from 1989 Q3, 1989 Q4, 1990 Q1, etc...?
I'm basically pulling it into a sheet that looks like thisS&P.PNG
Appreciate all of the help

[duncandhu]

My previous post was basically describing just that:

If put the formula into the cells marked High, Low, Average and link the criteria to the cells under Quarter, Year etc, you should be fine.

Thanks,
Duncan

[culpepper]

I've tried it a few times, and I think my formatting might be wrong.
I've attached a snapshot of both tabs, do you think you could give me an example formula for max price that I could extrapolate off of, say for 1989 Q3 and Q4?
S&P.PNGpivtable.PNG
Sorry to keep coming back to you but I'm in over my head.

Thanks a ton

[duncandhu]

So in my original reply, I said to make a temporary formula (with the intention of modifying it):

in one of the cells in your first screenshot under the column "High", press "=" to begin a formula, and then click on (for example) cell B3 in your second screenshot. This will give you the formula for the Max of S&P 500 Index for Q3 1989.

When you have done that, please post the formula in this thread, so I can advise on what to change.

Thanks,
Duncan

[protonLeah]

Please post a workbook so that formulas can be tested; can't do that with pix.

To attach a Workbook
(please do not post pictures or links to worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"