Homework H10

The Tesla Trades

Learning Goal

Learn how to automate the cleaning of data in data set.  This is also known as data wrangling.

Context

It’s still a little surreal. Ms. Tradewell — hard to read, precise, always moving to the next challenge — personally recommended you for promotion to Senior Analyst. She never said anything about it...
You’re just starting your morning when you see her walking toward your desk. Direct line. No detours. That usually means work.
“Good morning, Ms. Tradewell. I wanted to thank you for—”
“Don’t bother,” she says, not slowing down, “I see your work. Well deserved.”
You nod. That was, in Tradewell language, a standing ovation.
“I just got off the phone with Franc Lemonney, who runs our Paris office,” she continues. “He’s seeing something… odd.”
You turn fully toward her. Odd usually means interesting.
“We have a group of clients buying Tesla shares,” she says. “Holding them for only a few days. Then selling.”
“Short-term directional trades?” you ask. “Event timing? News reactions?”
“Possibly,” she says. “But it gets better. Some execute through our U.S. broker. Others buy through our French affiliates. In euros.”
You pause. “So… maybe currency effects layered on top of short-term equity trades?”
“Maybe,” she says. “Or maybe noise. I don’t know if they’re making money. I don’t know if they know something we don’t.”
“Franc sent me a spreadsheet with trade data, U.S. and French channels. Begin by cleaning it. It’s messy. Later analyze the outcomes and tell me what you see.”
“I am on it, Boss.”
Messy data. A Paris office. Cross-border trades. Currency exposures. Short holding periods.
This is going to be fun.

Task

This homework is split into two parts. Create an app that uploads an arbitrary .csv file, and performs the analyses shown in the video: cleaning the CIDs, the DOBs, and the phone numbers.

Data Dictionary
   CID= Customer ID. 99999 means invalid customer or trade.
   BU =Business Unit: US or FRance
   EURUSD = price in USD of 1 Euro
If BU = FR, then the dates follow the European standard (d/m/yyyy) and figures are in Euros, with the dots and commas swapped wrt the US custom.

Criteria/Requirements

Your work must satisfy the following requirements

• At start, the Excel spreadsheets does not show any data
• Four buttons load a .csv file and clean CIDs, DOBs, and phone. The phones must be in (xxx)-xxx-xxxx format
• Buttons do not interfere with the data
• After each button, a messagebox informs the user with a relevant metric.

Hints and Tips

• Neither part I nor part II requires DB access (= you may use the big Shumway lab).