Monday 30 April 2012

How might you take this further? Some ideas...

The simple little DB we have constructed will, I think you'll agree, do at least as good a job of showing our strengths and weaknesses as most spreadsheet driven record keeping solutions. The bulk of the hard work is already done, come what may! There are, however, any number of relatively simple things that can be done to enhance the task, and to enhance the presentation of our records. The purpose of this post is really to act as a sort of thinktank for you, so you can experiment further and personalise your database.

Let's say you're developing a killer strat or two, but are paper trading it. You obvioulsy would want to record the results, but wouldn't want the stakes and profit / loss to appear in your 'proper' trading record. On a spreadsheet you'd probably do this on a separate sheet, but with the database approach it's a doddle to incorporate such a feature whilst changing very little, and using the existing file structure. Simply insert a new field in the Trades table, with the name Paper or similar and make the datatype 'Yes/No' with 'No' as the default value. It's then simply a case of dropping the new field onto the Trades Form and incorporating it in the qryTradesBase - as another parameter as we did with the dates. Nothing else need be changed!

It's all very well being able to limit the selection of trades by date, but unfortunately, as it stands at the moment, there is no way of incorporating the chosen dates into the title bar of the reports produced. As far as I am aware there is no way of capturing such information from within the main Access application itself, but with a little bit of Visual Basic for Applications you might come up with a control form similar to the one I've mocked up below:


The code to make this work is actually surprisingly easy to develop, and again a little bit of reading around the subject and trial and error is the way to go.

You might want to explore a deeper nesting of information. If you trade predominately one sport, and, unsurprisingly, football is a good example, you might like to see how various strategies perform in different leagues, or in friendlies, cup games or tournaments. With a little bit of imagination most things are possible, and whilst it can be extremely frustrating, working out how to do something new is actually quite good fun and I'm sure is also good for your karma!

Sunday 29 April 2012

Getting it all on (virtual) paper

At the moment our trading record is presented in what might best be described as a utilitarian format, so the next, and last, task for this stage of our trading database development is to get the data into a format which makes reading it and drawing conclusions from it more intuitive and visually pleasing. As you would probably expect by now this is not incredibly difficult due to the highly graphical nature of Access.

Ensuring that 'qryTradesBySport' is highlighted (but not opened) in the All Objects navigation  panel, select the Create ribbon, and 'Report Wizard'. The dialog box that pops up lists the fields in the query. We will use all of them so you could just click the '>>' button and have all the fields move over onto the right side of the screen. The problem with so doing, and why I would recommend selecting the fields one at a time, is that the report will then display the fields in the order they appear from top to bottom. You could sort them, but if you select them one at a time it's easier to control the order in which they appear on the finished page.

To my mind the best way to display the query is with the games traded, followed by the stake information, the P/L information and then ROI and Strike Rate - all grouped by Sport - but arrange it as you see fit. When you have selected all fields and ordered them as you want them to appear click next - you will be asked if you want to group the records. The answer, of course, is that we do - by Sport. After selecting Sport as the group you should then be looking as something similar to this:

Clicking next brings you to the Sort screen - assuming you remembered to sort alphabetically by Sport when designing your query I'd ignore this screen  and click next. The next dialog determines how you want the layout to appear. Keep the default 'Stepped' on the left options group but change the right one from Portrait to Landscape. You are then asked which Style you would like - I just accept the default 'Office' style.

The next screen asks you for a name for your report, which will also appear as the report's title, so type one in and then select 'Preview' and click 'Finish'. This action causes the report to run, and as it's information is displayed via a paramaterised query you will be asked to provide the 'From' and 'To' dates. Following a brief pause whilst Access generates the report you will see something like this:


Now we're getting somewhere! There's work to be done still, but at least you can now start really to see the fruits of all the labour so far!

Arranging the display of the report is best done in 'Layout' view. Happily this view is quite easy to use, and unlike with the form layout view the boxes don't all contract and expand to the same degree as you adjust one.

I'm not going to go into detail about how to adjust your report. Play around with it, switching between 'Layout' and 'Report' view to assess your changes. Have a look at the long list of properties you can change on the property sheet - most are fairly intuitive - 'Text Align' for example allows you to choose to have the text left aligned, centred or right aligned among others. I learnt how to do this largely by trial and error - trust me  - it's the best way!

You can then repeat the above exercise on the 'qrySportsByStrat' query - and that will conclude the detailed 'how to' for this stage of the trading database.  I trust you have found it useful.

Friday 27 April 2012

Lazy man's querying

As we now have a query that provides us with our trading results sorted by sport, it would be a good idea to have one that does the same but sorting them by Strategy. The good news is that it is very easy to do, and extremely quick!

Right click on qryTradesbySport and copy it. Name the paste 'qryTradesByStrat'. Open the designer and change just one field. I'll leave it to you to work out which, but the finished result below might provide a clue:


We'll have a look at how to produce the report layout for both queries next time, and once we've done that we'll move on to the challenge of working out how to cope with a bank, credits, winnings, withdrawals et al.

Thursday 26 April 2012

Dig a little deeper

Whilst useful, our query doesn't really tell us too much more than the Trades form or even the Trades table. So it's time to put some of the more complicated querying techniques into practice.

Open a new query in design mode from the 'Create' ribbon, and select our qryTradesBase from the list on the 'Query Tab' and double click each field with the exception of 'TDate','Event' and 'Strategy' so those items appear on the bottom of the designer. One thing to note when working with the query designer is that until the query has been saved there's not a lot that we can usefully do. So let's click 'Save' and call this query 'qryTradesBySport'.

We are going to use what are known as aggregate functions in this query and I'm struggling with how best to get it down on 'paper' so it makes sense to a novice.  You might have to play around with this a bit to get what I mean... Firstly we need to select the 'Totals' button at the top right of the 'Design' ribbon. On clicking this you will see that a new 'Total' row appears on the designer, and the words 'Group By' appear under each field / table name. If you run the query at this point all you'll see is the trades, filtered by date, identical (except for the fields that we de-selected) to the results of qryTradesBase. By the way, notice that because our new query is based on the qryTradesBase query that we don't need to replicate the date selection process - neat eh?

Select the 'Group By' drop down on the TradeID field, and from the list select 'Count'. Leave Sport as 'Group By' and for the remaining fields select 'Sum' from the drop down...to leave something like this...


Running the query will give you something like this:


Notice how the field names of all fields bar 'Sport' have changed to reflect the aggregate calculation being performed. Save the query again. Let's enhance it further by adding the 'Stakes' and 'PL' fields from qryTradesBase again, and changing from 'Group By' to 'Avg' on both. You can probably guess that the query now also calculates the Average Stakes, and Average PL, by Sport. Add 'Stakes' once more, and 'PL' twice more. Change the new 'Stakes' and one of the 'PL' from 'Group by' to 'Max' and the other 'PL' to 'Min'. The query will now show our maximum amount staked per sport, and the maximm wins and losses for each as well:


