Can anyone help me with my speed problem?

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
michaelg2708
Posts: 20
Joined: Fri Sep 02, 2016 3:08 pm

can anyone help with this spreadsheet. i posted before with another spreadsheet and after advice i got from everyone on here i got it cut down to what it is now only the bare essentials, it now updates at 8 times per second but once i add my formulas it slows right down to only once per second sometimes slower and thats without my charts. my formulas compared to others i have seen on here are pretty simple and my formulas only use HS4-JU1300 looking at the top 3 horses. any help would be much appreciated or point me to someone who can help or where i could hire someone to do the work. the person that made it cant make it any faster either. my internet speed is 310Mbps, i have a i7-6700 (3.4GHz) and the speed doesn't change if i am using 1 or 8 cores so i know its not my computer.
cheers in advance

https://www.dropbox.com/s/julvmye6joxqd ... .xlsm?dl=0
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Hi Michael

This part won't necessarly speed it up but it will help to make the coding easier.

1. The same worksheet calculate code doesn't need to go in every sheet

If you click on "this workbook" and select "Private Sub Workbook_SheetCalculate(ByVal Sh As Object)"
the Sh returns the name of the sheet being calculated at the time, so you could use something along the lines of

Code: Select all

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

If Left(Sh.Name, 9) <> "Bet Angel" Then Exit Sub

Dim shtName As String

shtName = Sh.Name

 'When traded volume changes

If Worksheets(shtName).Range("DD2").Value <> Worksheets(shtName).Range("DD3").Value Then

    Worksheets(shtName).Range("DD3").Value = Worksheets(shtName).Range("DD2").Value

    If Worksheets(shtName).Range("DD1").Value = 0 Then

        Application.Calculation = xlCalculationManual
        Call Main(shtName)
        If Worksheets(shtName).Range("EF1").Value = "ON" Or Worksheets(shtName).Range("EF1").Value = "on" Then Call AdjustVerticalAxis

    End If
End If


Application.Calculation = xlCalculationAutomatic


End Sub
Similarly, all those modules ( one for each sheet) aren't necessary, you can use one and access it via the above by passing the sheet name as shown above, so instead of "Call Main1", "CallMain2", "CallMain3" etc, etc, you can just use

Code: Select all

Call Main(shtName)
This may help in speeding it up a bit.

As you are looking at each sheet in turn, you don't need the whole workbook to calculate all the time.

You can turn it off and get the sheet you are processing to calculate independently. (Might have to move this command to suit)

Code: Select all

Sheets(shtName).Calculate
Can't quite see the reason for stepping through the code:

Code: Select all

For i = 0 To 49

Worksheets(shtName).Cells(5 + counter4, 137 + i).Value = Worksheets(shtName).Cells(3, 137 + i).Value

Next i
If it's just a case of converting a range of formulas to values ? something along these lines would be quicker (got the range just by adding 49 to 137). Change to suit.

Code: Select all

With Worksheets(shtName)
.Range(.Cells(5 + counter4, 137), .Cells(5 + counter4, 186)) = .Range(.Cells(3, 137), .Cells(3, 186)).Value
End With
NOTE: not quite sure where

Code: Select all

counter4 = counter4 + 1
is getting its retained value from? That would need to be changed.


Finally

If you only trade pre-off you could add the proviso to only process the sheet if not in-play
If you only trade ## seconds before the off you could add a countdown timer and only process if within the allocated time.
Once a sheet has been finished with, clear any data that won't be used anymore.
You do not have the required permissions to view the files attached to this post.
michaelg2708
Posts: 20
Joined: Fri Sep 02, 2016 3:08 pm

nigelk wrote:
Wed Jan 23, 2019 1:45 am
Hi Michael

This part won't necessarly speed it up but it will help to make the coding easier.

1. The same worksheet calculate code doesn't need to go in every sheet

If you click on "this workbook" and select "Private Sub Workbook_SheetCalculate(ByVal Sh As Object)"
the Sh returns the name of the sheet being calculated at the time, so you could use something along the lines of

Code: Select all

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

If Left(Sh.Name, 9) <> "Bet Angel" Then Exit Sub

Dim shtName As String

shtName = Sh.Name

 'When traded volume changes

If Worksheets(shtName).Range("DD2").Value <> Worksheets(shtName).Range("DD3").Value Then

    Worksheets(shtName).Range("DD3").Value = Worksheets(shtName).Range("DD2").Value

    If Worksheets(shtName).Range("DD1").Value = 0 Then

        Application.Calculation = xlCalculationManual
        Call Main(shtName)
        If Worksheets(shtName).Range("EF1").Value = "ON" Or Worksheets(shtName).Range("EF1").Value = "on" Then Call AdjustVerticalAxis

    End If
End If


Application.Calculation = xlCalculationAutomatic


End Sub
Similarly, all those modules ( one for each sheet) aren't necessary, you can use one and access it via the above by passing the sheet name as shown above, so instead of "Call Main1", "CallMain2", "CallMain3" etc, etc, you can just use

Code: Select all

Call Main(shtName)
This may help in speeding it up a bit.

As you are looking at each sheet in turn, you don't need the whole workbook to calculate all the time.

You can turn it off and get the sheet you are processing to calculate independently. (Might have to move this command to suit)

Code: Select all

Sheets(shtName).Calculate
Can't quite see the reason for stepping through the code:

Code: Select all

For i = 0 To 49

Worksheets(shtName).Cells(5 + counter4, 137 + i).Value = Worksheets(shtName).Cells(3, 137 + i).Value

Next i
If it's just a case of converting a range of formulas to values ? something along these lines would be quicker (got the range just by adding 49 to 137). Change to suit.

Code: Select all

With Worksheets(shtName)
.Range(.Cells(5 + counter4, 137), .Cells(5 + counter4, 186)) = .Range(.Cells(3, 137), .Cells(3, 186)).Value
End With
NOTE: not quite sure where

Code: Select all

counter4 = counter4 + 1
is getting its retained value from? That would need to be changed.


Finally

If you only trade pre-off you could add the proviso to only process the sheet if not in-play
If you only trade ## seconds before the off you could add a countdown timer and only process if within the allocated time.
Once a sheet has been finished with, clear any data that won't be used anymore.

thanks for your reply. i am completely clueless with vba i wouldn't even know where to start putting that code in. would you be up for giving me a price to amalgamate my spreadsheet with this new code?
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”