Step 1: Set Up a Ticking Midmarket Spot

Setting up the framework mainly requires VBA development. User inputs on the sheet are initial spot, time between ticks, and how much spot increments up or down at each tick. Outputs are the current time step and current spot. Control buttons for Go/Pause and Stop are also required:

The input cells should be named as per the screenshot. Naming cells makes development far more flexible than referencing (e.g., cell “A5” from the VBA).

The VBA module should start like this:

Option Explicit
Public MarketOn As Boolean 

The first line forces all variables within the VBA to be declared using Dim statements. This makes the VBA coding more similar to languages like C++ and encourages better programming. The second line defines a global Boolean variable called MarketOn that defines whether the market is currently ticking (MarketOn = True) or not (MarketOn = False).

The GoButton subroutine should run when the Go/Pause button is pressed, flipping the MarketOn variable and initializing the sheet if required:

Sub GoButton1()
    'If market is ticking, stop it. If market is not ticking, start it.
    MarketOn = Not MarketOn
    'If market was previously stopped then initialize it
    If Range("Step") = "" Then
        Range("Step") = 0
        Range("SpotMidMarket") = Range("SpotInitial")
    End If
    'Run a market tick
    MarketTick1
End Sub

The StopButton subroutine clears the outputs and stops the market if the Stop button is pressed:

Sub StopButton1()
    MarketOn = False
    Range("Step").ClearContents
    Range("SpotMidMarket").ClearContents
End Sub 

The MarketTick subroutine updates the market by moving spot up or down by the “SpotIncrement” amount at random on each market tick. Then a future market tick is scheduled. Note how time is converted from seconds terms into day terms for the OnTime function, plus “_” is used when a code statement goes over more than one line:

Sub MarketTick1()
    If (MarketOn) Then
        'Move spot up or down at random
        If (Rnd()> 0.5) Then
            Range("SpotMidMarket") = Range("SpotMidMarket") + _
 	     Range("SpotIncrement")
        Else
            Range("SpotMidMarket") = Range("SpotMidMarket") - _
	     Range("SpotIncrement")
        End If
        'Increment step
        Range("Step") = Range("Step") + 1
        'Schedule a market tick in the future
        Application.OnTime TimeValue(Now() + Range("TickTime") / 24 / _
	 60 / 60), "MarketTick1"
    End If
End Sub 

When the Go/Pause button is first pressed, the market should start ticking with the frequency specified in the cell named “TickTime.” Test different inputs to make sure everything is wired up properly. In particular, check that the Go/Pause button works correctly: Pressing the button while spot is ticking should pause it; then pressing again should restart the ticks.

Step 2: Record and Chart Spot

A time series of spot can now be stored on the sheet. Again, this is primarily achieved with VBA development. Columns can be set up to store the step and spot rate, with the upper-left cell of the output named: “DataOutput.”

Within the VBA, the MarketTick code needs to be extended to push the data onto the sheet. The .Offset command is used to access the appropriate cell within the sheet:

    …
    If (MarketOn) Then
        'Store data
        Range("DataOutput").Offset(Range("Step"), 0) = Range("Step")
        Range("DataOutput").Offset(Range("Step"), 1) = _
	 Range("SpotMidMarket")
    …    

And the StopButton code needs to be extended to clear the stored data:

    …
    'Loop around and clear the spot ticks
    Count = 0
    While Range("DataOutput").Offset(Count, 0) <> ""
        Range("DataOutput").Offset(Count, 0).ClearContents
        Range("DataOutput").Offset(Count, 1).ClearContents
        Count = Count + 1
    Wend
    …

When the simulator is run, spot ticks should be recorded in the table:

Having the ticks stored enables them to be charted. Run the simulator for a few ticks and pause it. Then select cells, starting at the title and running a large number (500ish?) of (currently mostly blank) rows down, including both step and spot columns:

Insert an X-Y Scatter chart with straight lines between points. When the simulator is un-paused, the data should plot with the chart automatically resizing as new data is stored (up to the number of rows originally selected):

Step 1: Set Up a Two-Way Price