This is also a good time to make sure that the data is presented neatly and in some kind of order. Go to the Sport column and select 'Sort' / 'Ascending' so that your sports are summarised in alphabetical order.

I hope you've been a bit more inventive than me in your dummy data - I seem to have a lot of mins and maxes at exactly the same figure because I've only entered one trade for that sport! The football row gives you the idea, though. Save your query, and go back into design mode as it's now time to add a couple of calculations using our old friend the 'Build' dialog...

First we want to calculate our Return on Investment, so lets go to the first vacant field in the design grid and type 'ROI:' - again ensure you include the colon... and then using the fields, you want to divide the PL by the Stakes, so you should end up with: ROI: [SumOfPL]/[SumOfStakes]. ( By the way I neglected to mention before that when you use the Build dialog and enter your field name as soon as you double click the field you want in your calculation a pesky <<Expr>> appears; you need to delete this.. i've no idea how to shortcut this - perhaps an Access expert can advise?  ). Select 'Percent' on the 'Format' tab of the property window.

Next we want to calculate our Strike Rate. This is simply winning trades / total trades. Thinking back to when we made qryTradesBase you'll remember adding the 'Winners' field, which is calculated to drop a 1 in if the trade won, and a 0 if it lost. This enables us to count the winning trades by using the 'Sum' aggregate function, as any 0's just get summed as 0 if you follow what I mean. By selecting 'Count' as the aggregate function for TradeID we can count the total number of games traded... if we used 'Count' on 'Winners' it would count all winners and all losers! You can probably now guess that to get what we want with this calculated field, we need to end up with this in the Build dialog: SR:[SumOfWinners]/[CountOfTradeID]. Once more, select 'Percent' as the format.

If you now run the query, something strange will happen. In addition to asking for a start and end date, Access will now ask you to enter 'SumofPL', 'SumofStakes'.'SumofWinners' and 'CountofTradeID'. This is not the desired outcome! The reason is that if we look at the designer we have still got 'Group by' on the columns in question in the total row. Access therefore assumes we are supplying our own data to run the query. The fix is simple...go to  'Group by' (the two new fields only!!) and change it to 'Expression'. This simply tells Access that the field is a calculated field rather than a parameter field. If you run it again you should get something like this:



Hopefully you can now see how relatively simple it is to extract some useful data from the three tables we created and present it in a fairly intuitive manner. In the next session we'll explore how to enhance the presentation of this data, and will end up with something like this:


Until next time....

Let's go data digging

With the nuts and bolts of our basic Trading database now in place we can record individual trades and examine them in detail and in summary on one useful form. I personally think that for the 'man hours' invested progress so far has been very good. It's now time to accelerate that a little and start to draw some interesting facts from our database.

Welcome to the enthralling world of database queries. Those totally new to this kind of thing might already be thinking what an accomplished programme Access is; but in truth very few professional db programmers would use it as the basis to build a commercial application. Such applications are much more likely to be developed using a pro DB like SQL Server or Oracle; both of which are comlplicated, capable and make Access look very much the younger, weaker sibling. Nevertheless the underlying principles of data separation (normalisation to give it its proper title), relationships and the basic structure of the DB are very similar. The big strength of Access in simple applications like ours lies in its relative simplicty. Under the hood, all three programmes use SQL - which stands for Structured Query Language - normally pronounced 'see kwell' in polite conversation.

Luckily for us Access enables us to use SQL almost completely graphically, so with no further ado let's create a basic Query.... On the main ribbon select Create / Query Design. You are then presented with the following screen:


The first query we will create will be a basic summary query, using data from all three of our tables, so click on 'Add' for each table and then 'Close' when finished:


Notice how the relationships we created last time are shown. The simple way to get what we want is to follow the structure of the Trades table. So double click on TradeID and that field will magically appear in the leftmost column of the designer. Do the same for TDate. When we get to sportID we want the sport to display rather than the ID, so doucle click on 'Sport' from the Sports table. Then it's back to Trades for the Event field, and then over to the Strategy table for the strat name. The rest of the fields can be entered from the Trades table, but for this exercise we don't need the Description field. You should have something like this when finished...


That's it! our first query is now created and ready to run. So go to the top left of the ribbon and click on the large exclamation mark (! for non English speakers) and you'll see something like this:


If this look very similar to the Trades table - that's because effectively that's what we've done - replicated it! Click 'Save' and you'll be prompted to come up with a name to replace the somewhat limiting 'Query 1' that it's called at the moment. Fashions change with respect to naming DB objects, but I always like to prefix my query names with the letters 'qry' so I'll call this one 'qryTradesBase'. The logic of me using the word base will become apparent in subsequent outings.

So all that effort has really just succeeded in duplicating something we've already done! So far, so useless!

Let's enhance it a bit by enabling us to select a range of dates to filter the data. Return to design view and place your cursor in the 'Criteria' box in the TDates column and type the following: Between [Start date:] and [End date:] - notice how logical the language is... the words in the brackets are prompts for the user, so go ahead and click 'Run' - and enter a start date when asked and an end date (you might need to change the dates of some of your dummy records if, like me, all your entries so far have been in April 2012! Put a couple in March and run the query with 1/4/12 and 30/4/12 as the parameters and you'll see only April trades.)

Hold on, you say, if I want all my trades to appear I don't really want to have to remember the date of my first trade and enter that every time.... what a pain that would be! Fortunately you don't have to.... take your cursor to the box immediately underneath the criteria you've just entered, and type the following: [Start date:] Is Null And [End date:] Is Null.

Run the query again, but this time just click OK for both the start and end dates. All your trades will now be displayed. For this to work it is crucial that the prompts are spelt and punctuated exactly the same way in both boxes - otherwise Access will assume you wish to filter by three or four parameters and all kinds of weird and wonderful results might ensue. This one little tweak adds an enormous amount of utility I think you'll agree. By entering at most two dates you can now see your trades by all time, by the month, the week or even the day (just enter the same date for Start and End if you just want one day) - simple as that!

We'll just do one last little tweak to this query for now - namely to decide whether an individual trade was a winner or not. The test for this, of course, is very simple! A positive PL entry means 'Yes' and a negative PL entry means 'No'. However, all we want to do is to record a number... 1 for Yes, 0 for No. Here's how to do it...Go to the last column in the designer, and in the field name type: 'Winner:' (with the colon - that tells Access we are entering a user defined field) and then right click and select our old friend the 'Build' dialog. Another old friend, the Iif function is used to decide how this is filled - you need to end up with this in the build dialog; Winner: iif([PL]>0,1,0). Run the query again and you should see something like this:


