Which approach to create a chart similar to stock charts - different periods % series start from zero

fabiospark

New Member
Joined
May 29, 2015
Messages
12
Do you know the charts you usually find on finance pages whe looking for stock data?
You can see how the stock performed in a give period: a day, a week, a month, a year or you can choose
the initial and last dates of the priod you want to monitor.
Whichever the length you choose, the data will use the stock price of the first day of the chosen period
as a floor against which the subsequent variations of the price are confronted and the percentage value of the ratio
are used to present the performance line in the chart.

I'd like to build a thing like that where I can choose the initial and final date a period and in some way the value
of the first day becomes the floor for the subsequent calculations.

As an example let's say I have a table with dates in the first column and values in the second as you see at the end of the post.

If I choose 1-1-20 as the starting date I'd like to have these four percentage values:

val 1 = (123-120)/120 = (B2-$B$1)/$B$1 = 2.5%
val 2 = (132-120)/120 = (B3-$B$1)/$B$1 = 10%
val 3 = (126-120)/120 = (B4-$B$1)/$B$1 = 5%
val 4 = (135-120)/120 = (B5-$B$1)/$B$1 = 12.5%

But, if I choose 2-1-20 as the starting date I'd like to have these three percentage values:

val 1 = (132-123)/123 = (B3-$B$2)/$B$2 = 7,32%
val 2 = (126-123)/123 = (B4-$B$2)/$B$2 = 2.44%
val 3 = (135-123)/123 = (B5-$B$2)/$B$2 = 9.76%

Does anybody know it it is possible to do that without having to build a column for each period?
And maybe how to do it "on the fly", with some good formula in a named range or anything else?
For now, please consider Excel (2003) only, no VBA.
I can filter the data set, if it is useful or needed.

Thank you.

DatesValues% from 1-1-20% from 2-1-20
1-1-20120
2-1-201232.5%
3-1-2013210%7.32%
4-1-201265%2.44%
5-1-2013512.5%9.76%
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Watch MrExcel Video

Forum statistics

Threads
1,127,581
Messages
5,625,611
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top