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.