Just in case you were wondering what this work would look like if written in SQL... here it is in all its glory:

SELECT Trades.TradeID, Trades.TDate, Sports.Sport, Trades.Event, Strategy.Strategy, Trades.Stakes, Trades.PL, IIf([PL]>0,1,0) AS Winner
FROM Strategy INNER JOIN (Sports INNER JOIN Trades ON Sports.SportID = Trades.SportID) ON Strategy.StratID = Trades.StratID
WHERE (((Trades.TDate) Between [Start date:] And [End date:])) OR ((([Start date:]) Is Null) AND (([End date:]) Is Null))
ORDER BY Trades.TDate;
Bet you're glad we can do it graphically!

Monday 23 April 2012

Relationships matter!

A supportive and understanding partner is a useful thing in any trader's life; but it's not what I refer to in the title!

We are going to look at data relationships and why these things are the very building blocks of what makes a relational database a very viable solution for recording and analysing your trading activities.

Thinking back to our tables you'll remember that we have separated those items which are generic to our trading from those which are specific to our individual trades. In this way we ended up with three tables, the main Trades table and one each for the various Sports and the Strategies employed. We now have to create a relationship between those tables so that Access can collate and extract the information that we need to get meaningful data out of our DB.

We'll dive in and do so, then look at what we've done afterwards. Select 'Database Tools' on the main ribbon, then 'Relationships'. A blank page will appear, so we need to click 'Show tables' to bring up our tables. Add all three to the diagram by clicking. As this is a simple DB arrange them so that Sports is one side of the Trades table, and Strategy the other. (If this were a larger DB the advantage of leaving the 'ID' suffix on the field names in the tables will now become very apparent).

Creating a relationship is easy.... once you've worked out what it is...Select 'SportID' in the Sports table by left clicking, and whilst holding the mouse button down, drag the mouse to the SportID field in the Trades Table. On releasing the mouse button you will see this little dialog box:



Notice that Access has identified the two fields to be joined by the names used, and that I have ticked ' Enforce Relational Integrity'. Click 'Create' and repeat the exercise for 'StratID' from the Strategy table and the Trades table, again ensuring that 'Enforce...' box is ticked. You should then see the following:


That's it! Done and dusted!

But what, exactly, have we done? Basically we have created two 'one to many' relationships between our three tables. If you look at the Sports table you will see the figure '1' at the end of the black line, and the '∞' sign at the other end. In English, for every individual sport in the sport table there can be an infinite number of associated trades in the Trades table. Likewise for strategies. By selecting 'Enforce Relational Integrity' we have ensured that in order to be able to enter a new trade we MUST select both the appropriate sport, and an appropriate strategy. Save the relationship window by closing it, and try to enter a new trade without selecting both a sport and a strategy. You won't be able to!

The DB cannot determine whether or not you have chosen the correct sport and / or the correct strategy, but it sure as hell won't let you not select one, correct or not!

Believe me if you are serious about taking this project further with your own ideas grasping this fundamental concept is essential! Making the correct decisions about how to split and record your data at the outset saves hours of tears and frustration later after you've entered shed loads of data. Trust me, I've got the tee shirt!

To enable you to see what a professional database looks like I suggest you download the free 'Northwind' sample DB from Microsoft. The relationship diagram from this is shown below and you will see how simple our little trading DB is by comparison!


Next we'll start to look at how to extract some interesting facts and figures from our DB.

Fine tuning the Trades form

A couple of useful little tweaks to further enhance the information available from the main Trades form. We'll introduce three text boxes to show our total trades to date, total P/L and the average P/L on those trades, and we'll also put a text box on the form to display the current date. Again, I think you'll be pleasantly surprised by how easy this is to achieve.

First, we need to create some screen space, so with the Trades form in 'Design View' select the 'Form Footer' and either drag the bottom edge down a bit or go to the properties sheet and select 1cm as the height. Drop four text boxes into the new space, deleting the associate label with the leftmost but keeping the other three labels.

The leftmost box will hold the current date, so select 'Data Source' and bring up the 'Build' dialog as we did before. Type an '=' sign then navigate to Functions / Built-in Functions / Date Time and select 'Now' by double clicking it. Close the build dialog, change to the Format  tab, select the Format menu and choose 'Long Date'. Simple as that. The text box will now display the current date in 'dd/mmm/yyyy' format if you are in the UK.

The second text box will hold the number of completed trades, so put a suitable name in the label, and again bring up the Build dialog. Again type an '=' then go to Functions / Built in Functions / SQL Aggregate and select 'Count' by double clicking it. This will bring up the following: Count(<expr>) - so all we have to do is to substitute <expr> with what we want to count! Navigate to the top item on the left pane of the build dialog, which should read 'Trades' and on clicking it all the fields in our table will appear. It doesn't really matter what we count, but for the avoidance of silly errors I'd be inclined to choose 'TDate' as every trade will have a date!

Repeat this step for the next two boxes, selecting the SQL Aggregate 'Sum' for the Total P/L box, using, unsurprisingly, the 'PL' field as the expression to sum, and select 'Avg' (i.e. Average) for the final box again using 'PL' as the field to average.

Whilst you are about it why not use the conditional formatting to colour the text in these boxes as we did before? You should end up with something a bit like this....


These pieces on Access have repeatedly used the term 'relational database' and we haven't yet really got to grips with what that actually means. In the next exciting installment we'll start looking at relationships between different sets of data and how to implement them. Bet you can't wait :-)

Friday 20 April 2012

Balancing the books

Fed asks whether we will be able to track bank withdrawals when we get to that stage of the journey, and I thought that as a break from the practical we could have a look at the theoretical for a while.

A bank balance, ultimately, is the sum of a column of numbers, some positive, some negative. That, in itself, is meat and drink for a programme like Access or Excel, but the approach in the two programmes is different.

I've got to say in this particular instance it probably makes much more sense to keep a running balance in the spreadsheet. One way you could achieve this is with four columns, a date, a balance bought forward, a credit / debit and a balance carried forward. The only other thing to consider is the start balance, but that is easily overcome by placing that figure in the original Balance C/F column. If we wish to know what the start balance was on 15th April 2012 we just look for that cell. If we want to make a graph of end of day balances from 1/3/12 to 31/3/12 that also is relatively simple.

Moving over to Access, if all we want to do at any time is to know how much is in the betting bank at that time, that's a simple thing to do. If, however, we want to be able to see the bank account balance over say a month's period then that becomes a little more tricky and there are a number of ways you might approach it.

