Market Study Bootcamp: Part 3 – Creating Histograms in Excel

 

[box style=”rounded”]Warning: some basic statistics concepts ahead. If you have not had any coffee yet today, don’t read on until you have had some. ;-}[/box]

 

If you’ve been following along with this series, as well as the reading the market studies generally, you’ll know that one of ways I use to examine data is via the histogram. To the analytically bent, histograms – also called bar charts – are beautiful things. They are scary simple, and show the distribution of values in a set of data.

If you’re a volume or TPO profiler, you look at histograms every day, only maybe you didn’t realize it. Profiles themselves are either time or volume histograms, only they are rotated 90 degrees and displayed vertically. Other than that, they are the same.

Why are histograms so powerful? Because they show so clearly show us frequency. Frequency, or how often a certain value appears in a set of data is essentially, conceptually, the same as probability. In other words if we have a set of, say, 10 whole numbers and the number 5 appears 9 times in that set, we can say that the probability of the number 5 appearing in our set of data is 90%.

This can have predictive value as well, assuming our sample is representative of the whole set of possible values. While a sample size of 10 is far too small to have reliable predictive value (unless the total number of possible data points is about 10) and there are mathematical tests for the reliability of a probability calculation, suffice to say the larger your sample size the more reliable the predictive value.

One last example… say the largest possible size of  your set of data is 1000 numbers and say your sample contains 900 randomly selected numbers from that set of 1000. Let’s say further that the number 5 occurs in your sample 800 times. This means that the frequency of the value 5 is 800, and also that the probability of the number 5 occurring in your universe of data is 88%, which is pretty high. 88% is calculated frequency/sample size or .88 = 800/900. And since your sample included  90% of the all possible values (900 out of 1000), the predictive value is of this 88% is quite high. As traders, we live in a world of non-absolutes. We live and breathe a future without certainty. Our universe contains only likely or probable outcomes. And that is why performing this kind of analysis is so important.

[hr]

Now that the, er, probability that you’ve had enough of this background mumbo-jumbo is pretty high, let’s move on to the meat of the matter. That is how to actually create one of these beasties in Microsoft Excel®. You can use other tools, of course, but Excel’s ubiquity makes it the one we’ll choose today.

Step 1 – Install the Data Analysis Toolpak

Install the Excel Data Analysis Toolpak if you do not have in installed already. Instructions from Microsoft on how that’s done are here. Go ahead and have a look. No worries, we’ll wait. Back so soon? OK, let’s continue.

 

Step 2 – Sample Your Data

Before we can create the histogram, we need some data. In this case, we’ll just use our example above. We’ll create a set of 10 whole numbers. To do this, fire up Excel, create a new worksheet. In column A, type the first 10 whole numbers that pop into your head. Your worksheet should look something like this:

Step 2 - Data Sample

Step 2 – Data Sample

Step 3 – Create a Bin

A histogram needs 2 things as input. First, it needs a set of values to plot (the actual data). But it also needs to know the range of values contained in the data, which is called a bin. The way to create a bin is to find the minimum and maximum values in your set of data. You can do that as follows:

  • Click on the header for column A
  • Click the Data item on the Excel ribbon
  • Click the Filter button

Afterward a small arrow will appear on the column header, and this allows us to sort and filter the data in our worksheet by the values in that column:

Step 3 - Filtering the Data

Step 3 – Filtering the Data

Now click on the small arrow in column A and then click the menu item that says “Sort Smallest to Largest.” Afterward, we can see that the top row is the smallest value in our data set and the bottom value is our largest. This range – 1 to 12 – will be our bin. So head over the column G and type in the number 1 as that is our smallest value, then type 2, and 3 in the rows just below 1 (still in column G). Then:

  • Highlight the cells containing 1, 2 and 3
  • Click the small + sign in the lower right corner of the highlighted cells and drag it downward until we reach the number 12, as shown below

[one_half first]

Step 3a - Binning

Step 3a – Binning

[/one_half]

[one_half]

Step 3a - Binning

Step 3a – Binning

[/one_half]

 

Step 4 – Create the Histogram

We’re almost home free now. So let’s finish this one up (everything shown below):

  • Click the Data Analysis button on the ribbon. It will probably be all the way on the right of the Data tab
  • Select Histogram from the list, then click OK.
  • Select the values in column A as the Input Range and the values in column G as the Bin Range
  • Click the Chart Output checkbox and Excel will automatically create a new chart graphic for us in addition to the histogram data (shown below)
  • Click OK to generate, and the output will appear on a new worksheet

Step 4a - Histogram

