$9$:$15$ C

Back WRDS Project$\_$ACC $315\_2024$

$1\mathrm{.}2$ Extract industry level ratios $(5$ pts$)$

Extract industry level ratios from here: Compustat $-$ Capital

IQ$/$North America$/$Financial Ratios$/$Financial Ratios Industry Level by WRDS $($Beta$).$

The steps are similar as extracting firm level data, except for:

Step $2$: GICS $10$ Sectors.

Step $4$: Median.

$1\mathrm{.}3$ Extract firm$-$industry data $(5$ pts$)$

Extract the GIC sector data for your company from here:

Compustat $-$ Capital IQ$/$North America$/$Fundamentals Quarterly Please ignore the screening variables as they do not affect your analysis for this project.

Use the Ticker Symbol to extract GIC Sectors $($gsector$)$ code for your company.

Search online to match the GIC industry code to the textual industry categories from the industry ratios table.

$1\mathrm{.}4$ Merge firm level ratios with industry level ratios $(15$ pts$)$

Please import three tables to the same Excel file $($please name your file this way: FirmName$\_$YourName; then create a merged table with quarterly firm level ratios and corresponding industry level ratios of the same period. To reduce your workload of merging, this merged table includes only the $1$ st variable $($in green$)$ of each type of variables.

Hint:

Please use data from $1\mathrm{.}3$ to check the sector of your company.

Then filter out other sectors in the data you get from $1\mathrm{.}2,$ copy and paste ratios for this one sector from filtered $1\mathrm{.}2$ to a new Excel worksheet.

Then use the VLOOKUP function to merge ratios into one table $($use the date of ratios as the lookup value$).$

Question: Answer with words $(5$ pts$)$

Look at your firm level ratios table. Some ratios change every month, while others usually stay the same for $3$ months. Why?

Data Transforming

In the merged table, create a column "Holiday" with value of $1$ if that quarter includes the month of December. $(5$ pts$)$ Hint:

If a fiscal quarter ends at the end of a month, extract the calendar month from the date by using the MONTH function.

Then use the IF function to return value of $1$ if the value of month meets certain condition.

Question: Answer with words $(10$ pts$)$

Companies' fiscal quarter ends usually fall at the end of a month, but it is not always the case. If we consider the possibility that the fiscal quarter could end on any day, how would it complicate the data transforming process of identifying a holiday quarter our data