1

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

Yada
  • 30,349
  • 24
  • 103
  • 144
Omar
  • 11
  • 1

1 Answers1

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.

Yada
  • 30,349
  • 24
  • 103
  • 144
  • 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? – Omar Jan 21 '10 at 09:35