Cymatic Trader Community

[mr_hossa]

hello, I still have a problem. I have no strength for this Can someone send me a working template Exel on horses? mr_hossa@o2.pl

[Cym27]

After further testing of my adaptation of the HorseFrontRunnerBack spreadsheet, I found that one situation that wasn't being guarded against sufficiently was when a leading horse dropped back late in the race, the subsequent lead horse was backed, and the first horse then regained the lead and won. In the race I was watching, the first leader's price went from 1.12 out to 12+ and then back in to 1.1. In order to cover this situation I've added another table - Resurgent Fav (Cells CC4 - CD50). This is used alongside the Steamer Check table. The latter attempts to spot a horse which is heavily backed in from its SP. The Resurgent Fav table attempts to spot a horse which has been a high price in running, although may have had a low SP, but is then heavily backed late in the race and betting is suspended BEFORE the heavily backed horse takes the lead. The Resurgent Fav and Steamer Check tables both use the percentage figure entered in cell BM6 with the blue background.

I've also simplified the Times sheet so that, if a course and distance time is already in existence, it only gets updated if the latest time is shorter.

I followed canuk's advice to remove part of the formula in column CA. I'm still looking at the cell clearing method. Gavin did say not to clear the Status cells of any row other than those where you intend to issue a new command (GREEN ALL, CANCEL ALL etc.), so I think it best to use

Code: Select all

Range("BD8:BD9") = ""
   Range("BA8") = "CANCEL ALL"
   Range("BA9") = "GREEN ALL"

rather than clear the whole of column BD.

One other note - due to timing/updating issues I noticed that the Liability figure (Cell BJ4 - total amount to be bet on any 1 race) can be exceeded by the Stake amount (Cell BC6), so bear this in mind.

To avoid confusion with canuk's excellent spreadsheet, on which mine is heavily based, I have renamed it to LeadingHorseBack. Remember this is a work in progress. Only run in testing mode until you are sure it works for you. Use your own input values. The ones in the spreadsheet aren't recommendations - they are only for testing.

Download latest version here:

http://ge.tt/8aYgaKM2/v/0?c

[Cym27]

An error was found in the formula computing the bet delay time (Cell BP1). Please download this version:

http://ge.tt/8aYgaKM2?c

Note - when selecting the Autopilot options, choose EndOfInPlay as the trigger.

[canuk]

Just uploaded an upgrade. Removed some redundant code. Simplified a few things that were not necessary.
Automated the market entry point to approx last 33 seconds.
Greatly simplified Status clearing logic, of "PLACED" cells, it had far to many pre-call checks.
Added STAKE ramp logic as odds drop and as the GAP to the 2nd place get wider.

Details on the INFO sheet of the spreadsheet, including a link to some videos.

Same place as before http://ge.tt/4FZNNZK2

Thanks to cym27 Steve for his continued interest, and check out his added ideas.
Gavin has given us a great tool, sharing and helping can make this great. I have three other spreadsheets cooking.
Feedback is always welcome, there is always room for improvment.

Canuk

[Bozu]

Just dropped by to say well done on a very interesting angle/concept.

I'm not an excel expert with regard to formulae and programming, but want to learn more so I can contribute effectively.

I have downloaded the 4.2 version and will take a look.

Kind
Regards

Nick

[Gavin]

Hi Canuk,

You are doing some very good work here, so I thought take a look and see if I can offer a little more help again. Your code is sound and works well, but I can suggest a small improvement that should help speed up the execution within Excel and keep it working optimally.

Execution is faster in Excel if you avoid selecting cells in code, just to access them directly. Selecting cells is the sort of code produced when you record a macro, but when writing VBA code manually (rather than letting a macro generate it), you can avoid selecting cells. There is no need to select cells in code, if it moves the cursor around then it is slow and when done a lot can cause flicker.

The existing worksheet uses the following code, which is the sort of code generated by recording a macro:

Code: Select all

Sub ClearStatusCol()
'
' ClearStatusCol Macro
'
    Range("BD8:BD34").Select
    Selection.ClearContents
    Range("AS2").Select
End Sub

but for more efficiency, change it to this:

Code: Select all

Sub ClearStatusCol()
'
' ClearStatusCol Macro
'
    Range("BD8:BD34").ClearContents
End Sub


The following suggestion is not for efficiency, but is just a good practise. It is best to clear the status cells after putting commands into the command cells (rather than the other way round, i.e. clearing the status cells before putting the commands in). This ensures that you don't accidentally send unwanted orders if there were old commands present in the command cells, which you don't want to be triggered again.

