Empirical Finance
THE TRADE AND QUOTE (TAQ) PROJECT
This is a quick project that can be done in 30 – 45 minutes. Its purpose is:
• To give you a feel for some real trade and quote data.
• To provide hands-on experience calculating key concepts (e.g., NBBO, % Effective Spread, etc.).
• To analyze to what exchange you should route your order to so as to minimize transaction cost.
Your assignment is to analyze several thousand trades in one security on one day. Specifically, there are three things to compute: (1) the NBBO, (2) transaction cost measures, and (3) transaction cost by exchange.
You can analyze TAQ data on any of the following six securities: (1) Alphabet (Google’s parent company), (2) Apple, (3) Cummins, Inc., (4) Eli Lilly & Co., (5) SPDR S&P 500 ETF, and (6) Vanguard Total World Stock ETF. Pick one of them and download the corresponding Excel spreadsheet TAQ Project Alphabet.xlsx, TAQ Project Apple.xlsx, TAQ Project Cummins.xlsx, TAQ Project Lilly.xlsx, TAQ Project SPDR S&P 500 ETF.xlsx, or TAQ Project Vanguard Total World Stock ETF.xlsx from the Files link in Canvas.
(1) Computing The NBBO
The first goal is to compute the National Best Bid and Offer (NBBO). The first step in computing the NBBO is to keep track of the Currently Active Bid and Offer prices that are quoted by each exchange. Here is some Consolidated Quote Data for Tesla. Consider the first six quotes:
The Exchange variable indicates what exchange updated its bid and offer. The exchange codes are:
• A = NYSE AMEX (formerly American)
• B = NASDAQ OMX BX (formerly Boston)
• C = National Stock Exchange (formerly Cincinnati)
• D = Financial Industry Regulatory Authority (FINRA) Trade Reporting Facilities (TRF), where Dark Pools report
• I = International Securities Exchange
• J = CBOE EDGA (formerly DirectEdge A)
• K = CBOE EDGX (formerly DirectEdge X)
• M = Chicago Stock Exchange (formerly Midwest)
• N = New York Stock Exchange (NYSE)
• P = NYSE ARCA (formerly Pacific, also formerly Archipelago)
• S = Consolidated Tape System
• T = NASDAQ trades in NYSE-listed and other-listed securities
• Q = NASDAQ trades in NASDAQ-listed securities
• V = Investors’ Exchange (IEX)
• W = Chicago Board Options Exchange (CBOE),
• X = NASDAQ OMX PSX (formerly Philadelphia) • Y=CBOEBYX
• Z=CBOEBZX
The first quote has a legitimate offer price = $219.88, but NOT legitimate bid price. When the Bid = $0, this means that temporarily there wasn’t any bid price on exchange “P.” By the fourth quote, there are legitimate bid and offer prices.
Now let’s keep track of the Currently Active Bids for all exchanges in columns L – AC below
These are exchange codes: A = NYSE MKT, B = NASDAQ OMX BX, etc.
and the Currently Active Offers for all exchanges are in columns AD – AU below.
In rows 6 – 8, we see the Currently Active Bid price for NYSE ARCA (exchange code = P) updates from $61.11 to $176.11. Similarly, in rows 3 – 8, we see the Currently Active Offer price for NYSE ARCA updates from $219.88 to $206.98. Any bid price that is NOT legitimate, we assign a fake bid price of $0. Any offer price that is NOT legitimate, we assign a fake offer price of $1000. Thus, we know the currently active bid and offer quotes for every exchange at every moment in time.
The next step is to determine the Best Bid and Best Offer. The Best Bid is simply the highest Currently Active Bid by any exchange (in columns K – AB). Enter =MAX(K3:AB3) in cell AU3 and copy it down
the column. The easy way to do this is to hover the cursor over the fill handle (the square in the lower- right corner) of cell AUV3, the cursor turns to a plus symbol as shown below,
and then double-click. This copies the formula in cell AU3 as far down column AU as there are entries in neighboring column AT.
The Best Offer is simply the lowest Currently Active Offer by any exchange (in columns AC – AT). Enter
=MIN(AC3:AT3) in cell AV3 and copy it down the column by hovering the cursor over the fill handle and double-clicking. Now you’ve computed the NBBO!
Code Help
We want to know what the NBBO was at that moment the trade took place. This is done by using the LOOKUP command, which has three arguments: lookup value, lookup range, results range. We want the lookup value to be the reported trade nanoseconds since 4 AM. For the trade in row 3 above, the reported trade time is 89,118,297 nanoseconds after 4:00 AM. So the lookup value is the number of nanoseconds in cell BD3. The lookup range is the Quote Nanoseconds Since 4 AM in Column I. The results range is the Best Bid in column AU. The LOOKUP command will find a time in column I that is less than or equal the Trade Nanoseconds Since 4 AM and then find the corresponding Best Bid in column AV. Enter
=LOOKUP($BD3,$I$3:$I$41237,AU$3:AU$41237)
in cell BE3. The row numbers in the cells $I$41237 and AU$41237 in the formula above should be changed to match the number of quote and NBBO rows in your data file. Copy the formula in cell BE3 to cell BF3, and copy both cells down the column by selecting the range BE3:BF3, hovering the cursor over the fill
handle , and double-clicking.
(2) Transaction Cost Measures
Several transaction cost measures use the NBBO midpoint as the standard benchmark for the current value of the security. The NBBO Midpoint is simply the average of the NBBO Best Bid and Best Offer:
NBBO Midpoint = (Best Bid + Best Offer) / 2
Enter =(BE3+BF3)/2 in cell BG3 and copy down the column by hovering the cursor over the fill handle and double-clicking.
The dollar and percentage quoted spread are defined as follows:
Dollar Quoted Spread = Best Offer – Best Bid
Percent Quoted Spread = Dollar Quoted Spread / Midpoint
Enter =BF3-BE3 in cell BH3, enter =BH3/BG3 in cell BI3 and copy both cells down the column by
selecting the range BH3:BI3, hovering the cursor over the fill handle , and double-clicking. The dollar and percentage effective spread are defined as follows:
Dollar Effective Spread = |Trade Price – Midpoint| * 2
Computer Science Tutoring
Percent Effective Spread = Dollar Effective Spread / Midpoint
Enter =ABS(BC3-BG3)*2 in cell BJ3, enter =BJ3/BG3 in cell BK3, copy both cells down the column by
selecting the range BJ3:BK3, hovering the cursor over the fill handle , and double-clicking.
(3) Calculate Transaction Cost by Exchange
We want to figure out to what exchange to route our order to in order to minimize transaction cost. So far, we have calculated various transaction cost measures (i.e., Percent Quoted Spread and Percent Effective Spread) for each individual trade. But we would like to analyze what the average transaction cost by exchange for our whole sample of several thousan trades. In other words, we want to calculate the average Percent Quoted Spread and the average Percent Effective Spread by exchange.
The easiest way is to use a powerful feature of Excel called the PivotTable. Click on Insert tab, in the Tables group click on PivotTable.
In the Create PivotTable dialog box and in the Select a table or range box, enter a range that starts at AX2 and which goes the last trade in column BK (for the Tesla spreadsheet this range is $AX$2:$BK$2362, but the last row in your BK column will be different) and enter a cell location $BM$3 in the Location box, and click OK.
To create the report we are interested in, we will choose fields to add to the report from the automatically generated list in the top-left. Check the box for Exchange and it is automatically added to Rows area. You will immediately see rows for the various exchanges (B, D, J, etc.) and a grand total. Check the boxes for Percent Quoted Spread, Percent Effective Spread, and Trade Size. They are automatically added to the Values area.
程序代写 CS代考 加微信: cstutorcs
The figure above shows the result at this intermediate step. Excel guessed incorrectly that we wanted to calculate the Sum our variables. We are actually interested in calculating the Average of each of our
variables. To change this, click on the Sum of Perce.. button in the Values area and then click on Value Field Settings on the pop-up menu.
In the Value Field Setting dialog box, select Average and click on OK.
Repeat the procedure for the Percent Effective Spread and Trade Size. That is, click the second Sum of
Perce.. button , click on Value Field Settings, select Average and click on OK. Then
click the Sum of Trade button , click on Value Field Settings, select Average and click on OK.
You can make the output a little easier to interpret with some formatting. Select the range BN4:BO14 and click on the Home tab, in the Number group click on the Percent Style , click three times on Increase Decimal , and click on Center Alignment . Select the range BP4:BP14 and click on the Home tab,
in the Number group click five times on Decrease Decimal , and click on Center Alignment . The final result will look like the figure below.
Interpreting The Results
Now we have the results, we are ready to figure out to what exchange to route our order to in order to minimize transaction cost. To help you with interpreting your results, here are some questions to ponder:
• Do you notice a difference between the Average of Percent Quoted Spread and the Average of Percent Effective Spread for most exchanges? Which of these two measures of execution quality is most relevant for security traders?
• Which exchanges had the lowest Average of Percent Effective Spread for your particular security? Which had the highest? Why would anyone send an order to the exchange with the highest Average of Percent Effective Spread?
• Do you notice a difference in the Average of Trade Size across exchanges? Does this suggest that there is any segmentation by Trade Size? Explain.
• If you were to carry the analysis of your data further, what else might you look at?
Your write-up should be double-spaced, and should not exceed 5 pages. Include a brief executive summary in which you describe your major assumptions and your estimate of the firm’s stock price. Your report must be clear and succinct. In addition to the text, you may add up to 5 pages of exhibits like graphs, tables etc.,