You might well think that we could just replicate the four columns I described above into an Access table, and you'd be right. However the two Balance columns are calculated values, whereas the Date and Credit/Debit figures are hard entities. Good practice in database design and implementation requires calculated fields not to be stored as part of the data - the reason - as far as I know - relating once again to storage space. Having said that I note that Access 2010 apparently now does include the capability to store calculated fields in a table but I've not explored any limitations to this. We could use this route but I'm not going to!

If you stop to think for a second, we are already recording the Profit / Loss from our trading activities in the Trades table. In my opinion this is absolutely the right place to do this... the P/L is independent of last night's balance in a data sense (obviously there is a dependency - if last night's balance was £2.50 today's P/L will not be brilliant unless we can lay a 1.01 shot for £2.50 liability AND win it!). The PL is completely dependent on the trade and the stake used. Let's face it, the main thrust of this exercise is to track our trading activity rather than track a betting bank balance. The point is that the P/L shows the trading debits and credits to the betting bank, leaving us to worry about only the Deposits and Withdrawals from said bank. Also note that by recording losing trades as negative P/L figures we don't have to worry about our stakes in a bank sense.

For this model to work we could create a Bank table, with a date column, a credit /debit amount and an ID to track it. The opening bank balance would be deposit #1. From here it is extremely simple to calculate the current bank balance.... Sum of PL from the trades table PLUS sum of Dep/Cred from the Bank table. Easy.

How, though, to find the balance at the end of 15th April, 2012?? It's actually quite easy to work this out logically... we need the balance at the end of April 14th plus any P/L and Cred/Deb on the great day itself.

What about the data needed to build a graph showing the effects of the slings and arrows of outrageous fortune on our bank balance for the month of March, 2012? This is a little more tricky. The balance on the 29th February is esay enough to compute, as is that for the 1st April. Subtracting one from t'other shows how much the bank has grown or shrunk in that month. But we would then need to know how that actually happened day by day....and hence to find some way to represent that graphically. I'm on the case!

I'm conscious that this post hasn't really advanced our cause much, but I think it's worth lies in pointing out that stopping to think about the kind of information you need, before jumping in with both size 12's will help get the design of your db right from the outset.

I'll leave you with another thought.... your current balance is available to you on the Betfair site, and on any software that you might use. Do you really need your Access DB to tell you as well? Or do you just want to know at a glance what your total number of trades is, your total stakes, p/l, winners and losers? Your ROI and your Strike rate?

If the answer is the latter... we've already got it - you just don't know how to extract and present it yet!

You might run a separate bank in your own mind for trading a particular sport or strategy, but one which is 'witihin' your normal account so to speak. As you play around with Access and read on the web about how to query it I'm sure you'd be able to come up with something suitable in the not too distant future to handle three, four or more banks in that way. There's a challenge for you!

Thursday 19 April 2012

Am I green or red, and how great was my return?

We'll make just a couple of minor amendments to the Trades form this time, firstly one to format the PL field to reflect either a profit making trade or a losing trade. Secondly we'll add another text box to the form and use that to show the ROI on a given trade.

Formatting the PL field to reflect a winning or losing trade is simplicity itself, and if you've ever done something similar in Excel you'll find it especially easy. Open the Trades table in 'Design View' and ensure that the 'Design' ribbon is active. Select the PL text box, and select 'Conditional' from the relevant menu group on the ribbon. The easiest way to test if a profit or loss has been made is to test for values greater than or less than 0. You'll now appreciate why I changed the default value of this and the Stake field to zero earlier on - it makes it easier to test this condition. Then simply add the two conditional formats as shown - 't'is a doddle!


The next task, calculating the ROI on a trade is just a little more advanced, but stick with it because the principle can be reused to perform lots of similar tasks with other data elsewhere in our database.

The Trades form is a many faceted tool, but its two main purposes are to display the details of a trade that's already taken place and to enable details of a fresh trade to be entered. It might be that you will enter the details of two or three trades at the start of a session, returning to them later to update the profit / loss made. To show a meaningful ROI the trade obviously needs to have been finalised, there's no sense in showing an ROI on a live trade. Experienced traders will acknowledge that sometimes a scratch trade is an excellent result, and also that not all losing trades are 100% losses so we need to try to cater for all those eventualities.

Again with the form in Design View and the Design ribbon active, place a text box somewhere sensible on your form. Once placed you will see the words 'Unbound' in it - indicating that it is not tied to a field in the DB at present. There will be no user interaction with this box, in fact I'd rather use a label but that makes life too difficult (trust me on that one!). So delete the label that accompanies the text box, change the BorderStyle property to 'Transparent' and on the 'Other' tab on the Property Sheet change TabStop from Yes to No.

Then select the 'Data' tab of the Property Sheet and click on the ellipsis at the far end of the 'Control Source' line. Select 'Build' from the options...


This is where we build the expression used to determine what is displayed in our new text box. In the middle of the three columns you will see the names of all the controls on the trades form and it is simply a case of double clicking to get the relevant control name to appear in the box at the top. You can see from the left hand panel that there are various inbuilt functions available you to use as well. This box is an invaluable part of constructing an Access application so please play around with it to get an idea of how it works - it's a lot less complicated than it might look!

To save you squinting at the picture, the expression we want to end up with in the top section of the Build dialog is:
=IIf([Stakes]<>0,IIf([PL]<>0,"ROI: " & FormatPercent([PL]/[Stakes],2),"ROI: 0%"),"")
Again, I'm sure this will look very familiar to any Excel users - it's the Access version of the infamous 'IF' statement. In a nutshell, if no stakes have been entered there can be no ROI, so "" is displayed - i.e. nothing. Because we set the border property of the box to transparent effectively the box is invisible. If stakes have been entered but there is no entry other than 0 in the PL field then 'ROI: 0%' is displayed - indicating either a scratch trade or that the result has yet to be entered. Otherwise the ROI on the trade is calculated and displayed in a percentage format to 2 decimal places. Both the latter items are displayed on the form, but with no border or lines it just looks like it's placed randomly on the form.

Save your form and run it and it should look something like this:


Notice how the formatting, and, more interestingly, the ROI figures have been carried over into the 'Master' section of the form. If you had 500 trades recorded you can scroll through them at the bottom of this form, stopping to examine the detail of trades where the ROI was particularly good or bad. Useful, eh??

Two more little tasks for the Trade form then we'll leave it alone for a while. I want to show the current date, and also the P/L to date on the form. Thereafter we'll get into some more detailed analysis of the data we're collecting.

Enhancing the interface

Whilst it couldn't be simpler to create the Trades form, the default layout that Access creates isn't particularly pleasing on the eye, with the boxes for data display / entry taking up far too much real estate on the screen for their content. We don't need a long box to contain our stakes and profit / loss for example, so we'll address that now.

