Pivot Table Demo: Analyzing Stock Market Data

Maven Analytics
A free video tutorial from Maven Analytics
Award-Winning Analytics & Business Intelligence Training
4.6 instructor rating • 23 courses • 564,957 students

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 January 2021

  • 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 twenty nine thousand four hundred and forty 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 closed price and the trading volume and the concepts that will cover in this kickoff include some basic sorting and filtering, really focus on conditional formatting here to really use visualizations to tell a story or practice, highlight, sell rules or 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 already. So with your pivot table 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 data set only down to row 29 for forty one, because that means if I add or stack new data beneath this, you'd 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 you 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 twenty nine, four forty one. And yes, my data does have headers in row ones. That box should be checked. Press OK, 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'll be much, much easier to accommodate. So with that, let's go back in the 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 data, pull it into my labels. It's auto grouped it. So at 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 training taking place format. This is a number with thousand separator and no decimals. OK, 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 clothes and volume, and it's 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 I'll 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 any one. 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 close column. And what I want to do is pull in a second instance of clothes. 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, state based item is previous and press. Okay, so this tells me how that close price has trended day or day in this case. On August 24th, it was down just under one hundredth of a percentage point compared to the twenty first. 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 I 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 total zero 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 and pivot table tool options and just uncheck that column with auto fit option there. I 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 close, let's call it daily change to make it a bit more clear. 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. There are few options I could use here. I could do color scales like red to green. This case I 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 we can format it with green fill, with dark green text. And press, OK, and without changing the selection, I'm going to 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 filled with dark red text and press. OK, and now it's very, very clear which days showed a lift for Apple in which they 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, the 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 clothes, I'm going to pull in a second instance of volume and 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 data, conditional formatting. And now I'm going 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 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 twenty first and 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 protest, no format custom, three consecutive semicolons press. OK, and while your numbers become invisible and I have this nice clean data bar column on its own, so it'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 out sort or slice and dice this data. So, for instance, instead of Apple, maybe we want to look at Amazon next. You 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 or something happened on the twenty third that caused Amazon's stock price to jump almost twenty seven percent 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 twenty third, 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 analysing 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 kickoff to the stock market case study.