Excel Code 1

[Mark2Aus]

Hi everyone. Happy New Year!!!

I need to do some tests on a series of prices.
Prices are entered manually (+/- 100 prices per day) in a single Excel row.
For every new price I need to check whether it is at least 10 points above o below the latest price entered. If it is, I have to check the next time price will cross the latest peak/trough alongside that same direction. If so, in a reserved number of columns (after the end of the area reserved for entering the prices) I will write to "Buy" or "Sell" and the crossing price.
Probably it will become clearer below:

Row 5: 123 125 123 126 124 134 131 136 ...

So, I will have to check when the price is larger (or smaller) than the previous price. In the example above, the first of such price of interest is 124 (as the next price is least 10 points higher than 124). Therefore, the next time prices will cross upwards 134, the next peak after 124 has occurred (because after 134 has been recorded, price fell to 131 making 134 a peak), I will write "Buy" and the correspondent crossing price, which will be 135.

The same should happen to prices which are falling. Example:

Row 6: 123 125 123 126 124 129 119 121 115

So, as prices fell from 129 to 119 (10 points), 129 becomes my price of interest and the next time prices cross 119 (the next trough after 129 has occurred because after 119, prices rose to 121, leaving 119 as a trough), I will write "Sell" and the correspondent crossing price, which will be 118.

I hope this has become clear.
As I have no expertise in writing VBA, I am a techical user of Excel, I dont't know how to do it.
Can anyone help me?

Thanks

MarkAus

 

[SkipVought]

Hi,

Seems that this could be dome on the sheet with a forumla like...
[tt]
=IF(INDEX(A5:M5,1,COUNTA(A5:M5)-1)+10>=INDEX(A5:M5,1,COUNTA(A5:M5)),IF(INDEX(A5:M5,1,COUNTA(A5:M5)-1)-10<=INDEX(A5:M5,1,COUNTA(A5:M5)),"","SELL:"&INDEX(A5:M5,1,COUNTA(A5:M5))-1),"BUY:"&INDEX(A5:M5,1,COUNTA(A5:M5))+1)
[/tt]
assuming that your reserved columns were A:M


Skip,

_[red][/red]

 

[Mark2Aus]

Thanks SkipVought.
For what I understood, if my reserved area for prices is AM (13 entries) I need to have your formula in 13 columns from column N. Each formula will take into account only 2 columns (eg. ...INDEX(A5:B5,1,...), the next formula in column O will be ...INDEX(B5:C5,1,...).
Testing it, you will see that it does not work as it should return Buy:135 the moment I entered 136 in column H. Your formula returns blank.
Suppose my sequence of prices is:
123, 125,123,126,124,134,131,136,134,140,130,132,128 columns A:M).
A formula should have returned Buy:135 the moment I enter price 136 in column H. And later on, the formula should have returned Sell:129 the moment I enter price 128 in column M.
I am still in the dark....
MarkAus

 

[SkipVought]

Only if the last value is +/- 10 or more from the previous.

What you just described is NOT what you initially described.

Please get your requirements straight.

Skip,

_[red][/red]

 

[Mark2Aus]

Sorry SkipVought to fail to make myself clear. Sometimes it is difficult to express in words exactly what one means. I believe the example explains better what I am looking for.
From my first post I said "...For every new price I need to check whether it is at least 10 points above o below the latest price entered. If it is, I have to check the next time price will cross the latest peak/trough alongside that same direction. If so, in a reserved number of columns (after the end of the area reserved for entering the prices) I will write to "Buy" or "Sell" and the crossing price".
You noticed I said that if the next price is at least 10 points above the previous price, a "Buy" signal will be triggered "...the next time price will cross the latest peak ...". It could be the immediate next price or it can be 2 prices down the track or 3 or...
In my first post I also mentioned that around 100 prices are entered everyday, therefore I could have 5 or 10 or more returns from the formula. It will depend on the price sequence.

Example: (If you draw the prices on a chart, it will become clearer)
row 5: 100 103 101 106 104 114 112 113 110 115 112 117 106 109 105 107 100 110 108 112...