Within the sheet a new bid–offer spread input is required, plus bid and offer rates must be output:

These new cells are referenced within the MarketTick VBA subroutine:

      …
      'Calculate bid and offer
      Range("Bid") = Range("SpotMidMarket") - Range("BidOfferSpread") / 2
      Range("Offer") = Range("SpotMidMarket") + Range("BidOfferSpread") / 2
      …

New code also needs to be added to the GoButton and StopButton subroutines. Within GoButton, the initial bid and offer need to be set up, and within StopButton, the bid and offer output cells need to be cleared.

Step 2: Set Up Price-Taking Functionality

In order to risk manage, a trader needs to know their position and their P&L. Within the simulator both position and P&L can be added as outputs and kept updated using VBA code. In addition, at each spot tick, the trader can do one of three actions: nothing, buy (at the market offer), or sell (at the market bid), hence crossing a spread to transact. Controlling these choices could be done in many different ways in Excel but the method implemented here uses Option Buttons from the Form Control menu. These buttons should be grouped so only one of the choices can be selected at a time and the selection is then linked to an output cell that can be referenced within the VBA:

New VBA code needs to update the P&L based on the trader position and the spot move. The trader action then needs to be processed and the position updated if appropriate (and the selection reset back to “Do Nothing”):

Sub MarketTick4()
    Dim SpotIncrement As Double
    If (MarketOn) Then
        'Store data
        Range("DataOutput").Offset(Range("Step"), 0) = Range("Step")
        Range("DataOutput").Offset(Range("Step"), 1) = _
	 Range("SpotMidMarket")
        Range("DataOutput").Offset(Range("Step"), 2) = Range("Position")
        Range("DataOutput").Offset(Range("Step"), 3) = Range("PnL")
        'Calculate spot increment
        If (Rnd()> 0.5) Then
            SpotIncrement = Range("SpotIncrement")
        Else
            SpotIncrement = -Range("SpotIncrement")
        End If
        'Update P&L
        Range("Pnl") = Range("Pnl") + Range("Position") * SpotIncrement
        'Update spot and step
        Range("SpotMidMarket") = Range("SpotMidMarket") + SpotIncrement
        Range("Step") = Range("Step") + 1
        'Calculate bid and offer
        Range("Bid") = Range("SpotMidMarket") - _
	 Range("BidOfferSpread") / 2
        Range("Offer") = Range("SpotMidMarket") + _
	 Range("BidOfferSpread") / 2
        'Process trader action: Buy
        If Range("Action") = 2 Then
            Range("Position") = Range("Position") + 1
            Range("Pnl") = Range("Pnl") - Range("BidOfferSpread") / 2
        End If
        'Process trader action: Sell
        If Range("Action") = 3 Then
            Range("Position") = Range("Position") - 1
            Range("Pnl") = Range("Pnl") - Range("BidOfferSpread") / 2
        End If
        'Reset the trader action
        Range("Action") = 1
        'Schedule a market tick in the future
        Application.OnTime TimeValue(Now() + _
	 Range("TickTime") / 24 / 60 / 60), "MarketTick4"
    End If
End Sub

Again, new code must also be added to the GoButton and StopButton subroutines to set up and clear the data on the sheet as appropriate.

The P&L and position are now also stored on the sheet and can be displayed in automatically updating charts using the same method as the spot chart:

The simulator is now ready to be tested. Check that spot still ticks correctly and the buy/sell controls work as expected. Each time a trade is executed there should be an initial negative P&L impact from spread cross and the trader position should correctly increment up or down. Also, the P&L must update based on market moves.

If everything is happening too quickly, slow it down; five seconds between ticks is fine to start with while the interactions between market, position, and P&L become familiar.

It should become obvious quite quickly that crossing the bid–offer spread to transact makes it difficult to make money within this framework; all transactions result in a negative P&L change so every trade reduces expected P&L. This is an important real-world trading lesson: Don't over-trade when there is spread cross involved. Test different combinations of bid–offer spread and spot increment to observe how their relative size impacts trading behavior and performance.