Pivot Table Demo: Analyzing Stock Market Data

A free video tutorial from Maven Analytics
Empowering everyday people with life-changing data skills
Rating: 4.6 out of 5Instructor rating
40 courses
1,276,085 students
Pivot Table Demo: Analyzing Stock Market Data

Lecture description

In this Excel PivotTable case study we'll explore a 3-month sample of US stock market data, including Open, High, Low and Close prices as well as trading volume for 500 individual stocks.

Learn more from the full course

Microsoft Excel: Data Analysis with Excel Pivot Tables

Master Excel Pivot Tables for data analysis, EDA & business intelligence. Learn from a top MS Excel instructor!

06:30:12 of on-demand video • Updated May 2024

Take your data analysis & EDA skills from ZERO to PRO with Excel Pivot Tables
Learn how to use Pivot Tables and Pivot Charts to streamline and simplify your workflow in Excel
Master unique Pivot Table tips, tools and case studies that you won't find in ANY other course
Explore fun, interactive, and highly effective lessons from a best-selling MS Excel instructor
Get LIFETIME access to project files, quizzes, homework exercises, and 1-on-1 expert support
Test your skills with 10 real-world Pivot Table case studies (weather, shark attacks, wine tastings, burrito ratings, and more!)
English [CC]
Instructor: All right, our next case study is using stock market data, and we have a pretty simple dataset to work with here. It's a three-month sample of stock market data for about 500 different publicly-traded companies. And looking at the screenshot to the right here, we've got 29,440 observations with just a few simple dimensions and measures. So the only dimensions that we'll work with here are date and symbol. And then for measures, pretty standard financial stock market measures. We've got the open price, high and low prices for the day, the close price, and the trading volume. And the concepts that we'll cover in this kickoff include some basic sorting and filtering. Really focus on conditional formatting here to really use visualizations to tell a story. We'll practice Highlight Cell Rules, we'll do some really cool demos with data bars, and then we'll wrap up with some value settings like percent difference from, which is a great tool to show day over day gains and losses in a really clear way. So pretty straightforward, pretty quick one. Let's go ahead and jump in. All righty so with your PivotTable Case Study workbook open, go ahead and navigate to the Stock Market Data tab. And before I create a pivot, just one side note is that this is a pretty standard data extract and it's by day, meaning that this is a good candidate for an analysis that I might revisit time and time again and maybe stack more data on as I collect more data in the future. So since that's the case, it might not make sense to select the entire dataset only down to row 29,441 because that means if I add or stack new data beneath this, I'm gonna have to go into my PivotTable Tools, change the data source, extend the row reference out to whatever row that I've added data to. So like we talked about, there are two ways to deal with data that potentially could grow over time, one of which is to select the entire column headers. So now this pivot table reference has no row references. It extends down beneath the last row of data. That way as we add new data, all we need to do is simply refresh the pivot. And the second approach is to convert this range into a table because one nice feature of tables is that they can absorb new data as it's added beneath your existing data range. So in this case, let's go ahead and take the latter approach. All I need to do is select any field or any cell within my range. Go into Insert, Table. You can see it selected the entire contiguous range containing data through row 29,441. And yes, my data does have headers in row one, so that box should be checked. Press OK, and there you go. Now if you wanna format your table, you can do that right here in their Table Styles. But this is my raw data. I really don't care much about how it looks, but now if I do add more data later on, it'll be much, much easier to accommodate. So with that, let's go back into Insert, drop in a PivotTable on a new worksheet, and we can name this one Stock Pivot. There we go, and let's just format that tab with a light green shade. And now my goal here in general is to analyze trending. So the first thing I can do is just go ahead and grab Date, pull it into my row labels. It's auto-grouped it. So with the Date field selected, I really don't want month here. I just wanna preserve the daily level trending. So I can go into my Analyze tools and just press Ungroup. And that will revert back to the original format of that field, which in this case is what I want. And let's go ahead and drag Symbol into our filters box since eventually, we'll want to dive into performance for individual companies. And then we'll drag all of our fields into our Values box. Open, High, Low, Close, and Volume. So typically, when you're kicking off a brand new pivot, you'll need to go through a round of formatting. So let's go ahead and get that done right now. I'm gonna format all of these first four or five columns as currency. So open price, high price, low price, and close. These will all be formatted the same as currency with no decimal points. So there we go. And then volume is not currency. Volume's just the amount of trading taking place, format this as a number with a thousand separator and no decimals. Okay, so this is a bit more clear. Let's also practice our custom column headers. So if you wanna get rid of these Sum of labels, you can change that right here in the Formula Bar. And instead of just Open, which is already a field name, I'm gonna use my trailing space trick and format that way. So same thing with High, Low, Close, and Volume. And this just makes my table a bit more readable. And also to help, I can grab all those columns, go into Home and just Center the alignment there. So again, just a little readability trick here. And now keep in mind we're still looking at the aggregation or the sum of prices and volume across all of the symbols in our dataset, which in this case doesn't really help much. So instead, why don't we drill down into one symbol specifically, and you can pick anyone, but I'm gonna start with AAPL or Apple. And now this kind of makes a bit more sense. We're looking at the actual Apple stock price day over day as well as the trading volume. So now I have all of the raw information that I need to understand Apple's stock performance over this three-year sample, but there's definitely some work that we can do to add some visualization and really help the insights shine through. So why don't we start by really emphasizing the day over day gains or losses. And to do that, I'm gonna really focus on this Close column. And what I wanna do is pull in a second instance of Close. But for this one, instead of showing them with no calculation, I'd like to show this column as the percent difference from the previous date. So my base field is Date, base item is previous, and press OK. So this tells me how that Close price has trended day over day. In this case, on August 24th, it was down, you know, just under a hundredth of a percentage point compared to the 21st, and the reason there are missing dates is because the market is closed over weekends. So the 21st was a Friday, 24th was a Monday when the market opened back up. So this field is definitely helpful, but the problem is that it's still just a lot of numbers and it's kind of hard to pinpoint, you know, trends or what's really going on and where my eye should focus here. So to help with that, conditional formatting will be a great tool to use. So before we do, let's just go into Design and get rid of our Grand Totals row since we don't need that. And you'll notice how anytime we make a change, the columns are kind of automatically refitting based on the data. I don't really like that. So I can change that option here in PivotTable Tool, Options and just uncheck that column width autofit option there. I press OK, now I can kind of just customize the width of my columns and these will no longer change as I make updates to my pivot. So let's call this instead of Sum of Close, let's call it Daily Change to make it a bit more clear. We can Center that. And now to apply conditional formatting, I'm gonna Control + Shift down and grab this entire column of data. Go into Home, Conditional Formatting. There are a few options I could use here. I could do color scales like red to green. In this case, I'm gonna use Highlight Cell Rules 'cause I only want these cells to take two formats, either red or green based on a gain or a loss day over day. So start with Greater Than Highlight Cell Rule, and say anytime this cell value is greater than zero, I mean it's a percentage lift compared to the previous day, that's a good thing. So I'm gonna format it with a Green Fill with Dark Green Text and press OK. And without changing the selection, I'm gonna drill right back into my Highlight Cell Rules and add a second rule for less than zero. And that's a bad thing, so I'll format any cells that are less than zero or negative to Light Red Fill with Dark Red Text and press OK. And now it's very, very clear which days showed a lift for Apple and which days showed a decline. It shows me, you know, periods of time where there are consecutive gains or consecutive losses. So those stories come through so much more clearly with something as simple as a Highlight Cell Rule applied in my pivot. So that was definitely helpful. Next, let's draw attention to this Volume field here since that's another really important element of the data that we can work with. So to show Volume trending day over day, you know, obviously a bunch of different ways to do this. I could use a pivot chart, you know, with a line chart or column chart. But in this case, I wanna add my visuals right here into the table itself. So to do that, just like we did with Close, I'm gonna pull in a second instance of Volume. I'm gonna call this Volume Trend, expand the width of this column quite a bit. And now same deal here, I'm gonna Control + Shift down to grab that entire column of data, Conditional Formatting. And now I'm gonna use something called Data Bars, which essentially put in a bar chart directly within the cells of the pivot. So it's a really nice tool to add visualizations without creating secondary charts. So we'll do a blue data bar here. And now it's very, very clear where those peaks and valleys occur in terms of trading volume. So in this case, for Apple, the highest volume day was the 21st, you know, followed by the 20th. There's clearly a lot of activity on those days for whatever reason. And then as a final tweak, notice how the numbers are kind of redundant here since we have Volume already in column G and they overlap with the bars, it just kind of looks ugly. So little pro tip, Number Format, Custom, three consecutive semicolons, press OK. And voila, your numbers become invisible and I have this nice clean data bar column on its own. So what's great about this is that, you know, now that I invested a few minutes in kind of building this template and applying these conditional formatting rules, I don't have to do that over and over again as I filter or sort or slice and dice this data. So for instance, instead of Apple, maybe we wanna look at Amazon next. We can press OK, our Daily Change formatting, our Volume Trend data bars all update automatically. Very, very easy. And now here I see a very clear trend where something happened on the 23rd that caused Amazon stock price to jump almost 27% and trading volume to pretty much explode compared to any other day in the sample. Now I happen to know that earnings were released on the 23rd, which were really, really positive for Amazon, which drove this gain that we see here. So overall, that was a really quick, really easy way to integrate some visualizations and some dashboard-style tools directly within our pivot to help us tell these stories and help us understand exactly what's going on with the raw data itself. So in your homework exercises, you'll go through a similar process, but instead of analyzing day over day differences, you'll dig a little bit more into price spreads per day, but the same logic will apply. And with that, that's our quick kickoff to the stock market case study.