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

 

[Mark2Aus]

It is working now. Thanks.

Is there a way of, instead of only one display of Buy/Sell, to have all the Buy/Sell displays alongside the same row in order for, at the end of the day, we would be able to have recorded all the signals given on a particular day and not just the last one? Thus, if a fist signal is Buy:148, it is recorded in column FE. If the next signal is Sell:165, it will be recorded in column FF, etc...
MarkAus

 

[SkipVought]

Is that a challenge?

Have you tried to design some logic to that effect?

Skip,

_[red][/red]

 

[SkipVought]

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, iCol As Integer
'=============================================================
'NOTE: change these ranges to fit your design
    Set rReserve = Range("A:Z") 'prices get entered in these columns
    Set rDisplay = Range("AA:IV")  '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
    Set rng = Application.Intersect(Target.EntireRow, rDisplay)
    iCol = Application.CountA(rng)
    With rng
        If iCol = 0 Then
            iCol = .Column
        Else
            iCol = .Column + iCol
        End If
    End With
    
    With Cells(rng.Row, iCol)
        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]

 

[Mark2Aus]

Wouldn't that be a similar logic:

As the day's prices are entered, when the price difference between 2 adjascent prices rises > = 10 pts, assign the max price to Max10.
When the price difference between 2 adjascent prices falls 10 or more pts, assign the min price to MIN10 (in a different column).
As prices continue to be entered, if later on the price difference between 2 other adjascent prices rises 10 or more pts, assign the new max price to MAX10a (a different column), etc...
When the price difference between 2 other adjascent prices falls 10 or more pts, assign the new min price to MIN10a, etc...

Therefore, in a sequence of prices such as:

10, 13, 11, 21, 18, 24, 21, 28, 25, 36, 34, 39, 37, 41, 30, 32, 27, 30, 20, 24, 18.

the displays would be:

Max10: Buy 21 (when 24 is entered after 18)
Max10a: Buy 36 (when 39 is entered after 34)
MIN10: Sell 30 (when 27 is entered after 32)
MIN10a: Sell 20 (when 18 is entered after the second 24 is entered).
And so on...

MarkAus

 

[SkipVought]

Is the new code NOT working as expected?

Skip,

_[red][/red]

 

[Mark2Aus]

To test it I entered the sequence of numbers I posted before: 10,13,11,21,18,24,21,28,25,36,34,39,37,41,30,32,27,30,20,24,18.

It returned as Display:

Buy 21, Buy 36, Buy 36, Buy 36, Sell 30, Sell 20.

As you can see, the first 2 displays were correct, then it repeated Buy 36 two extra times and then showed the correct Sell 30 and Sell 20.

MarkAus

 

[SkipVought]

I got
[tt]
BUY: 21 BUY: 21 BUY: 21 BUY: 36 BUY: 36 BUY: 36 SELL: 30 SELL: 20
[/tt]
It repeats at each price that is above/below the 10 point threshhold.

Skip,

_[red][/red]

 

[Mark2Aus]

Skip, would it be possible to have the code only showing as Display the new signals?
As it is, as you got on your latest post, where prices are being traded at 28, it still says Buy:21 or where prices are already at 41, the code shows Buy:36. Or worse, where prices are traded at 30, it shows Buy:36!!!

MarkAus

 

[SkipVought]

Sure. Have you tried anthing?

I think that you ought to take a stab at it.

Skip,

_[red][/red]

 

[Mark2Aus]

Skip, I don't know how to write codes, but I would change the end of the code is this way:

...
End With

With Cells (rng.Row, iCol)
If nThis > nMax10 AND Not IsEmpty(nMax10) THEN _
.Value = Buy: "& nMax10 AND Empty nMAX10

If nTHis < nMin10 AND Not isEmpty(nMin10) The _
.Value = Sell: " & nMin10 AND Empty nMin10
Application.EnableEvents = True
End With
End Sub

Probably this is completelty wrong but I would after displaying Buy:nMaz10, clear the contents of Nmax10, starting all over again. Threefore, nMax10 would only be filled again if another 10 or more points occur.

MarkAus

 

[SkipVought]

the nMax10 or nMin10 values are there. Otherwise, it would not repeat. So the variables would not be "empty".

Why not check these values against the numeric part of the previous cell's (Cells (rng.Row, iCol-1)) value. check out the Split function or the Instr function and the Right function.


Skip,

_[red][/red]

 

[Mark2Aus]

What is the use, after you have displayed nMax10, of keeping any value in it? It is past and of no use as prices move on.
By empting it, you will never repeat the same value and the process could start again when a new event (prices >=10) occur.

Is there a command which would empty a cell? As I don't know VBA codes, probably I am saying an absurdity!

MarkAus

 

[SkipVought]

"What is the use, after you have displayed nMax10, of keeping any value in it? It is past and of no use as prices move on."

Try your idea. Nuthin' stoping you.

Skip,

_[red][/red]

 

[Mark2Aus]

But Skip, how do I write a code to do what I am proposing? What should be the code to empty a cell?
That is the whole point. If I knew how to write VBA code I would not have asked for help.

Thanks

MarkAus

 

[SkipVought]

The objective is NEVER write to a cell unless the values are different.

But if you want to empty a cell, it is merely

TheCell.Clear

Skip,

_[red][/red]

 

[Mark2Aus]

Is this the way I should do it?

In the final part of your code:

With Cells(rng.Row, iCol)
If nThis > nMax10 And Not IsEmpty(Nmax10) Then_
.Value = "Buy: "& nMax10 And nMax10.Clear

If nThis < nMin10 And Not IsEmpty(nMin10) Then_
.Value = "Sell: "& nMin10 And nMin10.Clear
Application.EnableEvents = True
End With
End Sub

Thanks
MarkAus

 

[Mark2Aus]

And why when I right click a sheet containing historic prices and then copy and paste the code, nothing happens to the existing prices. The Buy/Sell will only be displayed only if I enter new prices on the next row?

And if I change the code in any way, say changing the cell range, nothing happens on the price sheet, no Buy/Sell is shown anymore, not even when I enter new prices?
MarkAus

 

[SkipVought]

Yes, on a new row.

Don't TELL me what you change, SHOW me.

Skip,

_[red][/red]

 

[Mark2Aus]

On the new row, do I write And nMax10.Clear or only nMax10.Clear?

I've changed your code as from:

Set rReserve = Range("A:Z") 'prices get entered in these columns
Set rDisplay = Range("AA:IV") 'BUY/SELL indication is displayed in this column
to

Set rReserve = Range("B:AG")'prices get entered in these columns
Set rDisplay = Range("AH:IV") 'BUY/SELL indication is displayed in this column

I still don/t know why the code does not work for existing prices in the sheet.

Thanks

MarkAus

 

[SkipVought]

nMax10 & nMin10 are VARIABLES not CELLS.

"The objective is NEVER write to a cell unless the values are different.

But if you want to empty a cell, it is merely

[b]TheCell[/b].Clear

"

"I still don/t know why the code does not work for existing prices in the sheet."

The logic is based on the Change Event on the sheet. If something is ALREADY there, there is no change.

Skip,

_[red][/red]