Prior to editing the form there is one other modification to make. Open the Trades table in 'Design' view (right click on the table name in the Navigation panel). Ensure the 'Design' ribbon is active and select 'Property Sheet' from the 'Tools' group at top right. Navigate to the Stakes field, and enter 0 (zero) as the Default value; do the same for the PL field. This will ensure that when you select a new / blank record that these two boxes now contain '£0.00' - making the whole thing look a bit more intuitive. Close and save changes.

Now open the Trades form in design view. When Access created the form it used a 'layout' arrangement. If you select the 'Stakes' text box and try to re-size it you'll find that all the boxes on that side of the layout change size as well! Not what we want. So we need to remove all the labels, combo boxes and text boxes from the layout. The simple way to do this is to 'lassoo' all the controls by clicking  and holding the left mouse button just above the first label on the left and then encircling all the controls. When you release the mouse button all controls on the form should have active orangey coloured borders round them. Then select the 'Arrange' ribbon and go to the second group of options from the left and click 'Remove'. This simply frees all the controls from the layout enabling us to re-size and re-position them as we see fit.

The TradeID label and text box are of no interest to us as users whatsoever. They relate to the inner workings of the programme and we don't care about them. So return to the 'Design' ribbon and select 'Property Sheet' from the tools group. If you click on the 'Format' tab of the property sheet you'll notice that the topmost property is named 'Visible'. Make sure that you select the TradeID label and text box and change 'Visible' from 'Yes' to 'No'. Simple!

Now is also an ideal time to change the text in some of the labels, losing the 'ID' from sportID and StratID for example, and to size all the boxes to more realistic dimensions. You might also like to change the 'Text Align' properties for the Stake and PL fields to 'right' - I don't think a form looks natural with money amounts aligned to the left of a container This is also the time to change the form title from the rather uninspiring 'Trades' to something more personal if you wish.

Getting a professional looking interface with everything properly lined up and proportioned is something I haven't the skill set or patience for, but after a bit of playing around I came up with the below as a workable form which I'm reasonably happy with.


Next time we'll look at a couple of minor enhancements to the user experience and add a text box that shows your ROI on the currently selected record.

A user friendly interface

Now we've got the first three tables sorted out we can look at creating a meaningful, logical and useful interface so that we can enter the details of trades, and look at historical trades in detail on the same screen. You won't believe how easy this is to accomplish!

With the Trades table closed, but highlighted in the 'All Tables' bar at the left of the Access workspace, all you need do is click the 'Create' tab on the ribbon and then click 'Split Form'. As if by magic a form apprears on your desk top, and although it wouldn't win any beauty pageants it is amazingly simple to use. The image below shows what it look like:


The form is split into two parts, and this type of form is known as a 'Master/Detail' form. In simple English the 'Master' is the Trades table and the individual records are shown at the bottom of the form. The detail of the currently selected Trade is displayed in all its glory at the top of the form. At the bottom of the form are some 'VCR' buttons to navigate the Trade records. Enter some dummy records and get a feel for it; notice how every time you move to a different trade in the bottom section that the detail section updates to show, unsurprisingly, the details of that particular trade. Also, remember the 'Descriptions' entered when designing the Trades table? Well, they appear in the status bar at the bottom of the Access workspace - highlighted in the picture.

What is happening is that in reality you are looking at two forms. The detail section 'interrogates' the TradeID of the current record and then uses that to pull out the individual fields that make up that record and display it in a form that makes a lot of sense to stupid humans!

Over the next couple of posts we'll look at ways of enhancing that rather austere looking form, and of improving its functionality...to start with we'll look at showing the ROI on the current trade, and later can look at things like diplaying your current Profit / Loss and or Bank Balance and a host of other things.

We'll also take a look at how we can get useful information out of the database in another way - you'll be introduced to the notion of a database 'query' and start looking at some calculations.

Wednesday 18 April 2012

Hooking it up

We now need to construct the Trades table. We'll start with TradeID, make the data type 'Autonumber' and make sure this field is set as the Primary Key (more about which later). Make the second field 'TDate' and set the data type to 'Time/Date'. Unfortunately we can't just call this 'Date' as that is a 'reserved' word meaning it refers directly to something going on under the hood. The third field is 'SportID' with data type Number, fourth is 'Event' with data type 'Text'. Field 5 is 'StratID' / Number, six is 'Stakes' / Currency, no 7 is 'PL' / Currency and last but not least 'Description' / Memo.

Your table in design view should look like this, notice I have added some text to the 'Description' boxes  - this text will appear in the status bar at the bottom of your Access screen it is useful as an aide memoire for example that a losing trade needs to be entered as '-£xxx.yy' in the Trades table etc.


We have now reached the point where we can start wiring some of these component parts together and get a feel for a working piece of software.

At the bottom of the table design screen is the 'Field Properties' section, and as you can see this defaults to the 'General' tab. Click the 'Lookup' tab and you are confronted with a one line chart with either absolutely nothing displaying, or, if you have got the cursor at the top of the screen on wither the 'SportID' or 'StratID' fields you'll see the word 'TextBox'. Click to the immediate right of the 'x' and a drop down appears - select 'Combo Box', and a whole new world of possibilities opens up to us:


The purpose of this screen is to make the data in the underlying data store presentable to humans via the UI (User Interface). If you remember the intention is not to store the word 'Football' for the sport traded in this table, but the number '1' as a reference to it. You might, as an end user, be able to remember a dozen or so such codes but what a fag that would be! With this screen we are going to tell Access to store number 1, but display 'Football'. Click on the highlighted elipsis.


The query screen show above is shown, select the Sport table from the list and select 'Close'. If you then double click on SportID and Sport (IN THAT ORDER!!!) they will appear on the grid. At that point click the 'x' to close the grid and you'll see the screen pictured above. Click 'Yes'. The resultant screen is shown below:


This is all a lot simpler than you might be thinking, I promise you! Taking the highlighted lines one at a time...

"SELECT Sports.SportID,Sports.Sport FROM Sports" is what is known as an SQL statement and simply tells Access to pull every sportID and every Sport out of the Sports table. The second line, Bound Column 1 tells Access to  store the value of the first column (i.e. SportID) in this field of the Trades table. The third line tells Access to display two columns to the end user, whilst the third line tells Access to hide the first column by setting its width to zero and display the second column to a width of 3 cm. In other words you don't see the number (1 in this case) but you do see the content of the second column (Football) in this case.

To show you how that appears in real life, here's my fledgling Trades table at this stage (with some dummy data obviously!):


This clearly shows the relational model in proper action and in an unhooked up state in the same shot. Here we can see and understand which sports are traded, even if which strategy is being adopted is a mystery unless we can remember what StratID 6 represents.

