Homework 11
Tesla's short-term trading data (part II)
NOTE: Homework 10 and 11 replace the midterm for this course. Treat them as you normally would, EXCEPT that for these two homework there will be NO COLLABORATION OF ANY KIND among students, nor faculty support. Clarification questions to faculty are welcome, of course, as you would in an in-class midterm. It s OK to submit partially completed Homework (i.e., certain buttons but not others).
Goal
By completing this exercise you will learn several techniques to clean up digital data and preparing it for analysis.
Context
"Done with the data cleaning?" Ms. Lendalot was clearly curious to know the results of your analysis.
"A piece of cake, Boss. As always with data cleaning, it took longer than expected, but I am just about done."
"Faaaantastic! And have you translated the Euros into Dollars using the exchange rate that was current at the time of each transaction?"
"Not yet. That is next on my to-do list.“
"Ah! It is indeed taking longer... OK. Let's do it right. Once you have translated the prices, I want you to calculate the average of all the valid daily
CAGRs for these transactions. Are you familiar with the concept?"
"Yes, I took a class at McIntire where I had to calculate daily Compound Average Growth Rates"
"Ah! You went to a faaantastic School. Good for you they taught you something useful!"
You smile as you say "I am on it, Boss!"
Task
This is the second half of the task that Ms. Lendalot gave you in H10. Refer to the class slides to create an Excel app with Visual Studio. As always, the video will point you in the right direction yet it is not the solution. Data may be different.
Criteria
- H11 does everything that H10 was supposed to do.
- The appearing / disappearing of the buttons must mimic what is shown in the video
- A button uses the exchange rates found in the table called EURO_USD_EXCHANGE_RATE in the SmallBankDB to find the appropriate rates to turn Euros into Dollars. A message box informs the user about the number of changes made
- A second button calculates and shows the daily CAGRs for each valid transaction (formatted as % with 3 digits after the decimal point)
- A message box shows the average of the valid CAGRs to the user (format as 'p3') and how many rows were excluded because the cid was invalid.
A copy of the same financial data used in the previous homework is here (right click and save
as...).
Tip: If the homework is too challenging, you may submit it with some (not all) of the required functionality for partial credit.
Data Dictionary (column & definition)
A Customer id
B First and Last name
C Date of birth
D Phone number
E Business unit (US or France)
F Date bought
G Total paid
H Purchase price
I No of shares
J Date Sold
K Selling price