Cymatic Trader Community

[Iain]

Thanks for the support guys.

Funnily enough although I mentioned horse racing in my first post, the inspiration to create this was football correct score. While looking through the Betfair app directory, I noticed an app where you could select individual scores and determine how much profit or loss each score would have. The author claims he is using multiple markets and using the ones that provide the best value so you get maximum profit from your bets. The issue I have with this is with cross matching and arbers it is very difficult to gain an advantage and even if you could it would be very small. So if we forget about multiple markets, what he is offering is a glorified variable profit dutching tool that can only be used for football. For what it is I think it is quite expensive, so I set about creating something that would emulate it with Cymatic and Excel.

I have another minor update as I noticed another couple of bugs. Clicking 'Submit Bets' before ticking any of the checkboxes, won't appear to do anything. However, as soon as you do tick a checkbox the bet will be submitted instantly not giving you a chance to tick further selections. Not good! Also loading another spreadsheet causes a VBA error and the application will stop with the debugger. Both issues are fixed with this update. One side affect of the changes I made to resolve the problem with loading another spreadsheet is any of the sheetnames can be changed by the user and it won't have any adverse affects. In previous versions that would cause the code to fail.

[felix79]

Hi Iain,

This is a really useful spreadsheet for working out dutching stakes, thank you for creating it. I have been using it to get the stakes required then manually adding them into the market as clicking the 'Submit Bet' button does not do anything. I have checked the trigger commands box is checked when it loads and the submit button is not greyed out from already placing bets in the market.

Out of interest I tried the v3.01 spreadsheet and that does place the bets into the market but then throws up a VBA runtime error box (even though it is the only spreadsheet open).

Unfortunately VBA is not my thing so despite looking at the code I'm non the wiser. I'm probably doing something silly as usual so if you have any ideas it would be appreciated. I'm running Excel 2007 on win 8.1 if it makes any difference.

[Iain]

Hi Felix,

I am glad you like my spreadsheet, but disappointed you can't get it to work properly.

