Finance 480
Assignment 1
Due: February 26, 2020
Question 1
You are a new agency trader who is expecting to receive large institutional orders for Apple shares
(symbol: AAPL) over the next few days. It is currently the end of the business day on March 2nd, 2005
and you would like to retrieve some information about AAPL. You have data consisting of all AAPL trades
from the current business day. This dataset can be found in the Assignment 1 folder on Blackboard.
(Note: A hard copy of your solution to this question must be submitted in class on the deadline date.)
First, you must clean the data. Trades that are not cancelled or corrected appear in the CORR column
and will take on a value of 0, 1, or 2. Any other value indicates that the trade was cancelled or corrected,
and thus should be deleted from the dataset. In addition, you should delete trades with unusual
conditions, which are reported in the COND column with the following possible values indicating
unusual conditions: O, Z, B, T, L, G, W, J, and K. The EX column indicates the exchange on which the
trade took place; no trades need to be deleted based on this information. (Also ignore the G127
column.) You should also delete trades that occurred before 9:30:00 or after 16:00:00.
a) Create a bar graph that indicates the total number of shares traded within each 30-minute
period of the day, starting with 9:30:00 to 9:59:59. (For the last half hour of the trading day,
include any trades that take place on 16:00:00 in the calculation.)
b) Report the total number of shares traded within each half-hour period, the total number of
shares traded for the day, and the total dollar value of all the shares traded for the day.
c) What is the VWAP for the entire day?
d) The time-weighted average price (TWAP) is also occasionally used as a benchmark for traders
who would like to trade a fixed number of shares in each time period. The TWAP can be
calculated by obtaining the price at the end of each thirty minute period during that day and
then taking the average of these prices. What is the TWAP for the entire day?
e) If you receive an order to buy 250,000 shares of AAPL the following day, how many shares
should you buy in each 30-minute time bin, based on your answer in (a)?
Question 2
Refer to Example 3 on slides 55 to 57 of the Week 4 lecture slides. In that example, the probability of an
upward or downward stock price movement was 50 percent. Make the additional assumption that if the
stock price goes up twice in a row, the probability of a third upward stock price movement is 30 percent
(and thus the probability of a downward price movement is 70 percent). Also assume that if the stock
price goes down twice in a row, the probability of a third downward stock price movement is 30 percent
(and thus the probability of an upward price movement is 70 percent). On average, what price do we
pay if we submit a limit order to buy shares at $9.99 per share? Is this preferable to submitting a market
order to buy shares right now at $10.00 per share? (Note: A hard copy of your solution to this question
must be submitted in class on the deadline date.)
Question 3
This question is based on the Agency Trading 1 case study. Create a Visual Basic (VBA) algorithm that will
automatically purchase 100,000 shares over the course of the trading day. The shares must be
purchased in a way that is consistent with the trading volume pattern provided in the case. This case will
be running all week at port 25001, and you can access the case to make sure that your program will run
successfully. (Note: your solution to this question must be submitted via e-mail as a spreadsheet with
macros (i.e. an xlsm file) by the deadline date. Also make sure that the workings in your spreadsheet and
VBA code are clear and easy to follow.)
Help for Question 3
The sample spreadsheet in the Week 3 folder of Course Documents has a simple auto-execution program
to help get you started. For additional help about setting up an auto-execution program, refer to the
section titled Algorithmic Trading Example Arbitrage in the Visual Basic API Documentation pdf,
which can be found in the RIT Help Files folder on Blackboard. Finally, several of the points in the Excel
and VBA Notes document in the RIT Help Files folder on Blackboard (particularly the note about the
VLOOKUP command) are also useful for this case. My last suggestion is to keep the VBA code minimal
and let the spreadsheet itself do most of the heavy lifting for this question.
RIT Case Brief AT1
Build 1.05
Agency Trading 1
Its 7:00 AM and youve just come into the office where youre a trader for Jennings Global. The sales
call wrapped up a few minutes ago and the Sales team from your S&T desk has been busily calling
their clients relaying research, trading ideas, and market chatter to the different pension fund and
hedge fund clients.
Based on M&A activity from the past 6 months, your firm is recommending that their clients should
buy shares of TNX Corp. due to attractive valuation and the possibility of a takeover offer coming in
the next few months. You are expecting to get a fair number of orders to purchase shares for your
clients this morning.
In order to prepare for this, youve decided to pull up the historical intra-day trading volumes for
the stock over the past three weeks. Summarizing and averaging the data into 10 minute segments,
you generate the following graph:
1,200,000
Intraday Average Trading Volume for TNX
(10 minute intervals)
1,000,000
800,000
600,000
400,000
200,000
0
9:40
10:40
11:40
12:40
13:40
14:40
15:40
Average daily volume: 12.36 million
Excel Data Available in the RIT under the module Case Files
http://rit.rotman.utoronto.ca/cases.asp
Kevin Mak* and Tom McCurdy** prepared this case for the RIT market simulation platform, http://rit.rotman.utoronto.ca/.
*Manager of the Financial Research and Trading Lab, Rotman School of Management;
**Professor of Finance and Founding Director of the FRTL, Rotman School of Management, University of Toronto.
Copyright © 2014, Rotman School of Management. No part of this publication may be reproduced, stored in a retrieval
system, used in a spreadsheet, or transmitted in any form or by any means electronic, mechanical, photocopying, recording
or otherwise without the permission of Rotman School of Management.
Its 8:15 AM and your sales team has concluded some very productive calls. Theyve got client
orders to purchase a total of 100,000 shares of TNX Corp. The instructions are to purchase shares
throughout the day and your clients will measure your execution skill based on the fill 1 that you
assign them versus the market Volume-Weighted Average Price (VWAP) of TNX over that day.
Agency Trading Simulation #1 AT1
Using the Rotman Interactive Trader (RIT) software, accumulate a position of 100,000 shares of
TNX Corp. using only market orders. Your goal is that the VWAP of your position is as close as
possible to the market VWAP for the day. The trading simulation is designed such that an entire day
of trading will occur over a 5 minute (real-time) trading period.
TNX shares will begin trading at a price of $25.00. Each time the 5-minute case is run, a new
random-walk will be generated. During the simulation, programmed liquidity traders (ANON) will
buy and sell shares to force the market clearing price to approximately follow that path. Your
transactions will have a small effect on the market price, but the relative size of the ANON trades
will be very large relative to those from human traders. That is, the latter are participating in a very
liquid market so that, in this case, their trades just have a small effect on the market clearing prices.
Important Note
Please note that the portfolio window on the RIT Client shows both the market VWAP and your
VWAP. The market VWAP is reported in the second column from the right; while your VWAP will be
shown in the Cost column. For example, in the screenshot below the market VWAP is $25.16
(VWAP column) while your VWAP is $25.25 (Cost column).
Discussion Questions and Follow Up:
(1) How does your VWAP compare to the market VWAP? What is the optimal strategy to
employ so that the two VWAPs are close?
(2) What if you were to buy all 100,000 shares in the first minute? What is the likelihood that
your VWAP would be near the market VWAP for the day? Assuming the market follows a
random walk that does not trend up or down, what is the likelihood that you will be lower
(or higher) than the market VWAP over the day.
(3) How will your hedge fund and pension fund clients react when you report their fills as being
better or worse than the market VWAP?
(4) Trade the same simulation but this time, purchase shares only by submitting limit orders
into the limit order book. What are the differences between using limit orders versus using
market orders?
(5) A simpler execution strategy is called a Time-Weighted Average Price TWAP which
involves buying an even number of shares at even intervals (i.e. 5000 shares every 15
seconds). What is the disadvantage of a TWAP vs VWAP order?
A fill is the number of shares, and average price of those shares, that an agency trader executed for their client. i.e. I
spent the whole day buying shares for you, your fill is 1 million shares at an average price of $24.87
1
Copyright © 2014, Rotman School of Management.
2
Program
Ticker
Time Remaining
rit2.rtd
HAR
#N/A
1
2
3
4
5
P&L
Position
AGBID
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
AGBSZ
#N/A
#N/A
#N/A
#N/A
#N/A
AGASK
#N/A
#N/A
#N/A
#N/A
#N/A
Last Price
Volume
AGASZ
#N/A
#N/A
#N/A
#N/A
#N/A
Market Sell Size for Button
29
Purchase answer to see full
attachment
