Pivot Table Demo: Analyzing Stock Market Data

A free video tutorial from Maven Analytics
Award-Winning Analytics & Business Intelligence Training
Rating: 4.6 out of 5Instructor rating
30 courses
808,406 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 pivots & learn data analysis w/ advanced Excel cases from a top Excel & business intelligence instructor

06:32:18 of on-demand video • Updated August 2022

Take your data analysis skills from ZERO to PRO with Excel Pivot Tables
Learn how to use Pivot Tables and Pivot Charts to streamline and absolutely revolutionize your workflow in Excel
Master unique Pivot Table tips, tools and case studies that you won't find in ANY other course, guaranteed
Explore fun, interactive, and highly effective lessons from a best-selling 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 [Auto]
All right. Our next case study is using stock market data, and we have a pretty simple data set 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 screen shot to the right here, we've got 29,440 observations with just a few simple dimensions and measures. So the only dimensions that will work with here are date and symbol. And then four 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 kick off include some basic sorting and filtering. Really focus on conditional formatting here. To really use visualizations to tell a story, we'll practice highlights 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 pivot table case study workbook open, go ahead and navigate to the stock market data tab and before it create a pivot. Just one side note is that this is a pretty standard data extract and it's by de 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 data set only down to row 29 for 41, because that means if I add or stack new data beneath this, I'm going to have to go into my pivot table 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's selected the entire contiguous range containing data through row 29 for 41. And yes, my data does have headers in row one, so that box should be checked. Press OC And there you go. Now if you want to format your table, you can do that right here in the 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 will be much, much easier to accommodate. So with that, let's go back into insert drop in a pivot table on a new worksheet and we can name this one stock pivot and we go and let's just format that tab with the 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 want to preserve the daily level trending so I can go into my analyze tools and just press on group 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 going to 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 volumes, just the the amount of trading taking place format. This is a number with 1000 separator and no decimals. So this is a bit more clear. Let's also practice our custom column headers. So if you want to get rid of these, some of labels can change that right here in the formula bar. And instead of just open, which is already a field name, I'm going to 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 of 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 data set, 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 going to 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 going to really focus on this closed column. And what I want to do is pull in a second instance of close. But for this one, instead of showing them no calculation, I'd like to show this column as the percent difference from the previous date. So my base field estate base item is previous and press OC. So this tells me how that closed price has trended day over day in this case. On August 24th, it was down just under 100th 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 trends or what's really going on and where my eyes 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 any time 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 Pivot Table Tool Options and just uncheck that column with auto fit option there and press. Okay. 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 some of closed, let's call it daily change. To make it a bit more clear, I can center that. And now to apply conditional formatting, I'm going to control shift down and grab this entire column of data, go into home conditional formatting and there are a few options I could use here. I could do color scales like red to green. In this case, I'm going to use highlight cell rules because 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 any time 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 going to format it with green fill with dark green text and press. Okay. And without changing the selection, I'm going to draw 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. Okay. And now it's very, very clear which days showed a lift for Apple in which data showed a decline. It shows me 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 want to add my visuals right here into the table itself. So to do that, just like we did with Close, I'm going to pull in a second instance of volume. I'm going to call this volume trend, expand the width of this column quite a bit. And now same deal here. I'm going to control shift down to grab that entire column of data conditional formatting and now I'm. To 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, 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 a little pro tip number, format custom, three consecutive semicolons, press k and voila your numbers become invisible. And I have this nice clean data bar column on its own. So it's great about this is that 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 want to look at Amazon next. You can press okay our daily change formatting our volume trend data bars all update automatically very very easy. And now here I see a very clear trend or something happened on the 23rd that caused Amazon stock price to jump almost 27% and trading volume 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 a quick kick off to the stock market case study.