Help w Excel Challenge

[hcs1210]

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))

[TABLE="width: 885"]
<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD="align: right"] Close [/TD]
[TD="colspan: 2"]% Change[/TD]
[TD]Conditions[/TD]
[TD]Count[/TD]
[TD]Dates where condition is true[/TD]
[/TR]
[TR]
[TD="align: right"]1/4/10[/TD]
[TD="align: right"] $1,886.70[/TD]
[TD][/TD]
[TD][/TD]
[TD]7 consecutive values > 0[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/5/10[/TD]
[TD="align: right"] $1,888.43[/TD]
[TD="align: right"]9.16%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/6/10[/TD]
[TD="align: right"] $1,878.42[/TD]
[TD="align: right"]-53.29%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/7/10[/TD]
[TD="align: right"] $1,876.72[/TD]
[TD="align: right"]-9.06%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/8/10[/TD]
[TD="align: right"] $1,892.59[/TD]
[TD="align: right"]83.85%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/11/10[/TD]
[TD="align: right"] $1,886.24[/TD]
[TD="align: right"]-33.66%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/12/10[/TD]
[TD="align: right"] $1,861.79[/TD]
[TD="align: right"]-131.32%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/13/10[/TD]
[TD="align: right"] $1,886.13[/TD]
[TD="align: right"]129.05%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/14/10[/TD]
[TD="align: right"] $1,886.52[/TD]
[TD="align: right"]2.07%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/15/10[/TD]
[TD="align: right"] $1,864.52[/TD]
[TD="align: right"]-117.99%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/19/10[/TD]
[TD="align: right"] $1,895.48[/TD]
[TD="align: right"]163.34%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/20/10[/TD]
[TD="align: right"] $1,867.95[/TD]
[TD="align: right"]-147.38%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/21/10[/TD]
[TD="align: right"] $1,850.57[/TD]
[TD="align: right"]-93.92%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/22/10[/TD]
[TD="align: right"] $1,794.82[/TD]
[TD="align: right"]-310.62%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/25/10[/TD]
[TD="align: right"] $1,802.39[/TD]
[TD="align: right"]42.00%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/26/10[/TD]
[TD="align: right"] $1,803.86[/TD]
[TD="align: right"]8.15%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/27/10[/TD]
[TD="align: right"] $1,818.90[/TD]
[TD="align: right"]82.69%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/28/10[/TD]
[TD="align: right"] $1,771.10[/TD]
[TD="align: right"]-269.89%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/29/10[/TD]
[TD="align: right"] $1,741.04[/TD]
[TD="align: right"]-172.66%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/1/10[/TD]
[TD="align: right"] $1,760.72[/TD]
[TD="align: right"]111.77%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/2/10[/TD]
[TD="align: right"] $1,776.92[/TD]
[TD="align: right"]91.17%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/3/10[/TD]
[TD="align: right"] $1,784.70[/TD]
[TD="align: right"]43.59%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/4/10[/TD]
[TD="align: right"] $1,732.99[/TD]
[TD="align: right"]-298.39%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/5/10[/TD]
[TD="align: right"] $1,746.12[/TD]
[TD="align: right"]75.20%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/8/10[/TD]
[TD="align: right"] $1,734.88[/TD]
[TD="align: right"]-64.79%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/9/10[/TD]
[TD="align: right"] $1,753.84[/TD]
[TD="align: right"]108.11%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/10/10[/TD]
[TD="align: right"] $1,749.76[/TD]
[TD="align: right"]-23.32%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/11/10[/TD]
[TD="align: right"] $1,775.74[/TD]
[TD="align: right"]146.31%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/12/10[/TD]
[TD="align: right"] $1,779.11[/TD]
[TD="align: right"]18.94%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/16/10[/TD]
[TD="align: right"] $1,802.06[/TD]
[TD="align: right"]127.35%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/17/10[/TD]
[TD="align: right"] $1,810.86[/TD]
[TD="align: right"]48.60%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/18/10[/TD]
[TD="align: right"] $1,823.39[/TD]
[TD="align: right"]68.72%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[codeliftsleep]

Its a challenge so the idea is for you to be challenged, not have someone give you the answer.

 

[jtakw]

Its a challenge so the idea is for you to be challenged, not have someone give you the answer.

And it could possibly be homework.

 

[codeliftsleep]

And it could possibly be homework.

Ok...well I'm going to give you your first programming lesson.

Get familiar with google and how to effectively use searches to help get answers you need. Its a programmers best friend.

 

[jtakw]

Ok...well I'm going to give you your first programming lesson.

Get familiar with google and how to effectively use searches to help get answers you need. Its a programmers best friend.

Almost seems like you're addressing me, but you're probably advising OP.

 

[hcs1210]

Its a challenge so the idea is for you to be challenged, not have someone give you the answer.

I'm definitely not looking for someone to give me the answer. I'm just really struggling with this and need some help. I thought that's what this forum was for.

I've been googling and going at this for awhile to no avail. I cannot figure it out. It's not my world. Why would I not reach out to a resource for help?

I am really trying to learn this stuff and just sitting here not knowing the answer is not getting me anywhere either.

I would really appreciate any help anyone can offer.

 

[hcs1210]

I wish - sadly I'm no longer in school. I just really want to learn excel (it's always been an insecurity of mine) so I'm working through some problem sets. Stuck here.