Kevin Ngo's Blog | FMWC – Connect 4 case

This year, I'm participating in the Financial Modeling World Cup (FMWC). I'm working through old cases as practice and wanted to share my learnings. The first one's called "Connect 4".

You can download the case here.

*"Connect 4" challenge from Financial Modeling World Cup*

Instructions (I only did Part A):

Part A Task – Games 1 to 10: Assume Red always moves first. You are given a sequence of 42 moves for each game, but the game will stop when someone connects 4 pieces. For each game, determine:
who won
the number of pieces played,
the column of the final piece,
and the row of the final piece.

Game	Sequence of Moves
Game 1	5, 3, 3, 3, 7, 2, 2, 5, 2, 6, 5, 7, 7, 6, 3, 4, 4, 6, 4, 3, 5, 1, 3, 7, 7, 5, 1, 4, 2, 6, 1, 1, 1, 2, 7, 6, 2, 1, 6, 4, 5, 4
Game 2	4, 6, 4, 7, 2, 5, 2, 3, 2, 6, 7, 1, 4, 3, 6, 7, 2, 7, 5, 3, 7, 5, 3, 4, 1, 2, 1, 5, 7, 1, 6, 5, 3, 6, 1, 5, 2, 6, 3, 4, 1, 4
Game 3	5, 5, 4, 6, 3, 1, 7, 5, 6, 5, 1, 4, 4, 5, 2, 3, 6, 7, 7, 2, 2, 6, 2, 6, 4, 5, 1, 1, 2, 7, 2, 7, 7, 6, 1, 1, 3, 4, 3, 3, 4, 3

And so on...

The model that I ended up building:

The model I built takes in the game sequence as an input and then calculates the winner, the number of pieces played, and the final piece's column and row. It also displays a simulated game board. The board shows the numbered pieces played by each player and highlights the winning pieces in a darker shade of red or yellow.

To use it, you type in the game number from 1 to 10, then press "Play", which triggers a VBA script that I coded.

The VBA script and how it works:

Visual Basic for Apps (VBA) is a language used for writing Excel scripts. Here's how my script works at a high level.

Subroutine: FillBoard()

Sub FillBoard()
    Dim ws As Worksheet
    Dim moves As Variant
    Dim row As Integer, col As Integer
    Dim i As Integer
    Dim currentPlayer As Integer
    Dim winner As String
    Dim winnerCol As Integer
    Dim winnerRow As String
    Dim moveCount As Integer
    Dim winDetected As Boolean
   
	' Define the worksheet and the range for the board
    Set ws = ThisWorkbook.Sheets("Work")
    
    ' To read the sequence from C3
    moves = Split(ws.Range("C3").Value, ",")

The code first sets the worksheet (ws) and reads the sequence of moves from cell C3 in a comma-separated format. These moves correspond to column numbers in the game.

Reset the Board

	ws.Range("B9:H14").ClearContents
	ws.Range("B9:H14").Interior.ColorIndex = xlNone

Clears the game board by removing all content and resetting cell colors within the grid (B9:H14).

Processing Moves

    ' Initialize variables
    winner = ""
    winDetected = False
    
    ' Loop through the sequence of moves
    For i = LBound(moves) To UBound(moves)
        col = CInt(moves(i)) ' Convert the move to an integer (column number)
        currentPlayer = (i Mod 2) + 1 ' Alternate players (1 = Red, 2 = Yellow)
        
        ' Find the lowest empty row in the selected column
        row = 14
        Do While row >= 9 And ws.Cells(row, col + 1).Interior.ColorIndex <> xlNone
            row = row - 1
        Loop

The loop processes each move, alternating players between Red and Yellow. It looks for the next available row in the selected column (col). The Do While loop checks from the bottom row up (row = 14) to find the lowest empty cell in that column.

Placing the Piece

        ' Fill the cell with the move number and color
        If currentPlayer = 1 Then
            ws.Cells(row, col + 1).Interior.color = RGB(255, 182, 193) ' Red
        Else
            ws.Cells(row, col + 1).Interior.color = RGB(255, 255, 153) ' Yellow
        End If
        ws.Cells(row, col + 1).Value = i + 1 ' Place the move number in the cell

After each move, the script calls the CheckForWinner function to see if a player has won. If a winner is detected, the loop stops, and the winner, the column, and the row of the winning move are recorded.

Handling Draws and Outputting Results

   ' If no winner is detected and all moves are exhausted, it's a draw
    If Not winDetected Then
        winner = "Draw"
        moveCount = UBound(moves) + 1
        winnerCol = col
        winnerRow = Chr(65 + (14 - row)) ' Convert row number to letter (A = bottom row)
    End If
    
    ' Output the results
    ws.Range("B6").Value = winner
    ws.Range("C6").Value = moveCount
    ws.Range("D6").Value = winnerCol
    ws.Range("E6").Value = winnerRow
End Sub

If no winner is detected after all moves, the game is declared a draw. The results are output to specific cells in the Excel sheet, showing the winner, number of moves, final column, and final row.

Function: CheckForWinner()

This function checks if a player has won by connecting four pieces in a row, either horizontally, vertically, or diagonally. For example, to check vertical wins:

    ' Check vertical
    count = 0
    cellIndex = 1
    For i = 9 To 14 ' Match the board's row range
        If ws.Cells(i, col).Interior.color = color Then
            count = count + 1
            Set winningCells(cellIndex) = ws.Cells(i, col)
            cellIndex = cellIndex + 1
            If count = 4 Then
                CheckForWinner = True
                RecolorWinningCells winningCells, player
                Exit Function
            End If
        Else
            count = 0
            cellIndex = 1
        End If
    Next i

This loop checks vertically, looking for four consecutive pieces in the same column.

Summary

The script manages a Connect 4 game by alternating player moves, placing pieces in the grid, and checking for a winner after each move.
It handles win conditions in all possible directions (horizontal, vertical, and diagonal) and provides visual feedback by coloring the board and highlighting winning pieces.
Results, such as the winner and the number of moves, are displayed on the worksheet.