Effectively you can repeat the above steps to create the look-up for strategies as well, I'll omit the screenshots and jump straight to the query set up screen:


This, you will see, is identical in structure to the Sport one, just with the different table and fields. The end result of the display of the actual underlying data is shown here:

Now we're cooking.

Tuesday 17 April 2012

Enough waffling... let's get stuck in.

I should point out that I am using Access 2007, which afaik was the first version to feature the 'ribbon' principle common to all products in the MS Office range. I have no knowledge of Access 2010 and have more or less forgotten all about whatever came before, although I did cut my teeth on whatever version that was. The key principles, and, I suspect, most of the key features I'll refer to are only slightly different, if at all, in 2010 or the preceding version for that matter.

On opening Access you need to create a blank database and give it a name. I would suggest that you also create a separate folder in your 'My Documents' and call it 'Trading' or similar and store /create your trading DB in there.

The opening screen presents you with a skeletal table named, unsurprisingly, 'Table 1'. The easiest thing to do is to just close this and forget you ever saw it!

As discussed in the last post only three core tables are required to create the kind of information hinted at in the first part of this adventure. So for now we are going to create a 'Trades' table, a 'Sport' table and a 'Strategy' table. In order to better illustrate the purpose of the three tables I'll suggest we actually work backwards, so let's start by creating a 'Strategy' table.

Click on the 'Create' ribbon and then select 'Table Design' and you will be presented with a strange looking blank canvass on which to stretch your DB muscles, with the cursor flashing in the left hand corner of the topmost field name. Type 'StratID' and hit the tab key across to the 'Data Type' column and select 'Autonumber'. With the 'Data Type' column still active, click on the 'Primary Key' menu item and you should see a little key appear to the left of the field name box. Ignore the Description column for this one by tabbing through it to take you to the next Field Name and enter 'Strategy' and 'Text' in the field name and Data Type columns respectively. Your screen should now look like this:


Notice the little key symbol and that I have edited the Field Size in the Strategy field to 60 instead of the 255 the programme defaults to. There is no point in having large field sizes that won't all get used, a point we'll come back to in due course. That is all there is to it! On clicking to close the table you'll be prompted to name it - choose 'Strategy' for now and save your work. Now is a good time to double click on your newly created table and put some data into it. Even if you don't have named strategies as such please humour me and put a couple of trade styles in - even if it's only 'Correct Score Lay' or 'Scalp 0-0' - the idea is to show you possibilities. Notice that as you enter each strategy the StratID is added automatically. Perhaps as the 'Data Type' is 'Autonumber' this won't be too much of a surprise.

Perform a similar exercise to create the Sport table, again  an ID column ('SportID') of Autonumber data type and a field named Sport with Text data type. Again make the SportID field the Primary Key, and set the size attribute of the Sport field to say 50. Your table in design mode before saving should look like this:



Save the table as 'Sport', and again open it up and put the sports you are likely to trade in the table. Again even if you only ever intend to trade Origami please humour me for now and put four or five sports in your Sport table.

We now have two populated data tables and in the next step we'll construct the actual Trades table and start wiring the whole thing up.

Ascii a simple question and you'll getti a simple answer!

In a comment on the last post Fed questions why we need bother with the 'xID' column in a DB table. I'm assuming that he's working on the basis that, taking the Sport table as an example, 'Football' is unique and different from 'Cricket', and 'Cricket' from 'Golf' and so on ad infinitum. The sportID column, therefore, would appear to be redundant.

Fed is right. There is no reason why you can't form this DB with a single column table for Sports, Strategies, Markets Traded or a host of other 'descriptive' tables as long as the individual entries are unique.

There are, however, many key reasons why you shouldn't do this:


  1. Access will work a lot quicker working with a column of numbers
  2. Computers don't store letters as letters - they store them as number using the 'ASCII' coding usually, so an 'A' is stored as ASCII code 65 and 'a' is ASCII 97 - so why complicate things??
  3. Later, when defining relationships in a db these need to based on the same data types - so you can't relate an integer with a textual string for example
  4. Storage reasons - storing a Long Integer autonumber takes 4 bytes of hard disk no matter how big the number gets. Storing the city name 'Ely' requires 3 bytes, whilst if you need to store that Welsh town Llanfair........gogogoch you'd need over 50 bytes of storage! Ok once but what if it crops up 1,000 times in your db - 4,000 bytes for storing a number and over 12 times that needed to store the actual name that many times
  5. Because every book / web article on good database design theory says you shouldn't!


Hopefully that makes it clear.

Boring theory time...

For those of you who are unfamiliar with the concept of relational databases I'll do my best to explain it in simple terms, as an understanding of this is key to getting the best out of the whole exercise.

Below is a representation of a simple trading spreadsheet such as you might already use:


In database terminology this is know as a 'flat field' database. All information is recorded in a single place, and, as can be seen, lots of the same data is repeated. As the sheet grows this duplication gets more and more prevalent and the sheer physical amount of data becomes harder and harder to draw meaningful conclusions from. It's unlikely to affect us sports traders, but a commercial file with millions of data entries would soon need vast amounts of storage space and would be very unwieldy.

The core concept of a relational database is that commonly repeated data is removed into a separate table on an 'enter once, use often' sort of deal. If we look at the data above we can see there are some elements which are common to all trades, and some which are unique to a particular trade. Get this right and we're well on the way!

Each trade takes place on a specific date, and whilst that date is not unique, it is definitely a fixed facet of that trade. The same is true of the stakes used, the profit / loss resulting and any notes you might make. Conversely all trades involve a particular sport, and you might employ a particular named strategy but neither of those are unique to that trade alone. By the way, 'Scatter Gun' and 'Lazy Lamb' are actual trading strategies - the 'Cassini Catch' and the 'Sultan of Swing Trade' are made up - hopefully those two gents won't think I'm taking their names in vain!

So, what we do is to extrapolate the various sports and strategies into two separate tables:

Each sport and each strategy are assigned an 'ID' number which is unique to each entity. After having done this we can draw up a representation of a particular trade in a 'Trades' table as you can see below:


You don't have to be a rocket scientist to appreciate that to a computer such a table is simplicity itself. Whilst relatively unintelligible to a human looking at it, it is simple to store and to cross refer, index and sort etc for a computer. It also uses far less disk space - instead of there being 2,000 'Scatter Gun' records stored there would be 2000 '1' s etc.

I remember when I first became interested in databases I read something similar to the above and thought 'OK - I see the power of that, but how the **** am I supposed to remember that the Lazy Lamb is StratID 2 and that the X Factor  is represented by SportID 5?'

 Fear not, as all will be revealed in due course.

