Makhoul Quant Lab: Excel with VBA Fall 2016
FINAL PROJECT INSTRUCTIONS
For this project, you will have to use most of the concepts we have seen during the course and show that you can apply them in a real life situation on your own. You have one week to work on this, but I strongly suggest that you start of early, as this may be more challenging than it seems. The deadline is October 21st, at 23:59. You are allowed to exchange ideas with colleagues, but should work on your own program. Programs which are more than reasonably similar will be subject to further scrutiny, meaning that students will be called upon to prove the originality of their work. Plagiarism will NOT be tolerated.
Suppose you are an analyst in an asset management company. More than often, you receive two types of database, containing not necessarily the same companies and not necessarily for the same years. One of the first things you are interested in doing is organizing those two different databases into one organized database, with companies and years in common. Since this situation happens so frequently, you decided that it would save you a lot of time if you developed an algorithm to merge these two databases into a new database (a different file) with the click of a button. So, here is what you have to do (not necessarily in that order):
– Identify the companies in common in the two databases
– Identify the years in common for the two databases
– Aggregate the daily stock prices of companies into a yearly measure
(calculate the average price of stock x in the year t)
– Construct a panel of all the companies in common throughout all the
years in common and reporting all the information reflected in the two databases
Makhoul Quant Lab: Excel with VBA Fall 2016
Now, here is the tricky part: remember you don¡¯t actually know how many indicators, how many companies, or how many years those databases will have. That means you have to program something that will work no matter what. The program should be general enough so that no matter how many or which companies are contained in the database, your panel will be created without any problems. The same goes for years and indicators. What you can assume is that the name of the companies, years and indicators will always be in the same place (that is, if the names are located in column B starting at row 2, they will always be in column B starting at row 2). In other words, the format of the databases will always be the same.
Further instructions:
– You are not allowed to change anything on the original datasets. You cannot change the name of the files, the name of the sheets, delete or create any content, or move anything
– You are not allowed to manually write anything in your new file. You can (if you want to) format cells in your panel so that the headers are in bold, for example, or change the size of the columns so that the text fits in the cell
– You can create as many worksheets as you want, but they all should be blank when you submit your file
– You should create a button to run your program without having to actually go to the macro interface
– Do not forget to save your file as .xlsm (Microsoft Excel Macro- Enabled Worksheet)