Using Excel to Plot Volume by Price for Intraday Charts
Charts are extremely insightful; there’s no doubt about that. They can be used to garner a lot of insights about any stock for any given time frame. While this can be very helpful, there are some things that are more difficult to read with charts. There is where Level 2 analysis and tape reading comes in handy.
I’m always interested in finding out about important price levels. Charts are great for plotting support and resistance, but it can sometimes be difficult to see which prices attract the most buyers/sellers, and how strong support/resistance levels really are. I like to gather information about the volume for each price level of a stock. For example, if a stock has a daily range of $4-$5/share, where is most of the price action taking place? Are the majority of shares traded around $4 or $5? While you can gather this kind of insight from a stock chart, I much to create a bar chart that makes this data easier to comprehend.
StockCharts.com has a tool that allows you to plot the volume by price for free, however, I’m not sure how accurate the data is as I have not tested it myself. They do not show any values on the x-access which can make it more difficult to decipher the day. Additionally, their intraday charts are not free and I have never paid to try them. That being said, it is still a handy tool that I use to get some insight on a stock’s long term price action.
To use this free tool, just go to StockCharts.com > Enter a Ticker > Scroll down to the “Overlays” section > Choose “Volume by Price” > Click Update
This is what the result looks like (Black bar being buy volume, red bar being sell volume):
The free StockCharts.com tool is handy for doing a quick analysis, however, I much prefer to do my own as it allows me to get my data straight from the source and analyze intraday trends. So, let’s get started.
What you will need to do this:
1. Microsoft Excel (or a similar program)
2. Access to Time and Sales Data for a given time range
The Process
Step 1: Download Time and Sales Data
I use ETRADE Pro as my charting software, so I will use it for this example, however, you can use any Time and Sales data as long as it is accurate and can be imported into Excel.
For ETRADE Pro, go to Tools > Time & Sales > Type in a Ticker > Choose Your Time Range > Go to Settings to make sure all rows are showing (Set Max Rows to 65,000)
I usually use an intraday time range because I day trade volatile stocks so I care most about daily price action
I’ll use a stock called MYEC for this example.
Once this Screen has shown up, go to Settings > Export to CSV…
Step 2: Open Data in Excel
Now that you have exported the Time and Sales data, open it in Excel. It should look something like this:
This spreadsheet is showing the data for every single trade on MYEC for the time period I chose (1 day). Now we need to consolidate the data
Step 3: Consolidate the Data
Click on an empty cell in your Excel spreadsheet. Go to the Data ribbon and click “Consolidate”
The Function field should be left as “Sum”
The Reference field should be set to encompass all of the data from the Price and Size columns.
Check “Top Row” and “Left Column”
You should see something like this:
Click OK and make sure everything looks right. Make sure everything looks right.
Now, you should have a table that shows the volume by price, without any duplicate prices.
Lastly, you will want to go to the Data Ribbon > Click Sort > Choose Descending
This will sort the data by price.
Note: If you don’t care about tiny price swings, you can format the cells of the original data to round to a certain decimal place. Just select the cells under the Price column > Right click > Format Cells > Number > Set decimal place accordingly. This may make the data easier to read. For this example, you could format it so that all prices are rounded to the nearest thousandth (eg: .036, .037, .038, etc).
Step 4: Create a chart
The table you have just created is insightful, however, a chart is much more visually appealing and easier to read.
Highlight the data we just consolidated > Go to the Charts Ribbon > Choose a Chart Type
I am going to use a clustered bar chart for this example, but different charts work better for different stocks. For example, a pie chart would be very helpful for a less volatile stock.
Format your chart however you would like. I’m not going to go into that because it’s not the point of this post.
Here is a sample of what the chart should look like:
Step 5: Analyze
Now, you have done all of the busy work and it is time to analyze the results. MYEC daily range was .0335-.0398, but the true range was closer to .034-.0375. From the above chart, we can see a few really important levels. There is the most price action around the .035-.0352 price range, with some decent action between .036-.037. This means that there are a lot of buyers/sellers in those areas so those are the price ranges where the stock is most liquid. Also, notice how there is heavy volume around whole numbers like .035, .036, and .037. You should be conscious of this when planning your exit. I’m not going to go into a deep analysis here because that would take all day, but you should be using this chart alongside your stock chart. This can be a great tool for gaining even more insight about daily price action.
Look at the 5-minute candlestick chart for MYEC from the same day. The chart is a mess. The .035 level looks like support and .0375 looks like resistance, but it is difficult to tell how strong those levels are and if they even matter (considering they were breached a few times and the stock is trading on low volume)
Now, combine that your chart analysis with a simple price by volume chart and you can get a lot more insight.
Conclusion
It is important to keep in mind that this is just another tool to help you gain more insight into a stock’s price action. You can follow my tutorial exactly how I laid it out, but I would recommend tailoring it to fit your own needs. Mess around with Time and Sales data from different time ranges, try different kinds of charts, and organize the data in different ways.
Like any trading tool, this is only as valuable as you make it. Similar to RSI’s, MACD’s, and Bollinger Bands, the power is in the analysis, not the actual tool. See how you can incorporate this data into your strategy and trade accordingly. Personally, I find this data to be extremely insightful because it is straight from the source. All technical indicators are based around price action and the Time and Sales data. Tape reading is extremely powerful, and this is one of many ways to create visual representations of the tape. See what you can do with it!
