We can create a stock trading simulation application in Excel VBA 365(Any version of MS Excel will do).

For stock trading, we need to consider the following data:

• Asking Price- Price offered by the sellers
• Bidding Price- Price offered by the buyers
• Selling Quantity-Total number of shares available for sale on the stock market
• Buying Quantity- Total number of shares the buyers bid on the stock market
• Last Done Price- The price for the last transaction
• Order Price – Price bid by the trader to buy or to sell
• Order Quantity- Number of Shares ordered by the trader
• Average Price- Average share price
• Buy Value – Average value of shares paid by the trader
• Gross Market Value- The current market of shares owned by the trader
• The total number of shares in the hand of the trader.
• Profit or Loss

We shall use the following variables to represent the data:

• BD- Bidding Price
• SQ- Selling Quantity
• LP- Last Done Price
• OP- Order Price
• OQ- Order Quantity
• AVP- Average Price
• MV- Gross Market Value
• TQ- Total Number of Shares in Hand

In this example, we need to design the UI on the Worksheet, as shown in the following figure. We insert three command buttons, one to open the market, another one to close the market and one more to submit the order. Also insert two option buttons for the user to select buy or sell.

We need to use the Do Loop procedure and the DoEvents command to generate various random values and present them on several cells on the spreadsheet. The values arr the Asking Price, the Bidding Price, the Selling Quantity, the Buying Quantity and the Last Done Price.

We can control the prices change interval by setting the incremental value of x .

We declare the variables in the General section , as follows:

```Dim ranNum1, ranNum2, randNum3, ranNum4 As Double
Dim AP As Single
Dim SQ As Integer
Dim BD As Single
Dim BQ As Integer
Dim OQ As Long
Dim OP As Single
Dim LP As Single
Dim LVOL As Integer
Dim AVP As Single
Dim TQ As Long
Dim BV As Single
Dim MV As Single
Dim PL As Single

```

To set the initial values when we start the worksheet, we use the following code:

```Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Opt_Sell.Value = False
ranNum1 = Rnd * 2 + 3
ranNum2 = Rnd * 2 + 3
ranNum3 = Rnd * 2 + 3
ranNum4 = Rnd * 2 + 3
AVP = Round(ranNum1, 3) * 5 ' Average Price
TQ = Int(Round(Rnd(), 2) * 10000) + 1000 'Total Shares at Hand
BV = AVP * TQ 'Buy Value
AP = ranNum2 * 5 'Asking Price
SQ = Int(Round(Rnd, 2) * 10000) + 1000
BD = Round(ranNum2, 3) * 5 'Bidding Price
BQ = Int(Round(Rnd, 2) * 10000) + 1000
LP = Round(ranNum2, 3) * 5 'Last Done Price
MV = LP * TQ
PL = MV - BV
LVOL = Int(Round(Rnd, 2) * 10000) + 1000 'Last Done Volume
sheet1.Cells(5, 2) = Format(AP, "#,##.00")
sheet1.Cells(5, 3) = SQ
sheet1.Cells(5, 4) = Format(BD, "#,##.00")
sheet1.Cells(5, 5) = Format(BQ, "#,##.00")
sheet1.Cells(11, 4) = Format(OP, "#,##.00")
sheet1.Cells(16, 2) = Format(AVP, "#,##.00")
sheet1.Cells(16, 3) = Format(TQ, "#,##.00")
sheet1.Cells(16, 5) = Format(MV, "#,##.00")
sheet1.Cells(16, 6) = Format(PL, "#,##.00")
sheet1.Cells(5, 6) = Format(LP, "#,##.00")
sheet1.Cells(16, 4) = Format(BV, "#,##.00")
sheet1.Cells(5, 7) = LVOL

End Sub
```

The code to open the market is as follows:

```Private Sub Cmd_Open_Click()
Dim x As Single
x = 0

Do
x = x + 50

ranNum1 = Rnd * 2 + 3
ranNum2 = Rnd * 2 + 3
ranNum3 = Rnd * 2 + 3
AP = ranNum1 * 5
SQ = Int(Rnd * 10000) + 1000
BD = ranNum2 * 5
BQ = Int(Rnd * 10000) + 1000
LP = ranNum3 * 5 'Last Done Price
MV = LP * TQ ' Market Value
PL = MV - BV
LVOL = Int(Round(Rnd(), 2) * 10000) + 1000 'Last Done Volume
sheet1.Cells(5, 2) = Format(AP, "#,##.00")
sheet1.Cells(5, 3) = Format(SQ, "#,##.00")
sheet1.Cells(5, 4) = Format(BD, "#,##.00")
sheet1.Cells(5, 5) = Format(BQ, "#,##.00")
sheet1.Cells(5, 6) = Format(LP, "#,##.00")
sheet1.Cells(5, 7) = LVOL
sheet1.Cells(16, 5) = Format(MV, "#,##.00")
sheet1.Cells(16, 6) = Format(PL, "#,##.00")

DoEvents

Loop

End Sub
```

The Code to Submit the Order

```Private Sub Cmd_Submit_Click()
OQ = sheet1.Cells(9, 4)
OP = sheet1.Cells(11, 4)
LP = sheet1.Cells(5, 6)
If Opt_Buy.Value = True And BV >= 1000 Then
AVP = (AVP * TQ + OP * OQ) / (TQ + OQ)
TQ = TQ + OQ
sheet1.Cells(16, 3) = TQ
MV = LP * TQ
BV = TQ * AVP
PL = MV - BV
sheet1.Cells(5, 6) = Format(LP, "#,##.00")
sheet1.Cells(16, 5) = Format(MV, "#,##.00")
sheet1.Cells(16, 4) = Format(BV, "#,##.00")
sheet1.Cells(16, 6) = Format(PL, "#,##.00")
sheet1.Cells(16, 2) = Format(AVP, "#,##.00")
ElseIf Opt_Buy.Value = True And BV < 1000 Then MsgBox ("You don't have enough fund to buy, reduce order")
End If
If Opt_Sell.Value = True And TQ >= OQ Then
AVP = (AVP * TQ + OP * OQ) / (TQ + OQ)
TQ = TQ - OQ
sheet1.Cells(16, 3) = TQ
MV = LP * TQ
BV = TQ * AVP
PL = MV - BV
sheet1.Cells(16, 5) = Format(MV, "#,##.00")
sheet1.Cells(16, 4) = Format(BV, "#,##.00")
sheet1.Cells(16, 6) = Format(PL, "#,##.00")
sheet1.Cells(16, 2) = Format(AVP, "#,##.00")

ElseIf Opt_Sell.Value = True And TQ <OQ Then
MsgBox ("Not enough shares, reduce order")
End If
End Sub
```

The code to close the market:

```Private Sub Cmd_Close_Click()
Cancel = True
End

End Sub
```