Counting the frequency of an event

Question

Hi,

I have a string of stock price data data and I want to be able to count how many days the price moves up or down lasted. So for example, from the data I have a used "if" functions to determine if a day was up or down: "U" for and Up day and "D" for a Down day. Lets say the string then looks like this:

UUUDDUDUDUDUUU

I want a formula to count that there were 3 Up days, and 2 Down Days, 1 Up, 1 Down etc etc... and then I will be able to built a profile of how long the prices moves in one direction lasted....i want to then display the results in histograms or charts.

Does anyone know what formula or function I can use to get the results I want?

Appreciate any help. thanks

Answer 1

Ok, I created something on Google Spreadsheet that tracks the S&P 500 for this month. You can enter any other stock prices you want.

You probably want something like the spreadsheet.

Look at the formulas for the columns: UP/DOWN, UP Trend, Down Trend

View the spreadsheet HERE.

Download the Excel.

Answer 2

This is almost correct. So at the moment this spreadsheet tallies up how long, for example, each of the "Up" trends lasted. Now what I want to be able to do it tally up a frequency of how long each lasted...

So for example, in your spreadsheet example, I want to be able to count that there were

2 days with 1 up day 2 days with 2 up days 1 day with 3 up days 2 days with 7 up days

At the moment I am unable to do this without each of the results being double counted.... it there a way to get around this problem?