When I designed the spreadsheet I was using Excel 2013 and windows 8.1. I initially thought it could perhaps be something in the VBA that is only supported in later versions of Excel. However, I have an old PC with Excel 2007 and I tried V3.02 on it and it appeared to work fine although I didn't have Cymatic installed on that computer. I installed Cymatic and connected the spreadsheet. One thing I noticed straight away was the pointer fluctuating between a normal and busy pointer every time the spreadsheet updated, which doesn't happen with Excel 2013. Could this be the reason the bets weren't submitting? When I tried it seemed hit or miss. It would be even worse with a fast refresh rate. I updated the spreadsheet replacing some time consuming VBA with a normal formula. When you click 'Submit Bets' it no longer disables the button, but clicking it again won't have any affect. Also I protected both Dutch and Lay Dutch sheets so none of the formulae can be accidentally overwritten (some cells may appear empty but aren't). The sheets aren't password protected and can easily be unprotected. A fast refresh rate isn't necessary for dutching and I recommend you set the primary refresh to a minimum of 1000ms. This works fine on my old PC (about 10 years old) and Excel 2007.

If none of this resolves the problem I would ask you to check after you click the 'Submit Bets' button if the Cymatic sheet has anything in the Command, Odds and Stake columns? Clicking the 'Submit bets' button doesn't do much and it could be done manually if all else fails. It changes the cell underneath the button, M9 to TRUE and that is all that is required to trigger the bets. So if you can't get it to work, manually typing TRUE in cell M9 should trigger the bets. At least all the bets would be submitted at the same time.

The new version is attached.

[felix79]

Hey Iain,

Thank you for taking the time to look at my issue and even producing another spreadsheet. I can confirm that the Submit Bet button (or changing M9 to TRUE) now works and places the bet to market. Very impressive.

For my purpose I am now going to try and figure out how to keep the submit button active after placing the 1st bet because I am using this to take an initial position and then may place further bets in the same market as it progresses. That said, even manually entering the figures the spreadsheet produces is a real time saver for me and makes the process so much simpler.

Thanks again Iain.

[Iain]

@Felix, I am glad you got it working and your comments inspired me to create this new version. You stated you used it to open a position which you would manage at a later stage (possibly in play). This made me wonder if you could in fact mix dutching with trading? I certanly thought it warranted further investigation and the new version allows multiple dutches, both backing and laying with a greening function.

Firstly, I will summarise the changes:

Backing Version

A new column is added to show the lay odds. While using the spreadsheet I found it frustrating not knowing whether I was taking a good price and sometimes switched to the laying tab to check. The lay column has no function other than to provide the user with this information.

To the right of the P/L column, another new column has been added, the Mkt P/L which shows your current position with existing bets in the market, offset by any new dutch you are considering placing.

I wasn't happy having a flawed spreadsheet, which is the case if you move the profit slider to produce a smaller loss than your full exposure (as explained in my first post). Unable to come up with a solution I decided to have 2 modes for dutch backing. Mode 1 is how the previous versions worked with the exception being the minimum value for the profit slider is break even. Mode 2 works in a similar fashion to the laying dutching sheet, where the profit slider at its minimum setting is not backing that selection at all and the maximum setting being full profit. Somewhere in the middle you will reach a crossover point where profit turns to loss or vice versa.

The staking section has been changed and has a default stake and a Fill or Kill delay setting. When selecting 'Profit Target' the previous versions would have the amount for each 1% of profit. I thought this could be confusing when using Mode 2 or laying so only Mode 1 backing uses that setting. Mode 2 and laying use the actual profit target. I was aware that this could lead to errors when manually entering the stake, so I added radio buttons to select Staking type and mode 1 or 2. When you change any of these settings the stake is automatically entered based on your default stake. You can easily change your desired stake as before by typing the new value. The Fill or Kill value will be used for your bets and greening. When a price moves away from you and doesn't get matched it is possible to enter a new bet, but the first bet willl remain in the market. With fill or kill you know it will be cancelled after the delay. Using a Fill or Kill setting of zero disables this feature. Although this spreadsheet is for dutching you can still use it to enter a single bet.

To thr right of the staking area is a market descripion and below a countdown timer. The countdown timer is colour coded. BLUE for normal countdown before the event starts, RED when the countdown is negative, GREEN when the event is in play, YELLOW when it is an estimated time (caused by opening a market that has already begun).

Below the 'Submit Bets' button are two new buttons, CANCEL ALL and GREEN ALL which perform the function you would expect. Beside the CANCEL ALL button is a number, usually zero. This shows the number of unmatched bets you have in the market which you may wish to cancel. Beside the GREEN ALL button is market information or your current profit/loss if you green the market. When a market is suspended or closed it states so, otherwise it will show a greening (or redding) value.

When you click 'Submit Bets' the settings are reset after the bets are placed, allowing you to make a new bet, but clicking the button twice in succession will only place one bet.

Laying Version

Most of the changes mentioned above apply to the laying version with the exception of the staking, although the default stake used on the backing sheet is still used to set the stakes.

Because both backing and laying sheets include back and lay odds, you could make a mistake thinking you are backing while on the laying sheet or vice versa. To assist with this I colour coded the staking area of each sheet, BLUE for backing and PINK for laying.

[felix79]

Excellent update. This does everything I need and also includes some features I never thought of but now they are there they have proved rather useful, such as the lay column and green all feature, which is now so simple as it took me while to get my head around the figures from the way it's presented in Cymatic. The only minor tweak I made was to slightly amend the formulas in the P/L and Mkt P/L columns so it shows the figures after BF commission, as this is how I have it set in Cymatic. For what I need this sheet works very well indeed. Thank you again Iain, you are a star.

[Harry55]

Hi Felix , Curious if you could offer how you managed to change the formula to show the BF Commission in the P/L Column? I'd love that feature as well. Not too great this excel stuff.

Great Excel by the way Iain.

[Shuffler99]

On version 3, I can't seem to change the stake from £100. I wondered if anyone else had the same problem.
Version 2 works fine for me.

Shuffler99

[Gavin]

Hi Shuffler99,

I haven't tried it myself, but I just noticed that you hadn't mentioned the latest version. Have you tried the latest version (version 4)?

Kind regards,

Gavin

[Shuffler99]

Thanks for that, Gavin, yes V4 seems to be fine.
Shuffler99