Can any Excel Clever Cloggs........

[Spike]

Suggest a bit of code that will tell me in the sheet what my actual refresh rate is?

Ie- measure the time since the last refresh and display it in a cell?

That way I could get my bots to opt out when the API is playing up.

[dgs2001]

Spike

I'm not clever enough to put it together but I have seen on the Mr Excel Forum a bit of code that timed the execution of a worksheet change event/Macro.

I imagine that this could be put into your workbook and could be adapted to time the reverse of what it was intended to do, or subtract the time now from itself. I.E. time the gap between it being called, (I think) that would achieve what you are trying to do.

Duncan

[dgs2001]

On Second Thoughts ..........

Gary I can't help thinking this would be a nice feature (not sure if any of the compo have it )

A selectable option to auto stop betting if API response times become slower by X% or excel refresh times become slower than requested by X%.

Duncan

[Spike]

It's tricky huh....

[Captain Sensible]

Don't think it's that tricky to do , I'm surprised one of the VB bods hasn't posted up some code. I had some code to log the high and low prices of the odds and I guess it'd be much the same just comparing the current time to the off (C1) against the time logged at the next refresh, if thats over your limit just set a marker somewhere you can include in your code to cease betting until it comes into your limits again.

Trouble is I haven't a clue with vba and someone on here posted it for me, if no one posts some code I'll see if I can fiddle with it to get what you want

[GaryRussell]

You can use the following. It will give you the average refresh time (in milliseconds) of the previous 10 refreshes.

Insert the following code into a module.

Code: Select all

Public Declare Function GetTickCount Lib "kernel32" () As Long


Insert the following code into the worksheet.

Code: Select all

Option Explicit

Dim dtStart As Long
Dim dtEnd As Long
Dim refreshTimes As Collection
Dim elapsedTime As Long
Dim totRefresh As Long
Dim refreshed As Boolean
Dim refreshCount As Long
Const avgCount As Integer = 10

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count = 16 Then
        If Not refreshed Then
            refreshed = True
            dtStart = GetTickCount
            Set refreshTimes = New Collection
            refreshCount = 0
        Else
            dtEnd = GetTickCount
            refreshCount = refreshCount + 1
            elapsedTime = dtEnd - dtStart
            refreshTimes.Add Str(elapsedTime), Str(refreshCount)
            totRefresh = totRefresh + elapsedTime
            If refreshCount > avgCount Then
                totRefresh = totRefresh - Val(refreshTimes(Str(refreshCount - avgCount)))
                refreshTimes.Remove (Str(refreshCount - avgCount))
                Cells(1, 26).Value = totRefresh / avgCount
            End If
            dtStart = dtEnd
        End If
    End If
End Sub


It will set cell Z1 to the average refresh which you can refer to from your formulas and macros. Change the constant avgCount if you want to average the refreshes over a different period.

[2020vision]

Hi

just a quick message from someone who doesn't really have a clue how to
use the Excel functionality, (although I would love to learn), I got this code
up and running, working perfectly FIRST time!

Maybe there is hope for me yet?

Or this is such a precise and simple to follow instruction from Gary that a
monkey could do it.? WELL DONE GARY.

I think I learn better from examples than research but I have just started
to look into the possibility of getting my head around Excel a bit.

Have a nice day to all - Michael

[Spike]

Gary- that's fantastic. Thank you so much.