If you wish to look at the relational model Google is your friend. And enemy. It's a massively involved and complicated topic to the noob, but you can read about the First Normal Form and subsequent 'Normal Forms' if you really want to. I'm sort of OK with this up to the Third Normal Form but thereafter, to be frank, it becomes gobbeldegook to me! Good luck if you try.


Monday 16 April 2012

First question.... 'Which answers do I want?'

A few people have said they would like to explore the Access idea a bit further so I thought I'd give it a go.

Before I delve into constructing a database it is important to have an idea of the sort of information you would like to be able to get out of it. You can fill terabytes of hard disk with data, but if nothing useful comes out of the other end, there really would be little point. Equally, as I said previously, I am no software professional - merely a reasonably competent hobbyist - so I need to be relatively conservative in terms of what I can expect to achieve.

As sports traders, as a minimum, we need to know how many trades we've made and in which sports. What the stakes were, and with what result. I suspect most, if not all serious and semi serious traders have this kind of information in a spreadsheet, but even hobby traders would surely benefit from being able to track their activities. Most would probably like to have some kind of graphical representation of their trading - be it success rate, or to see how their bank is growing (or not!). There are commercially available compiled spreadsheets which will do this for you but I've always found that good as they are they usually don't present the data that I want to see in a manner in which I want to see it and there is little or no flexibility available to the end user.

So, to start this exercise off these are the kinds of reports that I will aim to produce from this simple little Sports  Trading database....

First - a Summary of trading activity... something like this:

Secondly - a summary of the above information broken down by sports... like this:

Lastly, for now anyway, a pictorial representation of the trading bank... like this:

These 3 reports have been knocked up in Excel, and are completely fictitious. The purpose is solely to get an idea of the kind of information we might need to record and to form the basis of outputting that data in a form which is a) useful b) clear and c) flexible.

Over the next few days I'll construct a simple database which will get us to the point of being able to store, view and report on the data shown in the first two images above. Those who are not familiar with databases might be thinking there is little advantage to this over Excel. I would urge you to persevere because I promise you the advantages will become more apparent as time goes by.

In case you're wondering how long it will be before we get to tracking the betting bank... the answer, at the moment, is that I'm not sure. There are several ways to skin that particular cat and I'm still mulling over which way to go, so bear with me!

Sunday 15 April 2012

The Trading Mindset - revisited yet again....

The Sultan has now written three very interesting and pertinent pieces on this difficult subject, and all of them are worthwhile reads. I know from my own experiences that the issues he discusses are very real, and overcoming these issues causes genuine pressures on traders. The chief reason for this is that in order to overcome your issues you first need to accept them for what they are. This has been my own personal bugbear as you know for some time now, but I genuinely feel that progress, although slow, is being made.

There is a load of hogwash on You Tube, and an awful lot great stuff as well. I came across a chap called Rich who runs a horse betting service and he has posted a series of 3 videos which he calls his 'Basic Training' course. Videos 1 and 2 cover Setting Up a Betting Bank and How to Stake respectively, but video 3 is entitled The Mindset of the Betting Pro, and I found it to be ten minutes of very well reasoned advice delivered in a softly spoken, no drama, northern accent. The key lesson I took from it was about how important it is to approach your betting / trading as if it were a business, but having written that sentence I now realise that's actually a very poor synopsis.

Well worth ten minutes of your time in my humble opinion: http://www.youtube.com/watch?v=8Cs1kHX48-w&feature=relmfu

Friday 13 April 2012

Accessing information

I've had a couple of people ask me about using Microsoft Access to record trading since my post mentioning record keeping.

It's taken me a long time to get round to recording my trades...despite the fact that I'm computer literate enough to have done so from day one. At the moment the financial side of my trading is recorded on an Excel spreadsheet as I would guess most do.

In lots of ways Excel is the ideal tool to accomplish this, but, in my opinion is not without its problems. A total of say 100 trades is manageable, but what about a thousand? Or a couple of  hundred each in three or four different sports? Pretty quickly the sheer real estate such a spreadsheet uses on your screen can become unwieldy, and any summary sheets you compile can quickly become a spaghetti junction of references to named cell ranges, or worse 'A35:A35678'. The problem escalates when you think to yourself 'It would be useful to record.. (insert whatever data you would like recorded) ' and then summarise that.

Access makes storing diverse sets of information and pulling together detailed summaries arranged as you wish, (week by week, sport by sport, start odds by profit being some examples) if not exactly simple then at least achievable with a bit of thought and planning.

The way in which the programme stores data is similar to Excel in that the raw data is stored in what looks very much like an Excel worksheet. Where Access comes into its own is that it stores data using what is known as a 'relational model'. To give a simple example of this,  your Excel spreadsheet might have three different tabs, storing exactly the same sort of data for three different sports whereas the same data in Access is stored in two individual tables - one for the trades themselves and one for the sports. These two tables are then 'related' to each other under the hood and allow for relatively easy accessibility to the answers to all sorts of questions.

To cut a long story short I have decided to build an Access database to record my trading - both the note keeping info mentioned in my previous post, and the financial data as well. If there is enough interest I'll happily share the nuts and bolts of building such a beast on the proviso that anyone working through it with me understands that I am NOT a software professional and that the database purists will probably recoil in horror at what they see.

Let me know if you are interested and if there's  a decent number I'll give it a go. At the very least two things will result..

1) You might come out with something useful that you can tailor to your own needs.

And the big one...

2) I won't be short of blog copy for a while :-)

Another crazy game in the Copa

The plan, honestly, was to go to bed. But having settled my trade on the Copa Libertadores match between Olimpia Ascuncion and Emelec shortly after the away side made it 1-1 I shut down Geek's and went off reading the debate about full time trading over at Lamb's place.

I am now going to bed, but first, out of curiosity, glanced at flashscores to see this:


Yes, three goals between the 88th minute and the full time whistle. The last two must have happened too quickly for flash to even grab the scorers' names! I reckon a few small fortunes were made and lost there by the late night trading community.

I love trading the Copa and Latin club football in general. If you've never tried it and are a bit of a night owl I urge you to give it a go.

Tuesday 10 April 2012

The Trader's Mindset revisited

The Sultan promised to look at the question of the Trader's Mindest a couple of days ago, and his recent post does just that. Whilst to be perfectly candid I do not recognise myself in his description of a trader losing his rag in a trade and acting rashly, I do identify with the comments about getting to the root cause of a trader's problems and it still vexes me. Whilst it was disappointing to learn that a night out on the ale won't cure the problem, I've no doubt this is true!


I never used to keep records. None. Nada. And people would ask 'what's your strike rate?' or 'what's your ROI / ROC' or, even worse, 'which match odds give the best returns for the (insert name) trading strategy or tactic?'. And, as I couldn't give a straight answer, I'd mumble something like 'it's around x%' and swiftly talk about something else instead.