Step 4b - Histogram

Step 4c - Histogram

Step 4c – Histogram

[hr]

That’s it!

Now that your data visualization is complete, it’s on to interpretation. But that task, amigos, is up to you. Until next time…. trade ’em well.

Market Study Bootcamp: Part 2 – Popping the Big Questions

It’s been a long time. Much longer than I would have liked since Part 1 of this series. Things have been extremely busy here on The Ranch and, as they say, better late(er) than never.

But here it is, the next installment of the Market Study Bootcamp. We’re ready, dressed in our white lab coats, clipboards in hand, pocket protectors stuffed, glasses taped, to do a little information science.

Since all good science starts with a question, we will too. That question is “what does our instrument look like?” When I say look, I don’t mean literally look, as in what appears in the reflection when you hold a chart up to the mirror. I mean what are its general characteristics? As I posited in Part 1, I think in order to be successful trading any instrument, you really need to know it like a spouse, sibling, parent, best friend, etc. You need to develop an intuition for how it behaves and what makes it tick – so to speak. But that’s a qualitative kind of knowledge, and in order to gain that kind of intuitive understanding, it can help to first get to know an instrument quantitatively.

That can seem like a dauntingly big job. But not necessarily so, if you focus on the big picture and refuse to be mired in the minutiae. Case in point – below are the four major elements I want to understand as an intraday trader trying to play the swings:

  • How frequently does it swing? I want to know how many times per day it tends to swing or rotate. This is a key facet of any instrument. I’ve said many times before that you really should trade instruments suited to your temperament and natural disposition. For example, do you prefer quick, all-out effort of sprinting or the strategic and duration challenges of the marathon?
  • What is the magnitude of the swings? If we traded every swing precisely and perfectly, what are the most frequent potential rewards for any risk taken? This should be self-evident in terms of its importance. You’re in the risk management business as a trader, and a huge part of managing risk is understanding “whens and whethers” of the possible rewards.
  • How long do the swings take? What is the distribution of swing durations?  How long we should be waiting for a swing to play out is again a key facet of becoming intimate with your instrument. Is its style of price action fast and furious or is the tempo generally slow and low? Again, it’s all about whether you and your instrument are compatible. There’s no right or wrong answer here. If it feels good do it, I say. ;-} If not, find another partner.
  • When are the swings swinging? Again, an important compatibility question. If the instrument in question is at its most frisky early in the session but you’re not a morning person… well… let’s leave it at that. This is a family trading blog. ;-}

In any case, we’re going on our first “date” today with West Texas Intermediate Crude Oil, also known as NYMEX Light Sweet Crude Oil (symbol: CL). Want to be a fly on that particular wall?

Read the Instrument Spotlight Study – NYMEX Light Sweet Crude Oil here »

Until Part Three… trade ’em well.

Market Study Boot Camp: Part 1 – The Newlywed Game

Way back in the day, so far back in fact that I only vaguely remember it as reruns, there was a TV game show called The Newlywed Game. It had a pretty simple but entertaining premise – put several newlywed couples together in front of the cameras and ask them some silly and embarrassing, euphemistic questions about their domestic life to see which couples knew their spouses the best. Winning was pretty simple too. Answer correctly and accumulate points. Rack up the most points to win a new fridge, microwave, or washing machine. Domestic bliss indeed…

You may be thinking what’s this have to do with trading? Well, in a word, everything.

Over the years I have become convinced that in order to succeed at trading anything well over the long haul you must come to know your particular instrument(s) and its behaviors just like you would a sibling, spouse or significant other. Some of you have heard me say it before, and I am saying it again now.

If you were called onto a game show which asked you some pointed questions about your favorite trading instrument, would you stutter, stammer and stumble over your answers? Or would you answer with the calm confidence that only comes from deep understanding?

In this new series of posts I am going to explore some practical ways you might use to get to know your instrument, quantitatively, as well as create some new market studies and publish them here. In truth, I think there are two kinds of knowledge in this domain: qualitative/intuitive; and quantitative/hard facts. If you haven’t guessed already, this series will focus on the latter.

So, What Do You Want to Know?
All formal inquiry starts with curiosity, articulated as a question and then wrapped in a hypothesis. To that end, we’re going to explore the process, step by step, of trying to understand essential price, volume and time-related characteristics of several instruments individually and in relation to each other. Many instruments, especially the futures, have very distinct personalities. So let’s discover and quantify those distinctions. And hopefully we’ll increase our chances of taking home that snazzy new dishwasher or refrigerator-freezer combo like the couples on the game show.

So until next time… trade ’em well.