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