The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Hi Experts,

In attached WB there is already one Code is there. In which I want to add one criteria & investigate code as if I do calculation manually it shows criteria meet but while running code it by pass that point.

1.Add this criteria in existing code. Before procedure start.

Copy formula from cell G7 till end "DropDwon". Procedure start.

while procedure running ignore rows in calculation which have "Ignore" in column G.

2.Investigation

Code do this procedure

Copy formula from Cell H2 & I2 till end. if in column I found "Hit" then find lower or higher values from last point date which in range L3:N3 to current hit row date.

Shift down whole data in range L3:N3 one row down & register new data.

Clear all formulas in Column H & I from row 3 till end.

Copy formula from H2 & I2 & past to where last hit found till end.

Query Part

If I put manually formula to row 132 to till end (in attached yellow highlighted) I found "Hit" in row 172 & previous point is High so need to find lowest point from range =MIN(D132:D172) which is 1012.10 in row 162. but code by pass this point. I don't know why?

reply me back if any part is confusing or for question on this issue.

Thanks

Math-G-V04.xlsm

In attached WB there is already one Code is there. In which I want to add one criteria & investigate code as if I do calculation manually it shows criteria meet but while running code it by pass that point.

1.Add this criteria in existing code. Before procedure start.

Copy formula from cell G7 till end "DropDwon". Procedure start.

while procedure running ignore rows in calculation which have "Ignore" in column G.

2.Investigation

Code do this procedure

Copy formula from Cell H2 & I2 till end. if in column I found "Hit" then find lower or higher values from last point date which in range L3:N3 to current hit row date.

Shift down whole data in range L3:N3 one row down & register new data.

Clear all formulas in Column H & I from row 3 till end.

Copy formula from H2 & I2 & past to where last hit found till end.

Query Part

If I put manually formula to row 132 to till end (in attached yellow highlighted) I found "Hit" in row 172 & previous point is High so need to find lowest point from range =MIN(D132:D172) which is 1012.10 in row 162. but code by pass this point. I don't know why?

reply me back if any part is confusing or for question on this issue.

Thanks

Math-G-V04.xlsm

Re 1)

What code ie what procedure ?

Why G7 till end ? your data starts at G3 shouldn't be G3 tlill end ?

Re 2)

Is this new procedure or existing procedure ? If Existing what is its name ?

gowflow

Couple questions

Re 1)

•What code ie what procedure ?

•Why G7 till end ? your data starts at G3 shouldn't be G3 tlill end ?

Add mention procedure at very first stage of macro start.

Bcoz G7 - as if I copy formula to G3 & dropdown it procedure #ref# error.

Re 2)

Is this new procedure or existing procedure ? If Existing what is its name ?

Yes existing procedure but I don't know which sub calculate that part. as this code is created by Mr.Rob (tagit) & Sir. gowflow and I am not very good in coding.

Thanks

```
Sub WriteFormulas(ByVal curRow As Integer, ByVal lastRow As Integer)
Range("H3:I10000").Clear
Range("H2").Select
Selection.Copy
Range("H" & curRow & ":H" & lastRow).Select
ActiveSheet.Paste
Range("I2").Select
Selection.Copy
Range("I" & curRow & ":I" & lastRow).Select
ActiveSheet.Paste
End Sub
```

Thank You

I see that you have 2 sets of data

First set from Col A to F

Second set from L to W

So basically what is the data that is 'data' like fix data and what the macro is supposed to do.

If you can explain this then I can follow the logic and see where it flops.

gowflow

A.Do u want to write complete new Code ?

B.or modify the existing one?

if A then have to clarify whole process from start.

if B then little wording which is bit less then A.

Thanks

the code is fine I think it is a small issue but sometimes small isssues takes ages to be found especially when one does not know the logic !

I need to know the logic.

try to explain as much as you can don't go in details I can figure out things but need an overview. If I need more details I will ask.

What I found is that it first start by taking the first date that is in L3 then look in Col A to find that date and when it finds it it put the formula in H and I that is a copy of H2 and I2 from that row found till end of data in col A

So what are these 2 formula in H and I are they correct like they are as they refer to a row that is above and row that is under like it refer to row1 and row3 !

