Important Note: Do not submit files that were created during a lecture in the lab. You are supposed to submit files that you have created at home. The purpose is to have as much practice as possible. Use as many comments and examples as possible. Marks are based on the size, examples, and comments inside the submitted assignments.

1. Show the use of VLOOKUP for the following three uses: (4.5 marks)

1.1 Merge two dataset of your own choice (Examples can be merging share prices data with index on the basis of dates)

1.2 Make a data retrieval form (Examples can be Students data, where we retrieve students information based on students registration number)

1.3 Assign quota marks if students belong from different districts (Example: Make a list of students and in second column write their domiciles. Make another list where domiciles have different points, such as Peshawar has 10, Dir has 20, Bannu has 30, etc. Then use vlookup function to assign these scores to students in the first list)

2. Macro Exercise (5.5 Marks)

2.1 Make a macro that copies data from one sheet to another and then deletes the data on the first sheet. Give this macro a short-cut of Ctr + q

2.2 Make a macro that formats a data table in the following way: a. Header row has light grey color b. Header row has bold text c. Header row has upper and lower cell borders d. The last row on the table has bottom cell border

2.3 Search internet to find and use at least 10 useful macros, assign these macros to clickable buttons

3.  Protection : (3 marks)

3.1 Use a separate Excel file, name it “Protected” and make this file password protection so that no one can open it without a password of 123

3.2 Show practice on data entry in one sheet and some formulas on another sheet, using the data from the first sheet. (Example: Account data on one sheet, and Ratios on another sheet)

3.3 Protect the formulas on the second sheet with a password 123, the formula cells should not be clickable

3.4 Make a sheet, name it secret. Hide that sheet and password protect it from unhiding.

4. IF Statements: (4 marks)

4.1 Use at least 5 different examples of IF and nested IF statements.

4.2 Show the use of OR functions with IF

4.3 Show the use of AND function with IF

5. Data Validation (3 Marks)

5.1 Show example of data validation using lists

5.2 Show examples of data validation using whole numbers

5.3 Show examples of data validation using text length

6. Import and Filtering (3 Marks)

6.1 Show practice on importing delimited text

