Excel Question - How to find a price in a price series

Hi,

Say you have a day's worth of 5 minute price data in a column in Excel. For this example, say you have 81 data points, although the actual number of data points may vary in future applications.

For each row of price data, I'd like to find the first subsequent row pf price data that is N points above and current price in the row.

There would be three numbers per row: the price, and the row number of the subsequent bars that contain the +3 and -3 price bar.

So let's say N = 3 points. So if the first bar of data is at a price level of 1100, I'd like to figure out which subsequent row has 1103 and 997, if at all.

In the second row, the price is 1102. I'd then like to find the subsequent row that contains 1105 and 999.

This process would be repeated for the rows of price data.

The prices are not sorted (since they are the prices that occur during the course of the day), so the match and lookup functions aren't any help here.

A visual basic function would probably do the trick here:

FindRow( Array Reference, Price) = the row in the Array Reference that equals the specified Price.

Unfortunately, I haven't written a VBA function in over five years and forgotten how to do it.

Any suggestions?

Thanks.

-- M

 

Excel functions should suffice. I only know their names in french so I can't tell you exactly.

 

This should get you started. I have sacrificed elegance for simplicity, through no fault of my own.

(1) Let column A contain prices, say n in total
(2) In column B enter corresponding row number ie. B1=1, B2=2, etc.
(3) Copy and paste A1:Bn into J1:Kn
(4) Sort J1:Kn on ascending J
(5) In C1 enter =IF(VLOOKUP($A1+3,$J$1:$K$n,2,FALSE)>B1,VLOOKUP($A1+3,$J$1:$K$n,2,FALSE),NA())
and copy it into C2:Cn
(6) In D1 enter =IF(VLOOKUP($A1-3,$J$1:$K$n,2,FALSE)>B1,VLOOKUP($A1-3,$J$1:$K$n,2,FALSE),NA())
and copy it into D2n

Columns C and D are what you are looking for, and the whole process may be recorded as a macro. I have used the value of 3, but a cell reference would be more general. The smilie could have been eliminated with d2:dn, but I prefer consistency.

Remorseful edit : I'm not fully satisfied that the above will solve your problem, in fact I know it's flawed. The hour, however, is late so perhaps someone more alert can take over.

 

I could do this in Excel, no VBA, no VB, no macros. I would need to play with it and you would have a large cone shaped structure of formulas on the right side, since each row can find it's N target anywhere below. The cone would be as wide as the data is long, so there would be a limitation on the number of data rows equal to the width of Excel, column IV I think. The cone could be ignored as you can bring the answers back into the first few columns.

If you don't get a better solution, let me know and I can work something up for you.

 

Just a revision ... I'm searching for the first subsequent row that is N points or more above the current price, and N points or more below the current price.

Thanks.

-- M

 

Mr. Subliminal,

Thanks for your suggested fix.

I was hoping to find a solution that does not involve sorting the rows. Ideally, I could just copy and paste the formuals associated with one row, all the way down the column.

I am hoping to apply these formulas to year's worth of intraday data. The formula's would only apply to the current day's data.

I'm comfortable figuring out how to apply the formulas to the current day.

I'm just a bit stuck on finding the first row that is above and below the curren't row's price by N points or more.

Thanks again for your time though.

-- M

 

KTM,

Thanks for your suggestion.

I actually started doing just as you had suggested. For each row, I transposed the current row through the last row of the data set so that I had a flat, one-row high array. Since I'm looking at five minute data, it was a one row x 81 column array.

Then I realized that I didn't have enough columns in my Excel Spreadsheet to do all that I wanted.

I had some new ideas come to me in my sleep. I post it separately for all to consider.

Thanks again for your time.

-- M

 

The thought occurred to me last night that I might able be able to use the match function to find rows that are from N to say N + delta points from the current row's price

So say the current price is 1000, and I'm looking for the first row that is 1003 or larger. I'd do a manual MATCH function call for 1003, 1003.25, 1003.50, 1003.75, 1004.0 and so on.

I think it'll work most of the time, except when I get a big gap in price from one bar to the next.

I'll give this a shot to see how it works.

If have you a more elegant solution, I would welcome hearing about it.

Thanks.

-- M

 

CountIF could be of some use. Maybe try using ">B2+3" (where B2 is the price) in the criteria argument, when the count is greater than zero, you have found the row.

I don't think there's an elegant solution, but if you put two or three functions together, you can usually get the job done.

 

Thanks KTM.

CountIF returns the sum of the rows that meet the criteria, rather than the row itself.

It does at least tell you whether such a row that meets the criteria exists or not.

I'm now working on find rows that meet the criitera from N to N+D. I've done some statistical analysis and found a big enough D that should account for gaps that may occur between bars.

It takes up D x (# ticks / point) columns, but I think it'll work well enough.

Thanks.

-- M