Thank You… Again

What a spectacular year it’s been on The Ranch, and the Fall ’12 Membership Drive finished in similar fashion. We Ranchers are in gratitude for all our member support, and we give a special tip of the hat to our newest members. Put simply, your support is what makes everything we do possible. We wouldn’t be here without you. Thank you.

As you’re out there on the dusty trail of charts don’t forget to  abide and, of course, trade ’em well.

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.


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



Step 3a - Binning

Step 3a – Binning



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


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.