Statistics for trading

[rokafella]

I want to figure out how to use excel to find the percentage of times msft opens higher/lower after 4 days of subsequent higher/lower closings.

Any help, clues, hints or ideas would be appreciated.

Thanks,
Rok

 

[deepbluefaq]

This is how I did it, copied the data in from yahoo, then added the following columns. Sure, its clunky, but it did the job. See the attatchemnt

=IF(E15>E14;1;0)

puts a 1 in the colum if todays close was > then yesterdays close

=IF(G14=1;(H14+1);0)

adds up consecutive 1's

=IF(H15>3.5;1;0)

if we have 4 closes in a row, puts a 1 in the colum

=IF(I15=1;(B16-E15))

calculates the difference between the four up closes and tomorrows open

=IF(J15>0;1;0)

puts a 1 in the column if tomorrows open is up (the difference is positive)

 

[deepbluefaq]

this shows the addition of all cells where it happens and the average move, and % of up down days

note: its only run on a total of about 50 days, and I'd have to do the same for down days too

 

[H2O]

My problem is this :

A - B - C - D - E

Date - Products.............................................. etc.

1-1-05 - Settlement price for each product
2-1-05 - Settlement price for each product
3-1-05 - etc....
etc.
...


I add a new date every day, with the settlement prices in the subsequent colums

Problem: I want the top row to show me the difference between the last two dates (= change in settlement prices)

It sounds easy, but the problems is that the last row is different (increasing) every day.

So what I need is a formula that gives me the values of the last 2 filled cells in a certain column so I can calculate the difference.

Any solutions?

 

[H2O]

Mmmmhhh,

I see my post didn't show up the way I wanted...

What I have is columns with settlement data (horizontally) and I add one row of data every day (last settlement prices)

I want to create a row (first row) that shows me the change between the last two settlement prices.

(Remember, a new row is added each day, so I have to find out the values of the last 2 filled cells in a column to calculate my change)

Any solutions?