Welcome traders! I started this data collection in an effort to better understand timing trends on trading bitcoin at any given point in a day. I wanted to know if there was any particular day and/or time of day that was more likely to see volume and movement.
So i have collected hourly OHLC and volume data going back to April 1st. When i started, i wanted to have roughly 90 days worth of data to analyze; and of course, as we move forward, the more data there will be in the collection.
Using Google Sheets, below is a link to a limited version of my data. Perhaps in the future, i can include a more in-depth version, including the raw data itself, and make it sort-able on the users’ end, so that one could sort and manipulate the data in ways not yet thought of.
Below i will briefly explain each sheet and what the information is telling us.
Please keep in mind this is a constantly evolving project and I would very much appreciate feedback. Suggestions for improvements, what you would like to see and so forth. Thanks!
Just wanted to include brief example of the raw data that is collected. Hourly open, high, low, and close data; as well as hourly volume totals (in millions). I also record the range, % range, change, and % change.
When you click on the link, it will take you to the welcome page and I will briefly explain each sheet below
Sheet 1: ‘by day+time’
This sheet is the main part of the data collection and is typically the first sheet I will look at when I want a quick overview.
This sheet shows us several data points broken down by hour + day. We can also see the total for any particular day OR hour (or both).
Let’s take a closer look at what we are seeing here:
Each day/hour is broken down into 5 rows:
Column B (avg vol): Shows us the average volume for that time+day, in millions. The averages are ‘heat-mapped’ so higher average = darker shade of blue. The ‘heat-mapping’ applies to ALL days+hours (as opposed to for that day only)
Column C (change): Shows us the change in that average volume from the previous week (again, in millions). For example, we can see that after the most recent 12:00AM-Monday data, the average hourly volume is now 132.11 million. That average went down by 1.12 million after adding most recent data. The totals in this column are ‘heat-mapped’ showing both positive and negative changes, with the extreme totals being darker shade of red or green. Unlike column B, the ‘heat-mapping’ applies ONLY to that particular day (as opposed to every day)
Column D (r.o.c.): Shows us the average volume totals from the previous 5 weeks and displays them in spark-line (chart) form. This ONLY shows us the direction of the volume average for THAT day+hour. Meaning there is zero correlation between one spark-line and another, nor are they relative to each other.
Looking a little closer at 12:00AM-Monday spark-line:
Now let me show you exactly what data is shown in that graph:
Circled in red are the 5 data points in the spark-lines. We can see how those averages have moved over the last 5 weeks. The averages themselves are a running total as each week gets added, but the spark-lines only show previous 5 averages.
I feel this is helpful to look at in order to compare recent activity to the overall average for any particular day+hour. For example, we can see that average volume for that same 12:00AM-Monday is 132.11. By looking at that spark-line we can also see that we had a big volume total on July 30th, pushing that average up by nearly 25 million. The following week (Aug 6) we saw a return closer to the average. Translated to the spark-line we can see the big jump up, then slightly lower, but still higher from 5 weeks ago.
**Again, it is important to remember that each spark-line is entirely independent of any other one.**
(sorry, i hope i didn’t make that explanation too convoluted lol)
Column E (range): Shows us the average % range for that particular day+hour. Not CHANGE, but RANGE (high minus low). Similar to column B, it is ‘heat-mapped’ in yellow, with the higher totals showing up a darker shade. This ‘heat-mapping’ applies to ALL days+hours (similar to column B)
Column F (change): Shows us the % change in that average range from the previous week. For example, if we look at 1:00AM-Monday, we can see that the average range jumped 0.06% up to 0.91% after the most recent week. By seeing that, we can quickly determine that the most recent instance of that day+hour, we saw a big hourly range thus pushing that total up. Much like column B, the changes are ‘heat-mapped’ to show extreme positive and negative changes; and are ONLY for that particular day (as in column B)
Notes:
- All times are in UTC
- All volumes in millions
- Totals along bottom show overall average volume and % range for that particular day.
- Totals along right edge show overall average volume and % range for that particular hour.
- Also shows the average volume + range across ALL days+hours and when the last day of data was included in the totals.
Sheet 2: “odds of range”
This sheet shows us the odds of seeing a particular sized % range for any given day+time. These %’s are based on all previous instances of that day+time included in my data, and bottom row shows us the # of weeks that are included in these totals.
A closer look at this data:
Let us look at 11:00PM-Monday. We can see that 68.42% of the time, we saw a range of at least 0.50% for that particular day+hour. 36.84% of the time, we saw a range of least 1.0%, 10.53% of the time over 1.5%, and 10.53% of the time over 2.0% range. We can also see that there are 19 weeks worth of data in these totals. (So, for example, 13 of the 19 weeks in my data set, BTC had an hourly range of >0.50% at 11PM; or 68.42%.)
Pretty straight forward. This will allow us to very quickly see when the most volatile times of any particular day+hour. The data is ‘heat-mapped’ and covers ALL days+hours
Quick note: This data collection starts on April 1st, which happened to be a Sunday. So on this sheet, Sundays will be the first day that will see the # of instances tick up to the next one. (First to have 20. At time of writing, we are in middle of Saturday price action, therefore Saturday totals have one less instance than all the other days)
Sheet 4: “hourly volume heatmap”
This sheet is pretty straight-forward. Shows average volume totals and average % range, side-by-side, for any particular day+hour and applies ‘heat-mapping’ to each.
This allows us to quickly see what times are the most active in terms of volume and/or volatility. Typically, the 2 are similar but not always. (for example: 12:00PM-Tuesdays have the 2nd highest average hourly volume, but its corresponding average range (1.18%) is not one of the most volatile hours.
This data is the same data as shown in sheet 1’s ‘by day+ time’ (columns B and D); but shown side-by-side for easier comparison. Along bottom and right edges are overall average for that particular day or hour.
Sheet 5: “sparkline”
This sheet shows us an enlarged view of the spark-lines seen on the main ‘by day+time’ sheet. While there is no comparative value between them, by looking at them side-by-side like this, we are able to see any broader volume trends.
For example, if we look at the first 4 or 5 hours for Sunday, we can see that each of those spark-lines have been trending upwards. That tells me that over the last 5 weeks, we have seen the hourly volume averages for those particular times rising. Those times are becoming more active of late than average.
On the other hand, if we look at 5–8pm on Sundays, it is the opposite. We are seeing those hourly averages trend down. Those times have been less active of late than average.
Sheet 6: “top volume”
This sheet simply lists, in order, the 50 highest hourly volume totals in my data set. (again, going back to April 1st)
Sheet 7: “top range”
This sheet lists, in order, the 50 highest hourly % ranges in my data set. Often, it corresponds with one of the highest volume totals as well, but not always. As we can see, the highest hourly range (by a long shot, the ‘Soros candle’) is almost 18%, but volume wise, that day+time is only the 13th highest.
Wrapping Up
I do believe that covers everything for the moment. This project was started as a personal interest, to see if there were trends to the times of volatility and volume aside from the obvious times. It has since morphed into a much more comprehensive project with several hundred hours invested, and will be constantly evolving as we move forward.
For now, I will refrain from posting my personal opinions and observations, and will leave that for another post. The purpose of this is to simply explain the information, and will let you come to your own conclusions.
Lastly, it will automatically update on your end as i update the master sheets on my end :)
If you find this information useful, or have comments and suggestions on how I can improve this for everybody, I encourage to leave your thoughts below!
Thanks for reading and until next time,
Happy Trading!
@pjmorin20
btc tip jar: 1HWboE3fijGDoGhRZm9DsZqniRpNvNR6h3
