Practical A

Building a Trading Simulator in Excel

This practical demonstrates how simple financial markets work and illustrates the differences between price-taking and price-making roles. Task A sets up a ticking (moving) midmarket price. Task B then introduces a two-way price (bid and offer) around the midmarket and price-taker controls whereby the trader can pay or give the market. Finally, Task C adds the ability for the trader to act as both price taker and price maker. This practical links closely to the material discussed in Chapter 3.

Task A: Set Up a Ticking Market Price

The trading simulator has one main VBA subroutine that updates the market price. The Application.OnTime command is used to pause between market ticks.

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 ...

Get FX Derivatives Trader School now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.