For example, the current code is this:

Code: Select all

Sub CancelAllGreenAll()


'
' CanceAllGreenAll Macro
'

   Range("BD8:BD33") = ""
   Range("BA8") = "CANCEL ALL"
   Range("BA9") = "GREEN ALL"

etc... 


... but it would be best to change it to this:

Code: Select all

Sub CancelAllGreenAll()

'
' CanceAllGreenAll Macro
'

   Range("BA8") = "CANCEL ALL"
   Range("BA9") = "GREEN ALL"
   ' Clear the status cell after putting commands into the command cells, not before.
   Range("BD8:BD33").ClearContents   

etc...


I watched some of your videos. Nice work

Best wishes,

Gavin

[canuk]

Gavin:
Thank you for the feedback, it is greatly appreciated, until three weeks ago I had done nothing, with VBA, other than what macros produced. The Google has been my only source of information and learning. That is OK........ but getting yours and any other feedback is fantastic.

I made myself a hard copy of the examples you illustrated, for future reference. Actually all three of macros should be deleted as they have no purpose in this spreadsheet, I have a note to this effect down below the usable sheet area, they are carry over from the good old days (a couple of weeks ago) .

I do have a related question.... The "Cymatic" sheet VBA is only continues past the first... if PQ6 >0
Where PQ6 is the Excel generated =Sum(po8:pq33) a check of 75 cells for content.
Can this be done directly in VBA without, the first Excel step?

Thanks again
canuk

[Gavin]

Hi canuk,

Yes, I imagine there are various ways in Excel VBA to check for whether the contents of a range of cells are all empty. One solution would be to use a FOR loop to check all the cells, although that might not be the most efficient.

Another option, that I am guessing is more efficient is to use the COUNTBLANK command, something like this:

Code: Select all

Function RangeIsEmpty(ByVal SourceRange As Range) As Boolean

  RangeIsEmpty = (WorksheetFunction.CountBlank(SourceRange) = SourceRange.Count)
 
End Function

WorksheetFunction.CountBlank(SourceRange), counts how many cells in SourceRange are empty, including those with formulae that evaluate to "".

Excel cell formulae are also usually very efficient. So it might not be bad to just use a formula in Excel to check whether cells are empty. Then refer to the outcome of that formula in your VBA, for example.

I haven't really looked into the relative merits of these various approaches for the calculation that you are making, so I'd suggest simply experimenting with it.

Kind regards,

Gavin

[nig299]

Just a thought.

It might be better if you do use a loop to check each one, as the decision to clear it or not might be based (in future) upon the values of other cells. e.g

If .cells(x,12)="Maybe" and .cells(X,13)="Oh alright then" then
.cells(x,56).clearcontents

You could try something along these lines:

Code: Select all

Option Explicit
Option Compare Text

Sub Rummble2()

Dim Lr As Integer, x As Integer

'Get the last used row in column A (if the runner names are in column A, if not, change to suit)
Lr = Sheets("Cymatic").Range("A65536").End(xlUp).Row

If Lr < 8 Then Exit Sub 'if no runners exit the subroutine

If Lr > 34 Then Lr = 34 ' (don't want to clear anything below this row)

'start from row of first horse

For x = 8 To Lr

           If Sheets("Cymatic").Cells(x, 56) = "PLACED" Then '(BD is the 56th column)
           Sheets("Cymatic").Cells(x, 56).ClearContents
           End If
           
Next

'NOTE: To Excel, there is a difference between "PLACED"  and "placed". If you have "placed" in the cell, it will not be
'cleared. Include the "Option Compare Text" (above the name of the sub), and Excel will treat it as the same.


End Sub

Try this out on a copy of your workbook.

[Bozu]

Hi Canuk,

I checked the videos after I had some test-mode action this afternoon. Also played around with the price ranges and had some fun seeing the results/outcome.

A couple of observations whilst playing..

As normal I traded my positions before the start of the race and then flicked over to the spreadsheet to see how things looked as it went in-play. I had connected the spreadsheet to Cymatic before the race to save time.

I noticed any profit/loss on my activity pre race, was carried over into the spreadsheet in the profit column. I also noticed that the matched back bet info showed the average odds, stake & count of my trading pre-race. These are just observations and did not impact the excellent performance of your programming.

I have a lot to learn about excel and formulae, Well done.

Kind
Regards

Nick