$9$:$15$ C

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

Spring $2024$

ACC $315$ Excel Project

Data Extracting and Merging

$1\mathrm{.}1$ Extract firm level ratios $(10$ pts$)$

First, select a company of your interest. Then search the company name through Google search $($or whatever search engine reasonably reliable$)$ to find the corresponding Ticker symbol.

Now use the Ticker symbol as the company code to extract its financial ratios from WRDS: Compustat $-$ Capital IQ$/$North America$/$Financial Ratios$/$Financial Ratios Firm Level by WRDS $($Beta$).$

Hint: You may search the keywords $"$wrds firm ratio" on Youtube for general guidance of the database. But the following instructions are essential.

Instructions

Step $1$: Data range $2010-01-01$ and $2022-12-31$

Step $2$: Apply your company codes

Format of company code: TICKER $($Then type your company's Ticker symbol in the box$)$

Step $3$: Choose query variables

a$)$ Valuation ratios: Price$/$Book$($ptb$)$; P$/$E $($Diluted$,$ Excl. El$)($pe$\_$exi$)$

b$)$ Profitability ratios: Gross Profit Margin $($gpm$)$; Net Profit Margin $($npm$)$

c$)$ Solvency ratios: Total Debt$/$Total Assets $($debt$\_$at$)$; Interest Coverage Ratio $($intcov$\_$ratio$)$

d$)$ Liquidity ratios: Current Ratio $($curr$\_$ratio$)$; Quick Ratio $($quick$\_$ratio$)$

e$)$ Efficiency ratios: Asset Turnover $($at$\_$turn$)$; Payables Turnover $($pay$\_$turn$)$

f$)$ Other ratios: Research and Development$/$Sales $($rd$\_$sale$)$; Avertising expenses$/$Sales $($adv$\_$sale$)$

Step $4$: Select query output

Output format: csv

Compression Type: Uncompressed

Date Format: YYYY$-$MM$-$DD

You can click the "Submit Form" button to submit your query. This webpage will give you a link to the output data file $($You should allow the popup from WRDS$).$

$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 $[$O$/$North America$/$Fundamentals Ouarterly

$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