Excel formula question.

[Global OptionsTrades]

...I think the final result will be three or four formulas stitched together.

The formula will be straightforward. As you have seen, defining it is the difficult bit!

For ease of auditing / unpicking / modifying later, keep those three or four formulae in separate columns and then add them up (or whatever operator as appropriate) rather than nesting them all in one unfathomable cell.

 

[themickey]

Can you flesh that out a bit?

Ok, will try my best at explaining.
Basic info, price moves in waves which are random in interval and height & depth.

I'm attempting to measure these waves on a spreadsheet on multiple stocks, so on a spreadsheet you'll gets peaks and troughs, once identified dotted all over the spreadsheet, they are all waving and cresting at different intervals.

Old data is on LHS of spreadsheet, new data travelling toward the RHS.
So far this morning, in the wee small hours, I have identified the Peaks and values, I've got thus far.

This is how my trial spreadsheet last panel (just working on Peaks atm) is looking so far until last close.
As you will note, it's a large spreadsheet for 16 stocks I'm dummy trialling, I've arrived at column ~148 rows of data to achieve so fars results.
I've checked the data and I'm getting accurate results on Peak dates and Peak heights.

I'll just concentrate on Peaks atm, to attempt to simplify the process, the Troughs will be a straight forward inverse copy of formulas, so I'll leave that aside in the meantime.

The next hurdle to overcome is create a table whereby Peaks are tabulated in a tidy little box showing just the stock codes and Peak values so that I can ascertain whether Peaks are rising or falling.

Row #1 is Transaction dates
Row #2 is column numbers, not part of the formulas.
The "*" are obviously formulas where there is no Peak identified.

 

[SunTrader]

The formula will be straightforward. As you have seen, defining it is the difficult bit!

For ease of auditing / unpicking / modifying later, keep those three or four formulae in separate columns and then add them up (or whatever operator as appropriate) rather than nesting them all in one unfathomable cell.

Why do that?

I just pulled down the formula window till it is big enough. Then move it back after I am done.

 

[SunTrader]

Ok, will try my best at explaining.
Basic info, price moves in waves which are random in interval and height & depth.

...

I'm still lost as to what cell and what calculation are you trying to achieve?

 

[themickey]

I'm still lost as to what cell and what calculation are you trying to achieve?

Well, already I've answered most of my own queries, how do you tabulate Peaks and Troughs and their previous timing.

Next achievement is a standalone table to display the last two peaks and troughs with their values. Then I'm done.
So I need a formula which identifies the last two values in each row.

 

[SunTrader]

Well, already I've answered most of my own queries, how do you tabulate Peaks and Troughs and their previous timing.

Next achievement is a standalone table to display the last two peaks and troughs with their values. Then I'm done.
So I need a formula which identifies the last two values in each row.

Done.

But again I don't know what you mean by "identify" the last two peaks/troughs and their values?

 

[ondafringe]

Ok, will try my best at explaining.
Basic info, price moves in waves which are random in interval and height & depth.
View attachment 297404

I'm attempting to measure these waves on a spreadsheet on multiple stocks, so on a spreadsheet you'll gets peaks and troughs, once identified dotted all over the spreadsheet, they are all waving and cresting at different intervals.

Old data is on LHS of spreadsheet, new data travelling toward the RHS.
So far this morning, in the wee small hours, I have identified the Peaks and values, I've got thus far.

View attachment 297405
This is how my trial spreadsheet last panel (just working on Peaks atm) is looking so far until last close.
As you will note, it's a large spreadsheet for 16 stocks I'm dummy trialling, I've arrived at column ~148 rows of data to achieve so fars results.
I've checked the data and I'm getting accurate results on Peak dates and Peak heights.

I'll just concentrate on Peaks atm, to attempt to simplify the process, the Troughs will be a straight forward inverse copy of formulas, so I'll leave that aside in the meantime.

The next hurdle to overcome is create a table whereby Peaks are tabulated in a tidy little box showing just the stock codes and Peak values so that I can ascertain whether Peaks are rising or falling.

Row #1 is Transaction dates
Row #2 is column numbers, not part of the formulas.
The "*" are obviously formulas where there is no Peak identified.

So every trading day, you add a new column to the right?

What about the rows... why do you have 148 rows?

And how/where does the "Series" work into the mix?

Oh, I see you worked it out. Good!

 

[themickey]

Done.
View attachment 297411
But again I don't know what you mean by "identify" the last two peaks/troughs and their values?

On a chart they are identified visually but human bias only sees things the brain wants to see.
On a spreadsheet you see them as values, you know exactly date and size, it gets rid of bias and noise.

I think I've mentioned this a number of times previously, for my way of trading, I prefer to look at numbers rather than charts, I can only view one chart at a time, in a spreadsheet I can view hundreds of stocks simultaneously and number crunch them into priorities/rankings.

 

[newwurldmn]

Well, already I've answered most of my own queries, how do you tabulate Peaks and Troughs and their previous timing.

Next achievement is a standalone table to display the last two peaks and troughs with their values. Then I'm done.
So I need a formula which identifies the last two values in each row.

i think I actually had this question on my final exam for my algorithms class in college.

Worksheet formulas can work if have them calculating right to left to create a dynamic programming answer. This is tricky as your data set is expanding but you need to see the latest point to know if you are in a new peak or not.

best to do in vba (for excel).

 

[themickey]

So every trading day, you add a new column to the right?

Done automatically.

What about the rows... why do you have 148 rows?

First off, got to process raw data which includes weekend dates which need excluding.
Then seperate data from dates.
Then calculate moving averages as I use a 3MA to smooth the data.
Then number crunch the peaks.