statistical analysis of stockmarket
Posted by Patrik on January 11, 2002 3:04 PM
Hi my name is Patrik and i´m from Sweden.
I wonder someone you can help me with one thing?
I have a column with positive and negative numbers. It´s the percentual changing from day to day of a stockmarket index here in sweden.
I would like to examine what happens the third day if there are two days with increasing index before the third day.
day 1 increasing...day 2 increasing.....day 3 ???
Is it possible to make a formula that can solve how often third day is up, down or just equal?
Thanks so much for your help!!!!!!
Posted by Tom DIckinson on January 11, 2002 6:08 PM
How about adding a column with this formula:
=IF(AND(A5>0,B5>0),IF(C5>0,"Up",IF(C5<0,"Down","Even")),"")
This assumes the data starts in row 5, and day 1 is in Column A, day 2 in Column B, and day 3 in Column C. Adjust the formula for your columns. Then at the top you can put something like this:
=COUNTIF(D5:D20,"Up")
=COUNTIF(D5:D20,"Down")
=COUNTIF(D5:D20,"Even")
These cells will provide the numbers.
If it would be better in a macro, then
Sub IncreasesCnt()
Dim Cnt, Up, Down, Even as Integer
Up = 0
Down = 0
Even = 0
For Cnt = 5 to 500
If Range("A" & Cnt) > 0 And Range("B" & Cnt) > 0 then
If Range("C" & Cnt) > 0 Then
Up = Up + 1
Else
If Range("C" & Cnt) < 0 Then
Down = Down + 1
Else
Even = Even + 1
End If
End If
End If
Next
Range("D1") = Up & " Increases"
Range("D2") = Up & " Decreases"
Range("D3") = Up & " Unchanged"
End Sub
This will go through rows 5 to 500; adjust as necessary.
Hopw this helps.
