excel code

Discussion in 'Trading Software' started by dividend, May 9, 2007.

  1. hello. is anyone familiar with coding in excel? i would like to code a cell to change colors when the value of that cell changes.

    example

    if current.price > previous.price then color.font(green)


    in other words how can i cache the current.price into a previous.price variable into memory to check the next price against?

    thank you.
     
  2. Visual basic script is the lang you need to know. I used to know it, but not anymore.
     
  3. =IF(H10>K10,"HOLD","SELL") then you can change the color , in this case, cell turns either green or red ..

    then udertools >conditional format to change to color you want ..

    no charge jake
     
  4. If you open up the visual basic editor and place the following code under "This Workbook", it will do what you want.

    Code:
    
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Static price As Double
        If Range("d3") > price Then
            Range("d3").Interior.Color = vbGreen
            End If
        If Range("d3") < price Then
            Range("d3").Interior.Color = vbRed
            End If
        price = Range("d3")
        
    End Sub
    
     
  5. Pro Trader your the man !
    thanks I copy pasted for future reference with my excel files
    I LOVE EXCEL !!! jake
     

  6. If you like that, you should see my statements :D :D :D
     
  7. Since the OP has not commented I hesitate on posting, BUT...

    The Workbook_Change event solution as written works only on one cell. Yet this event triggers on ANY change on ANY sheet in the workbook.

    Better would be to use WorkSheet_Change event. But even that triggers on ANY change on the specific sheet.

    Note: Both events occur AFTER the cell data has been changed. There is no built-in BEFORE event.

    Now then, the OP states i would like to code a cell to change colors when the value of that cell changes.

    My *guess*is he has a column (or multiple columns) of cells that change, maybe via DDE.

    The events above can be used if this is the case, but the code must be much more robust. The after change data (price) and some sort of identifier (cell address) must be persisted per cell for comparing. After compare, the persisted data must then be updated with the after change data. Use of static vars is a good solution with few cells being tracked. You could use a static array for many. But large amounts of static data is not a good idea. Maybe better to write it out to a hidden "lookup" worksheet.

    Osorico
     
  8. thanks for the code and responses.

    i mistakenly asked about a 'single' cell instead of an entire column range. this seems to be a lot more complicated... i initially thought that it could be possible to substitute a ("$col") instead of a single cell ("d3"), but that does not seem to be the case. however that code by protrader did work fine for a single cell.

    performance seems good for single cell but i won't know how it would perform for many many cells during market hours without the optimizations that osorico mentioned. today right after the fomc announcement, some quotes were lagged like it was 1929.
     
  9. Here's another snip. The framework maybe for what you want. Using WORKSHEET change event

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    'original var
    'Static price As Double
    
        Dim lookup_result As Double
    
        Const LASTPRICECOLUMN$ = "B"
    
        'for column number to column letter conversion. First 26 columns only.
        Const ALPHA$ = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    
        'verify we are processing numeric data. Or whatever type of data expected
        If Not IsNumeric(Target.Value) Then Exit Sub
    
        'stupid pet trick to get the Column Letter
        'allows filtering changes we don't need to process
        If Mid$(ALPHA, CStr(Target.Column), 1) <> LASTPRICECOLUMN Then Exit Sub
    
        '*****************************************************
        '
        'here is where lookup value to compare should go, based on...Target.Address is my rec.
        'of course, the method of storage of lookup data is yet to be determined.
        '
        '*****************************************************
    
        'just a test value. changed manually until lookup code is written
        lookup_result = 123
    
        Select Case Target.Value
            Case Is > lookup_result
                Target.Interior.Color = vbGreen
            Case Is < lookup_result
                Target.Interior.Color = vbRed
            Case Else
                Target.Interior.PatternColorIndex = xlPatternNone
        End Select
    
        '**************************************
        '
        'here is where lookup UPDATE would go
        'updatelocation = Target.Value
        '
        '**************************************
    
    
    
        'original code
        'If Target.Value > price Then
        '    Target.Interior.Color = vbGreen
        '    End If
        'If Range("d3") < price Then
        '    Range("d3").Interior.Color = vbRed
        '    End If
        'price = Target.Value
    
    End Sub
    
    If you need further help let me know.

    Osorico
     
  10. option to extend original code to column:


    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Static price(500) As Variant
    dim i as integer

    'example for column "D" make length est for max number of changes

    For i = 1 To 500

    If Cells(i, "D") > price(i) Then
    Cells(i, "D").Interior.Color = vbGreen
    End If
    If Cells(i, "D") < price(i) Then
    Cells(i, "D").Interior.Color = vbRed
    End If
    price(i) = Cells(i, "D")

    Next i

    End Sub
    ___________________________

    Osorico, I tried your code, but cells were not updating to green, only red each time a cell was changed.

    Actually, I opened another file and tried it again, but this time it did work ... some times. I would change a cell and it would work, but then i would lower it a few times and there was no change to red. Is there a decision threshold?

    Not sure why:confused:
     
    #10     May 9, 2007