Record odds movement

[vanbuuts]

Can anyone assist with some macro code which will record the last price matched in cells AA5, AB5, AC5, AD5 and AE5 every 5 seconds (for example)?

[Captain Sensible]

Copying the data is as simple as just using lines like

Worksheets("Sheet1").Range("AA5:AB5").Value = Worksheets("Sheet2").Range("AA5:AB5").Value

There's quite a few snippets of code on the forum , are you trying to record a trail of odds or just the previous odds from 5 seconds ago, here's a sheet done for someone ages ago , shouldn't be too hard to tweak viewtopic.php?f=5&t=8909. All depends where you want the data going and what criteria to copy etc

[vanbuuts]

Hi, I am trying to get a continual loop so in AA5 I have the data 5 seconds ago, in AB5 4 seconds ago, in AC5 3 seconds ago etc.

[Captain Sensible]

Have a look at the spreadsheet I coded in that thread as that did a trail of ten prices, should be easy enough to drop to 5 prices. That copied on each refresh so at a 1 second refresh it'd work fine. Otherwise you'd just need to code in an if statement to fire every second, easist way is the take a time stamp once the data is copied then a simple if the current time is greater or equal to that value plus one secod then fire again.

I'm off to the pub now but if you get stuck just post where you're getting stuck and I'll see if I can tweak it tomorrow

[vanbuuts]

Hi, I couldn;t find a spreadsheet on that thread. I;ve been trying to do this all day and am getting pretty much stuck from the beginning.

I have this as a macro, but am struggling to trigger it. When it does trigger the screen freezes and the odds don;t update

Code: Select all

Sub Start()
 StartTime = Timer

            Do While Timer - StartTime <= 3
            DoEvents
                        Loop
                       
      [AA5] = "=RC[-12]"
       Range("AA5").Copy
    Range("AA5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
         
       
         StartTime = Timer
            Do While Timer - StartTime <= 3
             DoEvents
                      Loop
                     
       [AB5] = "=RC[-13]"
      Range("AB5").Copy
    Range("AB5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
          StartTime = Timer
          Do While Timer - StartTime <= 3
    DoEvents
                Loop
               
       [AC5] = "=RC[-14]"
      Range("AC5").Copy
    Range("AC5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
   StartTime = Timer
            Do While Timer - StartTime <= 3
  DoEvents
                Loop
           
       [AD5] = "=RC[-15]"
      Range("AD5").Copy
    Range("AD5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
         
 StartTime = Timer
            Do While Timer - StartTime <= 3
  DoEvents
                Loop
         
       [AE5] = "=RC[-16]"
      Range("AE5").Copy
    Range("AE5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     
 StartTime = Timer
            Do While Timer - StartTime <= 3
 DoEvents
                Loop
               
           
        Range("AA5:AE5").ClearContents
       
                         End Sub

[Captain Sensible]

Haven't time to look at the code now, the sheet was in this thread viewtopic.php?f=5&t=8904&p=46331&hilit=copy+data#p46331

Here http://www.mediafire.com/download/oaj9a ... Book1.xlsm

Might give you some ideas

[vanbuuts]

Thanks. No problem - pub comes first

Will take look cheers

[vanbuuts]

Haven't time to look at the code now, the sheet was in this thread http://gruss-software.co.uk/forum/viewt ... ata#p46331

Here http://www.mediafire.com/download/oaj9a ... Book1.xlsm

Might give you some ideas

Hi again, the sheet looks interesting. I'm not quite sure what its showing though? Is it the last 10 matched prices (in AB to AJ?) Or is it the Back price available at the last 10 seconds (from most recent to longest?).

I think it could well do what I want with some tweaking but I'm trying to understand exactly what the values are

[Captain Sensible]

I've tweaked it so it'll only show the last 5 matched prices and you can set your own refresh. Very basic but hopefully the idea behind copying the code should be useful for you. I've just hidden the other columns by shrinking the width to 0 so it's easier to see.

http://www.mediafire.com/download/99rpd ... ok122.xlsm

I haven't checked it fully but the main code is very simple just

Code: Select all

   
If Range("AA4").Value + Range("AB1").Value <= Time() Then
    Range("AB4:AE60").Value = Range("AA4:AD60").Value
    Range("AA5:AA60").Value = Range("F5:F60").Value
    Range("AA4").Value = Time()
End If

[Captain Sensible]

Just noticed you need to change the line


If Range("AA4").Value + Range("AB1").Value <= Time() Then
Range("AB4:AE60").Value = Range("AA4:AD60").Value
Range("AA5:AA60").Value = Range("F5:F60").Value
Range("AA4").Value = Time()
End If

to

Range("AA5:AA60").Value = Range("O5:O60").Value

for it to record the last price matched, it was just recording the best back odds before .

[vanbuuts]

Hi CS, thats a great bit of code - appreciated. Just as a note, your previous (older file) would change to the next market - this would does not?

Would it be possible to add in that bit of code that changes the market when the market gets to 1 second before the scheduled off?

[Captain Sensible]

I stripped out the switch markets and copy data to sheet2 as I didn't think you wanted that. Just replace the code with this and it should switch when D2 is equal to or less than 1 second to the off

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Static MyMarket As Variant
    Dim switched As String
   
If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

If [A1].Value = MyMarket Then



   
   
   
If Range("AA4").Value + Range("AB1").Value <= Time() Then
    Range("AB4:AE60").Value = Range("AA4:AD60").Value
    Range("AA5:AA60").Value = Range("O5:O60").Value
    Range("AA4").Value = Time()
End If


 
   
    Else
   
    MyMarket = [A1].Value
    Worksheets("Sheet1").Range("AA4:AE60").Value = ""
    switched = "No"
   
 
End If

If Application.WorksheetFunction.IsText(Range("D2")) And switched = "No" _
Or Worksheets("Sheet1").Range("D2").Value <= TimeValue("00:00:01") And switched = "No" Then
switched = "Yes"
GoTo Switch_Market
End If



Xit:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Exit Sub
   
Switch_Market:
    Range("Q2").Value = -1
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
   
End Sub



[vanbuuts]

Thats super, thanks for your help. I didn't realise I needed that either until I used your original code.

I have some other code in my sheet1 VBA sub, should I place this after or before your code for it all to work?

[vanbuuts]

Actually just trying this code and it failed to switch to the next market??

[Captain Sensible]

Just a quick change as I tried it and it wasn;t reliable

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Static MyMarket As Variant
    Static switched As Variant
   
If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

If [A1].Value = MyMarket Then



   Range("AF3").Value = switched
   
   
If Range("AA4").Value + Range("AB1").Value <= Time() Then
    Range("AB4:AE60").Value = Range("AA4:AD60").Value
    Range("AA5:AA60").Value = Range("O5:O60").Value
    Range("AA4").Value = Time()
End If


 
   
    Else
   
    MyMarket = [A1].Value
    Worksheets("Sheet1").Range("AA4:AE60").Value = ""
    switched = "No"
   
 
End If

If Application.WorksheetFunction.IsText(Range("D2")) And switched = "No" _
Or Worksheets("Sheet1").Range("D2").Value <= TimeValue("00:00:01") And switched = "No" Then
switched = "Yes"
GoTo Switch_Market
End If



Xit:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Exit Sub
   
Switch_Market:
    Range("Q2").Value = -1
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
   
End Sub