then also you said you start by G7 coz you have a REF for sure as the formula points to last 5 rows above

=IF((C7-D7)>(AVERAGE((C3-D

here we are in row 7 and we are taking the average of row 6,5,4,3 and multiply this by 7

meaning you take the average of the last 4 days multiply by 7 and compare it to the actual if it is smaller then you put "Ignore" in that cell.

if this formula is correct then I can simply modify it to adapt from first row of data.

pls tell me in short AGAIN what is the problem, what is the routine missing ?

gowflow

gowflow

There is Data which need to process - Column A to F.

There is Data in Range L3:M3 & O3:W3 is formula which replicate data for the date in N3 from Column A To F.

If L3="Low" then Find Highest(Max) value From Column C & Range is C3:C32 (Step 1 Row - Step 4 Row)

So now L3="Low" M3=974 & N3=2/10/2011 & O3:W3 have formula which replicate data for for date.

This is the whole process. Now problem area if you do it manually put L3="High" M3=1245.63 & N3= 7/8/2011 so date 7/8/2011 is found in Row 132 so copy formula from H2 & I2 & past to H132 to I132 & drop Down till end. you will find "Hit" in I172 as L3=High so finding lowest values from range D132:D172 @ Row 162 (D162) & date is 8/22/2011. which is missing if we run the code.

Thanks

Now problem area if you do it manually put L3="High" M3=1245.63 & N3= 7/8/2011 so date 7/8/2011 is found in Row 132 so copy formula from H2 & I2 & past to H132 to I132 & drop Down till end. you will find "Hit" in I172 as L3=High so finding lowest values from range D132:D172 which we found @ Row 162 (D162) & date is 8/22/2011. which is missing if we run the code.

Thanks

Low 1012.099976 8/22/2011

is it correct ? I have not changed anything to the code.

do you hv the problem in 2007 and 2010 ?

gowflow

See attached i had highlighted problem area in column L To N.

Math-G-V04.xlsm

Math-G-V04.xlsm

I did the following I put a stop at date 7/8/2011 so I can track what happens till the supposingly date of 8/22/2011 that you claim did not catch.

So as you can see in the attached file, the date of 7/8/2011 falls in row 132 and the first Hit is on row 267 and NOT on row 172 like you claimed it or put it manually.

So if you look at row 267 I put the formula in the red cell and it give you the value of 646.1 which correspond to the date of 12/21/2011 on row 242 which is the correct data.

Don't know how you came up with the Hit on row I172 ??

chk the file

gowflow

Math-G-V05.xlsm

See attached

Thanks

Math-G-V05.xlsm

I ran the macro as is and here are the results.

gowflow

Math-G-V05A.xlsm

any way out or overcome this?

Thanks

and I go back to the same question I asked before where you said bluntly they are correct.

If you tell me how these are supposed to be calculated then I will review these formulas. As you noticed when the data was complete the hit was on row 267 where you had a hit on row 172 so for same block of data we have different hit !!!

Please explain both formula how they need to be calculated as the logic for the macro is 'I think fine' unless some hidden things I have not discovered yet.

gowflow

if current data point range is grater then previous (one row up result) then formula returns to previous point +/- current high or low point. if it is lesser then previous result (one row up result) it returns to previous result.

Thanks

I dint explain more on that part as if I write It may create too much confusion for you.

Thanks

I know and see you are also a quite knowledgeable expert in excel here so you knowing your stuff and explaining in detail the formulas do you believe the formulas are correct ? If you say so then I will be looking elsewhere.

I also need to understand the ATR formula Col G why does it start at row 7 what should it calculate and how ? something here does not make sense. Do you need this column for the hit ?

gowflow

ok fine, by part 2 you mean Hit formula Col I ??See heading in previous comment, there are two heading. Formula for GAP which is for solely Column H & Formula For Hit heading which for column I. Formula For Hit heading have 4 part. basically I had split the criteria.

So yes formula for column Hit but second condition of AND Statement.

I know and see you are also a quite knowledgeable expert in excel here so you knowing your stuff and explaining in detail the formulas do you believe the formulas are correct ? If you say so then I will be looking elsewhere.I am learning here so may be I am wrong on my part. but first look I dint find any thing wrong in my formula.

I also need to understand the ATR formula Col G why does it start at row 7 what should it calculate and how ? something here does not make sense. Do you need this column for the hit ?

Basically ATR = Average True Range in technical term. & actual calculation is bit different. Which is not required for this purpose. I just added for header Sake.

why it is start from row 7 ?

just copy from G7 & past to G2..... Error message "#VALUE!" & formula look like this "=IF((C2-D2)>(AVERAGE((#RE

it is calculate range of each day ( High - Low) what I did is average of past 4 days & compare with present day. if present day is higher then multiple of 7 then I want to remove that day from calculation.

why m I doing this ? as you know some time in electronic trading market prices sharply bottom down. bcoz of error in punching - stop loss trigger anomalously - so it effect presentation of graph as well as technical studies so need to ignore that days.

No it is not required directly for Column Hit, but yes indirectly while running code. need to ignore these days it may effect final results of points.

if present day is higher then multiple of 7

the formula Multiply by 7 is that what you want ? or you want to see if it is a multiple of 7 2 different things. Multiple of 7 is a number divided by 7 gives 0 as a reminder like 7, 14,21,28,32 etc...

so which one is it ? All 3 formulas you created ? or helped by an other expert in EE ?

gowflow

say this to me more in English

What is the meaning of Hit ?? when do you consider in Market/Trading terms that you have a Hit !!

You have a Hit when .....

Please continue the sentence.

gowlfow

Why

01 H2>=H4 Current Gap (H Column Value) is Greater then or equal to forward 2 value (H Column Value)

Why 2 values not next value ???

like H2>=H3 ....

gowflow

Wow !!!!

i can see 7/12/2011 date in result.....what is the problem?

thanks

Thanks

I have the regret to tell oyu that who ever wrote this code never took into consideration the Ignore !!!

In plain English it is totally IGNORED !

Please confirm that beside the Ignore issue all is ok with the data ?

gowlfow

i don't claim my strategy is unique but in my known circle no one use this kind of strategy. Currently i take my trade decision based on charts which takes hours & human emotional innervation but after this whole project i just need to click 2 or 3 buttons & it will find most probable profit trade in minute.

Thanks

I can tell you that if all the data is fine and now you can see the item that was missing before is only due to the Hit Formula as I suspected. !!! :)

this is your original formula:

=IFERROR(IF($L$3="High",IF

and this is my correction:

=IFERROR(IF($L$3="High",IF

Notice the H2>H4 you were comparing each other row where in fact you need to compare current row H2 with Next Row not the row after reason why you were missing that 8/22/2011 (that you probably noticed) and missed a lot more that you didn't notice !!!

Let me know your comments.

gowflow

hmmm true.....

Thanks

hhhmmmm correct !! see I told you, your a pro in your business !!! :)

And You are in yours i.e. Coding

High 811.9000244 5/29/2012 808.13 811.9 799.07 801.43

Low 756.7299805 5/18/2012 760 781.2 756.73 774.1

High 1148.670044 9/8/2011 1 126 1148.67 1103.4 1140.83

Low 1012.099976 8/22/2011 1032.07 1049.33 1012.1 1041.33

High 1243 7/26/2011 1236 1243 1182.3 1185.53

Low 1178.670044 7/12/2011 1208.2 1215.83 1178.67 1186.1

As far as the Ignore, I need to have more data with the Ignore to be able to test effectively. As you know better the outcome I would ask you to change the data so that you get some Ignore that falls on the current data captured so that when I modify and include the Ignore in the code you can then test and make sure the data has been excluded.

For sure we can do this in the scope of this question.

gowflow

And after amendment same thing I done and I don't find any discrepancy at any point so I am totally ignorant about what point I am missing in previous results. Eagle eye ;)

Give me some time I am on my way back to home as soon as reach I'll provide you data.

Thanks

http://mobile.experts-exchange.com/Networking/Web_Computing/Q_28400008.html

Thanks

Thank You

Math-G-V06.xlsm

run the macro in sheet Data.

Let me know.

gowflow

Math-G-V07.xlsm

Thanks

My question for you: Are you sure the data is not correct ??? Remember when you ignore 1 row that says Ignore you actually have this row counted as a date but the values are not. You expected that all the other rows remain the same and not the red ones ???

Are you sure of the conclusion you made ? The only thing I am missing in my routine is that I did not modify the formula Evaluate(MIN ... and Evaluate(MAX ... maybe this one caused the error as when we exclude a row from a range we cannot use this same formula we need to adjust the formula so because it was long I preferred to give you the result first and then see. My feeling is that I need to modify this formula and then it will be fine.

pls advise

gowflow

Deleting is not an option as when you do this with the kind of formula you have in Col G you get REF error in the 4 rows that are linked to the row that you delete.

We can do one thing after Code apply copy formula from G7 & past till end. Delete ranges A:F which have ignore in column G. yes i see #Ref! error but no matter after deleting ignore rows purpose is solved. my concern is only that if ignore comes in Cell G7 & we delete, #Ref! comes in that cell which may affect procedure for next data set exploration.

For that my suggestion store ignore formula hard coded i.e. in code it self.

My question for you: Are you sure the data is not correct ??? Remember when you ignore 1 row that says Ignore you actually have this row counted as a date but the values are not. You expected that all the other rows remain the same and not the red ones ???

ignore means totally ignore that whole data - Date - Open High Low Close Volume (A:F).

Are you sure of the conclusion you made ? The only thing I am missing in my routine is that I did not modify the formula Evaluate(MIN ... and Evaluate(MAX ... maybe this one caused the error as when we exclude a row from a range we cannot use this same formula we need to adjust the formula so because it was long I preferred to give you the result first and then see. My feeling is that I need to modify this formula and then it will be fine.

up to you sir..... what ever easy for you.

if you want to see how result differ from previous one graphically. then use excel stock chart.

Thank You

or you modify "formula Evaluate(MIN ... and Evaluate(MAX ... " which causes the problem.

it is your decision i am totally trusted you as Coding !!! which is not my expertise.

Thank You

this being said, I have done a workaround and twisted the code in a way to ignore the the Ignore rows.

Please check out the results and let me know.

gowlfow

Math-G-V08.xlsm

After Amending Ignore criteria these two point have different data & different values.

i.e. 2/9/2011 & 6/3/2011.

Thanks

gowlfow

When I run the old routine on the same data and place a stop at the first Hit I get row 32 that shows a Hit on the Date 2/14/2011.

So the Interval to check for MIN / MAX is row 3 to 32 and it finds a Low on row 30 which is the following data

Low 974 2/10/2011 983.57 1008.67

Now when I run the new routine that Ignore the cells Ignore I get the first Hit at row 28 which is the Ignore row and I tell it take the next row ie row 29 so the data interval for MIN/MAX is row 3 to 27 and 29 to 29 (this is how it works to exclude row 28)

and in that interval the data that it finds a Low on row 29 which is:

Low 980.7000122 2/9/2011 1004.6 1025.33

So basically the question now is directed to YOU:

Why the Hit on the first instance is on row 32 and on the second instance is on row 28 (which is the Ignore row that we moved to the next Hit that is on row 29)

If you can answer this then we can understand why the data is different. Again here it is not the routine but your Hit formula that behaves Waco !!! :)

gowflow

sure pls let me know what you need by posting a link in here.

gowflow

All Courses

From novice to tech pro — start learning today.

Col HIf you encounter Ignore in this row or the previous or the next then put a blank.

Col IIf you encounter Ignore in this row or the previous or the next the put the value of the previous row.

This way Ignore has no effect neither on the actual row or the previous or the next. I did this because I saw the your formulas in both Col H and I are dependents of current row next row or previous row so to cancel effect of the Ignore we carry over the data as from previous row that did not have any Ignore.

The new formulas are

Col H

=IF(OR(G2="Ignore",G1="Ign

Col I

=IF(OR(G2="Ignore",G1="Ign

and I think I also did a small modification in the code and here is the new version altogether..

gowflow

Math-G-V08.xlsm