Homework H07

There’s got to be a better way to do this!

FinCalc

Learning Goal

Automating the gathering of business data from a cloud database, using pre-set "canned' SQL queries, and showing the results in Excel.

Optional Prework

Consult my very short Introduction to SQL. If you need more, check the SQL readings in Resources. You will find reference materials to support your learning the SQL language.  Your favorite GenAI can help, too!

Context

It’s 5:41 p.m. You glance at the reminder on your calendar: EOD REPORTS — 5:45 PM.
Every day, the same ritual: log into the fund database, run a couple of queries, paste the tables into Excel, and mail them to each manager for risk and compliance review. It’s not difficult, just mind-numbingly repetitive.
“There’s got to be a better way to do this!” So, you decide to build a small Excel-based app that connects to the database, runs the queries and produces the reports at the press of a button. You caught yourself smiling when you saw it running successfully for the first time.

Two days later, Ms. Tradewell stops by your desk. No warning, no small talk.
“What is that?” she asks, nodding at your screen.
“My EOD risk and compliance report,” you say. “I automated it.”
“Show me.”
You click 'Refresh Report'. Ms. Tradewell looks at the instantly refreshed screen.
“Ah! You automated the queries to our DB.”
“Yes. That was my least-favorite daily chore.”
“Good.” She replies. “But we can do better.”
She gestures toward the offices along the edge of the floor. “We’ll give your app to the managers, so they can pull updated reports whenever they want. Always up-to-date data for the managers, better decisions for LongShort, and no 'least-favorite daily chore' for you. Win-win-win."
As she leaves, you look again at your blue button. Yesterday it was an automation. Today it’s a product.

Task

For this homework you must use VS and HedgeTournamentBETA. Create a blue button/white ink ('Refresh report') that connects to the DB, runs two queries, and deposits the results in two well-formatted Excel list objects.

Criteria/Requirements

Your work must satisfy the following requirements

  • At start, the Excel spreadsheets does not show any data
  • Button is blue with white ink and does not interfere with the data
  • One table shows the ten largest (positive $) trades made in October, the other shows the ten largest (negative $) trades made in October.
  • Show only the first eight column of each trade. Format the TotValues ($, commas, and no decimals)
  • The tables are formatted with bands and headers similar in color to the video
  • The column headers show the full names of the columns in the tables. All names must be meaningful.
  • Column size must not cut out data or headers (autofit).

Hints and Tips

  •  Google and GenAI are your friends as you learn the SQL language. Do not just hunt for the right homework answer. Ask 'why?' until you feel you understand what is going on.