Hi friends,
New here and really hope someone can help. This is for a work challenge so is super important to me. I have never created a macro in excel, though I'm almost positive this is the only way to get excel to perform the task.
In my workbook I have price data for a stock by day (I am using end of day closing price).
Columns are: Date, Close Price of Stock for given date, % Change (to determine whether day gave positive or negative return)
The first task - Finding the number of times the stock has had 7 consecutive days of positive returns. I used a frequency function (and help from a previous thread in this community) to find that this has happened 14 times since 2010. (I believe my formula is correct by only 80% sure)
Current task - Now I need excel to spit back to me the dates where this has occurred. The idea would be to have a list of the dates in a column in the workbook. I only need the date of the 7th day of consecutive positive return.
What is the easiest formula or macro to get the dates?
Below is a snipit from my workbook for reference.
THANK YOU ALL SO MUCH and Happy Holidays!
H
Frequency formula used to provide 14: =SUM(IF(FREQUENCY(IF(H3:H2010>0,ROW(H3:H2010)),IF(H3:H2010<0,ROW(H3:H2010)))>=7,1))
<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>
New here and really hope someone can help. This is for a work challenge so is super important to me. I have never created a macro in excel, though I'm almost positive this is the only way to get excel to perform the task.
In my workbook I have price data for a stock by day (I am using end of day closing price).
Columns are: Date, Close Price of Stock for given date, % Change (to determine whether day gave positive or negative return)
The first task - Finding the number of times the stock has had 7 consecutive days of positive returns. I used a frequency function (and help from a previous thread in this community) to find that this has happened 14 times since 2010. (I believe my formula is correct by only 80% sure)
Current task - Now I need excel to spit back to me the dates where this has occurred. The idea would be to have a list of the dates in a column in the workbook. I only need the date of the 7th day of consecutive positive return.
What is the easiest formula or macro to get the dates?
Below is a snipit from my workbook for reference.
THANK YOU ALL SO MUCH and Happy Holidays!
H
Frequency formula used to provide 14: =SUM(IF(FREQUENCY(IF(H3:H2010>0,ROW(H3:H2010)),IF(H3:H2010<0,ROW(H3:H2010)))>=7,1))
| Date | Close | % Change | Conditions | Count | Dates where condition is true | |
| 1/4/10 | $1,886.70 | 7 consecutive values > 0 | 14 | |||
| 1/5/10 | $1,888.43 | 9.16% | ||||
| 1/6/10 | $1,878.42 | -53.29% | ||||
| 1/7/10 | $1,876.72 | -9.06% | ||||
| 1/8/10 | $1,892.59 | 83.85% | ||||
| 1/11/10 | $1,886.24 | -33.66% | ||||
| 1/12/10 | $1,861.79 | -131.32% | ||||
| 1/13/10 | $1,886.13 | 129.05% | ||||
| 1/14/10 | $1,886.52 | 2.07% | ||||
| 1/15/10 | $1,864.52 | -117.99% | ||||
| 1/19/10 | $1,895.48 | 163.34% | ||||
| 1/20/10 | $1,867.95 | -147.38% | ||||
| 1/21/10 | $1,850.57 | -93.92% | ||||
| 1/22/10 | $1,794.82 | -310.62% | ||||
| 1/25/10 | $1,802.39 | 42.00% | ||||
| 1/26/10 | $1,803.86 | 8.15% | ||||
| 1/27/10 | $1,818.90 | 82.69% | ||||
| 1/28/10 | $1,771.10 | -269.89% | ||||
| 1/29/10 | $1,741.04 | -172.66% | ||||
| 2/1/10 | $1,760.72 | 111.77% | ||||
| 2/2/10 | $1,776.92 | 91.17% | ||||
| 2/3/10 | $1,784.70 | 43.59% | ||||
| 2/4/10 | $1,732.99 | -298.39% | ||||
| 2/5/10 | $1,746.12 | 75.20% | ||||
| 2/8/10 | $1,734.88 | -64.79% | ||||
| 2/9/10 | $1,753.84 | 108.11% | ||||
| 2/10/10 | $1,749.76 | -23.32% | ||||
| 2/11/10 | $1,775.74 | 146.31% | ||||
| 2/12/10 | $1,779.11 | 18.94% | ||||
| 2/16/10 | $1,802.06 | 127.35% | ||||
| 2/17/10 | $1,810.86 | 48.60% | ||||
| 2/18/10 | $1,823.39 | 68.72% |
<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>
