1 BFC5936 - Final Assessment Bid-Ask Spread and Stock Liquidity Heidi Quah, PhD 2021 semester 2 The bid-ask spread (BAS) is widely used as a measure of stock liquidity and information asymmetry. Prior studies show that stock liquidity plays an important role in financial decisions and asset pricing. For the purpose of this assessment, BAS is computed as the yearly average of weekly closing bid-ask spread scaled by the mean of weekly closing bid and closing ask prices. The dataset consists of 3843 cases with twenty US public listed firms over the period 2017– 2020. A range of cells named “Inputdata” in an accompanying spreadsheet contains the raw data.1 You are required to either fix the VBA codes provided or create one if it is not provided to complete the given tasks. You cannot rename any of the subroutines or functions. Task 1 Your first task (Task 1.1) is to construct the yearly BAS metric from the raw data using the following formula and conditions: = 1!,#+ !,#,$ − !,#,$(!,#,$ + !,#,$)/2 %&&'(!,#$)* where Weeksi,t is the number of observations for stock i in year t, and Aski,t,w and Bidi,t,w are the closing ask and bid prices of the stock i on Week w of year t, respectively. To eliminate the outliers, you need to exclude: (1) weekly negative bid-ask spreads (crossed quotes) for which the ask price is smaller than the bid price; or (2) weekly bid-ask spread that is greater than 50 percent of the quote midpoint for which the bid-ask spread is unreasonably larger than the mean of ask and bid price; or (3) stocks that have less than forty weekly bid-ask spread during year t. Finally, multiply the resulting value of Bid-Ask spread (BAS) by -100 so that higher values for BAS reflect higher stock liquidity. Improve the buggy VBA function provided and then calculate and populate yearly BAS measure to “YearBAS” cells. If the condition (3) is true, replace the cell with the word “n/a”. Also, you need to consider other data issues that are not mentioned during the data cleaning phase. In Task 1.2, you are required to populate week number (within a year) for each firm in the week column of the “Inputdata” dataset. 1 In the dataset, ticker symbol is an unique identifier assigned to each security traded on the US markets. 2 Task 2 In this section, you need to complete two subtasks. In Task 2.1, fix and improve the VBA subroutine and then use it to transpose yearly BAS from “YearBAS” cells to “Transposed” cells. In Task 2.2, sort firms into two groups based on the yearly BAS in each year. The top group includes firms with higher liquidity and the bottom group includes firms with lower liquidity. Label the top group as “high liquid stock” and bottom group as “low liquid stock” for each year. Name the cell as “n/a” if there is no yearly BAS value in that year, and hence it should be logically excluded from the ranking of yearly BAS. Again, an incomplete VBA function is provided. Improve it before populating the results to “Ranked” cells. Task 3 Between March 3 and March 12, 2020, the US stock markets declined between 7% and 10% from its closing price the day before due to the Coronavirus fear. In this task, the aim is to explore the extent to which the level of stock liquidity responds to COVID-19 pandemic by examining the magnitude of change in liquidity around the event. We consider week 10 in 2020 as the event day. 1. First, compute weekly BAS over week 1 to week 10 in 2020 from the data in “Inputdata” cells and populate the results to “COVID” cells. A buggy subroutine is provided. 2. Second, compute weekly change BAS over week 2 to week 10 in 2020. ℎ = # − #+*#+* Create a subroutine to calculate this measure from the data in “COVID” cells and populate the results to “Changed” cells. 3. Then, compute the median change BAS in week 10, 2020. Classify firms as “greater liquidity decrement” if the change BAS is greater than or equal to median change BAS, and as “lower liquidity decrement” if otherwise. Label firms as “liquidity increment” if their liquidity increases during the event. Improve the codes provided before populating the results and other related information generated in the previous tasks to “magChanged” cells as shown below: Ticker Liquidity in 2019 (Category) Change liquidity in week 10, 2020 (the event day) Decrement during the event day (Category) ABC3 BFGK CUIP 4. All the best!!! low or high liquid stock numeric values greater or lower liquidity decrement; or liquidity increment 3 Appendix Partial answers are provided for checking your codes. Task 1.1 Year Ticker BAS 2017 AQN -0.26950 2018 AQN -0.12883 Task 2.1 2017 2018 2019 2020 AQN -0.269498588 -0.128833474 ? ? Task 2.2 2017 2018 2019 2020 AQN ? ? high liquid stock ? Task 3.1 Year 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 Week 1 2 3 4 5 6 7 8 9 10 AQN -0.06986 -0.06768 ? ? ? ? ? ? ? ? Task 3.2 Year 2020 2020 2020 2020 2020 2020 2020 2020 2020 Week 2 3 4 5 6 7 8 9 10 AQN -0.03113 -0.0212 ? ? ? ? ? ? ? Task 3.3 Ticker Liquidity in Change liquidity Decrement during 2019 (Category) in week 10, 2020 the event day (Category) AQN high liquid stock 0.260038241 ?