That has changed (although it is far too much of a fag to keep updating the results sheets on here, so the observant ones amongst you will notice they've now disappeared). Not only have I a detailed spreadsheet on which I record the financial aspects of my trading I have also knocked up a simple database on Access which enables me to record the thought process of the trade (or the lack thereof!).




Having uploaded the above sample (complete with typos ;-)) I've noticed I've not included the bloody event details on the form - what a muppet! Still a work in progress. I try to complete this process either during or soon after a match ends, whilst it's all still fresh in my mind. That isn't too much of an issue with soccer as I can still be trading other games as things generally happen quite sedately but would obviously be a bit more difficult for nag traders to make use of.


I'd be lying if I told you I hadn't deliberately selected a winning trade for my picture - so for those who have an interest in the kind of mindset issues I still have - here's a not so good one... (the handicap of not including the event is going to need to be fixed! I can't for the life of me remember which match this was):-






I wouldn't worry if this kind or trade were a rarity. But it's not. Don't misunderstand - it's not common but it's there in the plural and this I find intensely frustrating. I am hoping that by keeping such records I can ingrain on my pysche that this form of trading is NOT the way forward. It's a beginner's mistake - so why, oh why, oh why do I keep doing it? I'm not necessarily talking about taking the trade on - I'm talking about letting a losing situation run and run and run instead of cutting and running, reinvesting what's left and moving onto other, profitable, trades.


I really do think this is the last real issue left with my trading. I've been doing it long enough now to have formulated a viable strategy which I know works, and am able to spot other low liability / large upside trades along the way to augment my core activities. All that remains to be done is to eliminate the f'g stupid hare brained glorified punts from my trading and I'll be content.


In case anyone is still wondering whether I would ever consider doing this for a living, I would suggest the answer is contained in the above - you shouldn't have to dig too deep.


A SORRY AFFAIR


Some readers will know Robbo, and some will know what has befallen him. For those who don't let's just say that a substantial four figure amount was lifted from his Bf account and frittered away on the arcade site / casino. areas. 


I was sorry to hear about it mate, and know you'll get it back. The rest of us must ensure we safeguard our own accounts with regular password changes etc as, surprise, surprise, there's nothing Bf can do to help.


AND AN UNADULTERATED PLUG


Please download and use the latest edition of A Geek's Toy if you haven't already done so. Anyone trading at all seriously should use software of some sort and this is a strong contender as it is, currently, free. If you're wondering why I feel the need to plug it....well, let's just say it's financially motivated but not by means of affiliate plans! You can find out for yourself if you want to know....

Monday 9 April 2012

The pitfalls of trading by phone

Traditionally a small group of us sit in our local on the last night of The Masters and watch the final dozen or so holes being played out. It's a welcoming sort of local and mine host keeps his bitter well - an important consideration for yours truly who can't abide plastic or chemical beer.

 When I left home in the unseasonal drizzle and chilly wind Lefty was still trading in the mid twos and 8 under par. When I fired up my phone in the pub he was 5 under and trading at 14! Needless to say I greened my lay of him and then backed him. It was a great night's golf and I happily scalped a nice overall green figure across the field.

On closing time we hot footed it to a friend's den, charged our glasses whilst his delightful good lady produced some sausages in freshly warmed bread rolls for us, and watched the last couple of holes. When Bubba sprayed his tee shot into the trees on the second play off hole the limitations of trading by mobile phone became apparent. I wanted to lay Ooostie - available at 1.43 - but the refresh delay meant he'd taken his shot before my lay was matched and he too appeared to have found trouble off the tee. Convinced they'd both take five and that there would be at least one more play off hole I left it alone. Doh! Had I been trading indoors with my normal internet connection and specialist software that lay would have been matched - but the extra 'lost' profit is the price to be paid for a great night in good company watching exciting golf.

I've got to take my hat off to Mr Watson - that shot out of a narrow gap in the trees will, I'm sure, be talked about for a long time and although Ooostie very nearly made his par put I think most golfers would agree that 'Blubbing Bubba' deserved his green jacket.

Sunday 8 April 2012

Not sure if this is the right thing to do, but I've done it!

With 18 holes of (hopefully) scintillating golf left to play there is only one winner of the Masters as far as the Betfair markets are concerned:-


and the Leaderboard looks like this after 3 rounds:-


Anyone who follows golf will know that the Augusta National is a potential banana skin of a golf course on the last day of The Masters - just ask Mr McIlroy! They call the third round 'Moving Day' and so it was for our friend Mickelson, a quite fantastic eagle and a well taken birdie at the 18th moved him into what proved to be the last pairing for today's round.

Mathematically he is reckoned to have a 36% chance of winning by the odds available, and I personally think that's a price to lay at. I'm not suggesting that he won't win the tournament... merely that he will almost certainly trade higher at some point during proceedings.

As a small punt I'm having a fiver on McIlroy to finish in the top 5, 25 at the time of writing. This is being a bit silly I know, but I did make a shed load by laying him for a top five position at this stage last year when he was 4 shots clear so I regard this as a 'self funding' punt!

Thursday 5 April 2012

If you are struggling with your trading, read this

Now first off I'm not referring to this post! I wouldn't be that conceited -  I mean this one by The Sultan.

This guy has brought himself back from the edge and completely changed the way in which he trades. I've got some thoughts of my own on this and will return to the topic when they've been properly formulated. In the meantime there's a lot of wisdom in Sultan's words and I'm sure his experiences will resonate with lots of traders, from newbies to wizened old hacks like me.

Sunday 1 April 2012

An idea for an (almost) risk free trade.

I've written before about the way in which the Any Unquoted scoreline massively over reacts to a goal, and, given the right match here is a relatively risk free way to take advantage of it. I used this method successfully with Barca last night. At kick off I laid AU at 3.35 for £50, and a slow start before the expected goal rush enabled me to back it at 5.1 for the same sum, leaving me with £87.50 on AU and nothing on the other 16 scores. When Barca took the lead I was able to lay it at 2.8 ish using that £87.50 as my liability - so in other words scratch on AU and £40 odd the other sixteen scores. With no second goal I then backed AU again for the £87.50 and repeated the cycle at 2-0.

The only liability in this trade was at the time of the first lay. The other twice I was just basically recycling the same money giving me a risk free trade. The beauty of it is, of course, that you can use your judgement as to whether to let it run with the green on AU or to have AU as the scratch and the green on the other scores. You might even spread it if the game is 3-0 and could go either way. If no fist goal is forthcoming then at some point you can lay some of the money off, and likewise if it races to 4-0 you've lost nothing as long as you were sensible with the first lay.