The formula should result: Buy @ 114 at the moment I enter the price 115, as the first price which would trigger the "Buy" order would be when 104 moved to 114 (10 points) but only when it has crossed 113 (as I said in first post, "...the next time prices crosses the latest peak", and the latest peak is 113 as after 104 has been entered, prices moved to 114, then dropped to 112, moved higher to 113, dropped to 110 and then crossed the latest peak at 113 on its way to 115.
The formula should then return "Sell" @ 105 at the momment I entered 105 because after prices hit 117, they dropped to 106 (11 points), rose to 109 and then crossed the latest trough at 106 on its way to 100. And so on...

Thanks for any help.

MarkAus

 

[SkipVought]

"row 5: 100 103 101 106 104 114 112 113 110 115 112 117 106 109 105 107 100 110 108 112...

The formula should result: Buy @ 114 at the moment I enter the price 115, as the first price which would trigger the "Buy" order would be when 104 moved to 114 "

HOW is 104 the price to reference??? Where did 104 come from???

It looks to me like 112 is since it is "...the latest price entered."

You've got something in your head that you have not conveyed.

Please be CLEAR, CONCISE & COMPLETE.

Skip,

_[red][/red]

 

[Mark2Aus]

104 is the first price of reference because it is the first price from where a strong 10-point move happened. When the prices hit 104, support was so strong that prices quickly moved 10 points. It is a price of reference because if prices in the future cross it, it will mean that the previous strong support has disapeared and prices will tend to fall further.

In the same way, 117 is the next price of reference as when prices hit it, sellers came in so large numbers that prices fell more than 10 points to 106. It is a price of reference because the next time prices cross it, it will mean that the previous strong resistance at 117 has disapeared and prices will tend to climb much higher.

MarkAus

 

[SkipVought]

OK. Let's define the significant data elements that you need to store in your procedure.

Max10: The latest price that was 10 or more points more than the previous price.
Min10: The latest price that was 10 or more points less than the previous price.

You belabored this point, but it really does not seem to be significant.
LatestHi: The latest high price after a falling price.
LatestLo: The latest loe price after a rising price.

Logic:
When the price differential between two adjacent prices rises 10 or more points, then assign the max price to Max10.

When the price differential between two adjacent prices falls 10 or more points, then assign the min price to Min10.

When a price exceeds Max10, then on that row display BUY Max10

When a price is less than Min10, then on that row display SELL Min10

Where does the LatestHi & LatestLo come into play?

In your example 114 is Max10, so when the 115 price is entered, you would display BUY 114

Similarly, 106 is Min10, so when 105 is entered, you would display SELL 106

Is that the extent of your logic?



Skip,

_[red][/red]

 

[Mark2Aus]

Spot on Skip. That's the logic.

You're right. Under your construction of the logic, there is no need to take into account the latest peak/trough as the price to be crossed and acted upon is Max 10 or Min 10.

Thanks

MarkAus

 

[SkipVought]

OK what are the "reserved columns" that contain the price values?

Where is the Buy/Sell column?

Skip,

_[red][/red]

 

[SkipVought]

Right-click the sheet tab on the sheet containing prices.

Copy 'n' Paste this Event Code in the Sheet Code Window...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, r As Range, nPrev, nThis, nMax10, nMin10
    Dim rReserve As Range, rDisplay As Range
'=============================================================
'NOTE: change these ranges to fit your design
    Set rReserve = Range("A:Z") 'prices get entered in these columns
    Set rDisplay = Range("AA")  'BUY/SELL indication is displayed in this column
'=============================================================
    Set rng = Application.Intersect(Target.EntireRow, rReserve)
    If Not rng Is Nothing Then
        For Each r In rng
            If IsEmpty(r.Value) Then Exit For
            nThis = r.Value
            If IsEmpty(nPrev) Then
                nPrev = nThis
            End If
            Select Case nThis - nPrev
                Case Is >= 10
                    nMax10 = nThis
                Case Is <= -10
                    nMin10 = nThis
            End Select
            nPrev = nThis
        Next
    End If
    
    Application.EnableEvents = False
    With Cells(rng.Row, rDisplay.Column)
        .ClearContents
        If nThis > nMax10 And Not IsEmpty(nMax10) Then _
            .Value = "BUY: " & nMax10
        
        If nThis < nMin10 And Not IsEmpty(nMin10) Then _
            .Value = "SELL: " & nMin10
        Application.EnableEvents = True
    End With
End Sub

Skip,

_[red][/red]

 

[SkipVought]

... and I failed to add...

Modify the code as annotated.

Skip,

_[red][/red]

 

[Mark2Aus]

As this is my first time using a VBA code, what should I do to do all the calculations? I opened my Excel worksheet, right clicked the tab containing the prices, clicked on show code and copied & pasted your code. I also changed the ranges to suit my spreadsheet.
Now, how do I test it? What do I have to click to perform all the calculations using the code?
Thanks

MarkAus

 

[SkipVought]

simply add prices.

Skip,

_[red][/red]

 

[Mark2Aus]

Prices are already in the sheet (rows 3:128) in columns B:FD. I've changed the code to reflect this range. Nothing is shown in the columns for the Buy/Sell results for the existing rows.

When I entered any new price for the next day (row 129), it says: "Run-time error '1004': Method 'Range' of object'_Worksheet' failed" and when I clicked debug, it showed me the code sheet and a yellow arrow pointing to the 3rd line within the Note which starts with Set rDisplay = Range...
MarkAus

 

[SkipVought]

What is in the Range(???) on that statement?

Skip,

_[red][/red]

 

[Mark2Aus]

Set rDisplay = Range("GA:IV") 'BUY/SELL indication is...

MarkAus

 

[SkipVought]

WHAT???

Is the DISPLAY not in ONE COLUMN? I'd expect it to be ...

Set rDisplay = Range("FE") 'BUY/SELL indication is...

since column FD is the LAST reserve column.

It's designed for ONE COLUMN.

Skip,

_[red][/red]

 

[Mark2Aus]

Initially I had simply copied and pasted your code just to test it: reserved columns for data : A:Z and rDisplay = Range ("AA"). Then, when I entered any price the error message came.
I then thought that because we could have many Buy/Sell signals you meant for me to modify the code "as annotated" matching the price data with the display columns. It stopped giving me the error message but no display was shown for any price I entered.
Now I've returned to exactly your latest post: price range "B:FD" and rDisplay to "FE". The same error message appeared.
MarkAus

 

[SkipVought]

Sorry, make that

Set rDisplay = Range("FE1") 'BUY/SELL indication is...

Skip,

_[red][/red]