fabiospark
New Member
- Joined
- May 29, 2015
- Messages
- 13
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.
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.
Dates | Values | % from 1-1-20 | % from 2-1-20 |
---|---|---|---|
1-1-20 | 120 | ||
2-1-20 | 123 | 2.5% | |
3-1-20 | 132 | 10% | 7.32% |
4-1-20 | 126 | 5% | 2.44% |
5-1-20 | 135 | 12.5% | 9.76% |