6.2 show practices on filtering records (You may use merit list file from IMSciences website

6.3 Show practices on Pivot Tables

Please note: You can do all these in one excel file or in different files. If one excel file, then given proper names to excel sheets. If different excel files, then zip all files in one folder and upload.

Important Note: Do not submit files that were created during a lecture in the lab. You are supposed to submit files that you have created at home. The purpose is to have as much practice as possible. Use as many comments and examples as possible. Marks are based on the size, examples, and comments inside the submitted assignments.

1. Use data of at least 100 companies (2010-2018). You can download the data
manually or using Python from this page https://opendoors.pk/financials/ or any other site for the following variables.
a. Total assets
b. Net income
c. Dividend Percentage
d. Face value per share
e. Equity
f. sales
g. Financial expenses
h. Total Shares

2. Import the data from Excel files

3. Create the following variables:
a. Firm size = log of total assets
b. Profitability = net income / total assets
c. Dividend payout = dps / eps
d. Leverage = total liabilities / total assets
e. The standard deviation of net income for each firm’s
f. growth = geometric mean of the annual increase in total sales
h. Year dummies
i. Create a dummy if a firm pays a dividend, else zero. call it divdum

4. From the above variable, develop some interesting hypotheses and test them using ttest and regressions.
(Just one example: Using transaction cost hypothesis: Large firms have better access to external finance, hence they pay more dividends as they do not rely desperately on internally generated funds)
More interesting topics can be found here https://opendoors.pk/home/research-topics/research-topics-in-finance/.
I do not believe in change if two students have chosen the same hypothesis :D
5. Arrange data in panel data format and assign numeric identifiers to each firm

6. Declare the data panel data

7. Winsorize the variables at 1st and 99th percentiles

8. Create residual vs fitted values plots and visually confirm influential and outlier observations

9. Remove influential and outlier observations using studentized residuals and Cook’s D

10.Test for panel-level heteroscedasticity

11. Test for panel-level serial correlation

12. Use all appropriate steps in the panel data model selection and use robust standard errors according to steps 10 and 11.

13. Report your descriptive statistics, correlations, VIF, Hausman test, and regression results (use nested models) using asdoc.

14. Make a zip folder and add the following to it:
a. Stata log file
b. Stata Do File
C. Stata Data file
D. Excel file containing the data
E. Word Files containing the results

Important Note: Do not submit files that were created during a lecture in the lab. You are supposed to submit files that you have created at home. The purpose is to have as much practice as possible. Use as many comments and examples as possible. Marks are based on the size, examples, and comments inside the submitted assignments.

1. Download share prices data of 50 companies either from this site https://opendoors.pk/share-prices/ or any other site for the period 1/1/2010 to 12/30/2018

2. Create a year variable in this dataset

3. Download index points for the same period from this page https://opendoors.pk/od/kse-100-index-points-historical-data-in-excel/ or any other site

4. Download company symbols, year, companies book value per share and shares outstanding from this page https://opendoors.pk/financials/ or any other site for the same 50 companies

5. Arrange data in panel data format.

6. Merge share prices data with the financial data (point 4 above) using symbol and year as merge criteria

7. Merge index points into this new dataset (obtained in point 6) using the date as merge criterion

8. In each year, classify firms into small or big based on median market capitalization (me = shares * price). Small firms should be tagged as 1 while big firms should be tagged as 2.

9. In each year, classify firms into three groups based on book-to-market (beme) ratio: firms with low beme ratio should be tagged as 1; medium as 2; and high as 3

10. Make 6 portfolios and find their monthly returns as per the following definitions:

SL = Monthly average returns of firms that have size =1 and beme = 1
SM = Monthly average returns of firms that have size =1 and beme = 2
SH = Monthly average returns of firms that have size =1 and beme = 3
BL = Monthly average returns of firms that have size =2 and beme = 1
BM = Monthly average returns of firms that have size =2 and beme = 2
BH = Monthly average returns of firms that have size =2 and beme = 3

11. Reduce the dataset to portfolio level and remove duplicates.

Activity 1: Writing CV from Python to Excel
1. Create an excel file using openpyxl library. Name this file as Your Name – CV, where Your Name should be replaced with your actual name.
2. Rename the active sheet as CV
3. Create two columns with headings ‘Record’ and ‘Details’
4. Under the record column, write record names such as :

a. Name
b. Father Name
c. Domicile
d. Date of birth
e. More records that are included in a typcial CV

5. Under the Details column, write details for each relevant record: such as

a. Attaullah Shah
b. Kamal Shah
c. Bannu
d. 1982
e. Etc.

Activity 2: Create Sheet with the name ‘Series’. Complete the following activities:
1. In cell A1, write 0
2. In cell A2, write 1
3. In cell A3, sum the values in cell A1 and A2
4. In cell A4, sum the values of A2 and A3
5. In cell A5, sum the values of A3 and A4
6. In cell A6, sum the values of A4 and A5
7. Continue with this pattern until cell A1000

Important Note: Do not submit files that were created during a lecture in the lab. You are supposed to submit files that you have created at home. The purpose is to have as much practice as possible. Use as many comments and examples as possible. Marks are based on the size, examples, and comments inside the submitted assignments.

Complete the following tasks:

Create three excel files. The first file will contain (1) book value per share (2) market value per share (3) number of shares outstanding (4) company symbol (5) year. This file will should contain data for at least 20 firms and at least 3 year, though adding more years and firms is appreciated.

The second file will contain stock prices data of the same firms. This file should contain (1) symbol (2) date (3) closing stock prices. The data should be daily data for at least three years.

The third file will contain KSE-100 index points and date variable. Create a year variable from the date variable.

Data download links:

Download share prices data either from this site https://opendoors.pk/share-prices/ or any other

Download index points for the same period from this page https://opendoors.pk/od/kse-100-index-points-historical-data-in-excel/ or any other site

Download company symbols, year, companies book value per share and shares outstanding from this page https://opendoors.pk/financials/ or any other site for the same 50 companies

Task 2: make DataFrames from these three files. Assign proper names to these DataFrames.

Task 3: Merge file1 and file2 on symbols and year. Then merge file3 into the newly merged DataFrame using year.

Task 4: Create new variable in BM the merged DataFrame by dividing book value on market value. Also create another variable ME by multiplying shares outstanding into share price.

Task5: Create daily returns, convert them to monthly returns, remove duplicates to make the data monthly.

Task6: Create variable medME. This variable will hold median value of ME in each year. Create another variable medBM, which will hold median value of BM in each year.

Task7: Find average monthly returns for firms that have ME value greater than medME. Also find average monthly returns for firms that have ME value lower than medME.

Task8: Repeat task 7 for the BM variable.

Important Note: Do not submit files that were created during a lecture in the lab. You are supposed to submit files that you have created at home. The purpose is to have as much practice as possible. Use as many comments and examples as possible. Marks are based on the size, examples, and comments inside the submitted assignments.

1. Import the following industries.xls file from our website and name it ind

industries.xls

2. Create stock returns for each firm

3. Convert the firms returns to monthly returns

4. Create monthly returns for each industry

5. Remove duplicates and keep only one observation for each industry in each month

6. Report descriptive statistics for each industry

7. Which industry has the highest risk returns ratio (Sharp ratio)?
(hint find the mean and standard deviation for each industry in the whole period)

8. Repeat all of the above steps using weekly returns

Important Note: Do not submit files that were created during a lecture in the lab. You are supposed to submit files that you have created at home. The purpose is to have as much practice as possible. Use as many comments and examples as possible. Marks are based on the size, examples, and comments inside the submitted assignments.

1. Import a TSV file from a Computer folder and create a DataFrame, name it df2

2. Import Pipe (|) delimited file from this link into DataFrame and name the DataFrame as df3

3. Import data from this csv file into Panda’s DataFrame, name the dataframe as prices

4. Create stock returns for each firm

5. Find mean returns for all firms

6. Create a DataFrame with the name updays from the prices DataFrame by including only those rows where returns are higher than the mean returns

7. Create a DataFrame with the name downdays from the prices DataFrame by including only those rows where returns are lower than the mean returns

8. Find the standard deviation of returns of the updays DataFrame, call this variable as sdup

9. Find the standard deviation of the returns of the downdays DataFrame, call this variable as sddown

10. Find the log(sddown / sdup)

11. Show practice on the following functions using the symbol column from the prices dataframe:

11.1 count()
11.2 value_counts()
11.3 Find min(), max() of the returns column

12. Show practice on isin() method by creating two Pandas Series

14. Show practice on adding columns to a DataFrame

15. Show practice on dropping columns

16. Impress me with something that we have not studied in the class