Homework H12

Automating Pivot Tables

Learning Goal

Learn how to automate the creation and operation of Excel pivot tables

Prework

This assignment assumes familiarity with Pivot Tables. If you are not familiar with Pivot Tables, watch this short video.  Knowing Pivot Tables is a useful skill that will serve you well, beyond this exercise.

Context

FinCalc

Maya Chen's desk is next to yours. Maya graduated from NYU’s MS in Financial Engineering and has been at LongShort for a few years. She is exceptionally sharp and friendly.
It's late afternoon, she rolls her chair next to your desk.
“Got a minute?”
“Of course.”
She turns her monitor slightly toward you. Rows of numbers. Expense categories. Regions. Months.
“I’m looking at a potential investment,” she says. “ACME Global. Large footprint. Complex operations. Before we commit serious capital, I want to understand management discipline.”
You nod. “How are you thinking of measuring it?” you ask.
“Budget versus actual expenses,” she says. “If leadership can’t control costs, I want to know.”
"Makes sense. Large variances mean that they do not have a firm handle on operations "
“I have the data,” she continues. “And I know how I want it summarized. But…” she hesitates just slightly, “…I’m not great with databases and pivot tables."
Maya looks at you.
“Can you help?”
You think about the number of times she’s slipped you market color, trading tips, and quiet career advice since you joined LongShort. It's time to return the favors.
“I am on it, Maya.”

Task

Create an Excel app with three buttons: the first download a table of data from HedgeTournamentBETA called ACME_Budget in a list object.  The second and third create the Pivot tables that Maya would like to see (one and two).
Your GenAI has generated some code for the creation of a Pivot Table that you can paste inside your buttons and then customize to your needs.

Criteria/Requirements

Your work must satisfy the following requirements

  • At start the Excel does not contain any data. Pressing the button loads the data in a list object and fits its columns
  • The two buttons create pivot tables in two separate worksheets
  • The pivot tables look exactly like the pictures. Set colors as close as you can make them. Also ok if the position of the pivot filters is not exactly the same as the picture.

Hints and Tips

  • This homework requires DB access (= you may not use the big Shumway lab and most of RRH 300).