Build a candlestick chart in Grafana
Candlestick charts show the opening, closing, high, and low prices of financial assets, such as stocks, currencies, and securities. They are mainly used in technical analysis, to predict how prices will change.
They can answer questions like:
What are the open, close, high, and low prices of an asset on this day?
What is the spread between opening and closing prices over this time?
How is the price of this asset changing over time?
Is this asset entering bearish or bullish territory?
The figure above shows the structure of a candlestick. A candlestick covers a specific time interval, for example 5 minutes, 10 minutes, or 1 hour. For this period, it plots four values:
- Open: The starting price
- Close: The closing price
- High: The highest price
- Low: The lowest price
A candlestick chart can show many candlesticks over time. This helps you see patterns in the changing price of an asset. For example, you can tell whether an asset is entering bullish or bearish territory, or whether its market activity is topping or bottoming out.
This tutorial shows you how to:
Before you begin, make sure you have:
- Installed Grafana version 8.5 or higher
- Installed TimescaleDB
- Imported the stock trade data from the Getting Started Tutorial
The examples in this section use these variables and Grafana functions:
$symbol: a variable used to filter results by stock symbols.
$__timeTo()::timestamptz: Grafana variables. You change the values of these variables by using the dashboard's date chooser when viewing your graph.
$bucket_interval: the interval size to pass to the
time_bucketfunction when aggregating data.
Check out this video for a step-by-step walkthrough on creating candlestick visualizations in Grafana:
Create a candlestick visualization using the raw data in the table
Creating a candlestick with raw data
In the query editor, use this SQL to query a Candlestick dataset. Use the variable
$bucket intervalfor the time period covered by each candlestick.
SELECT time_bucket($bucket_interval, time) AS time, symbol, FIRST(price, time) AS "open", MAX(price) AS high, MIN(price) AS low, LAST(price, time) AS "close", FROM stocks_real_time WHERE symbol = $symbol AND time > $__timeFrom()::timestamptz and time < $__timeTo()::timestamptz GROUP BY symbol;
Click outside of the query editor, or click the refresh icon to update the Grafana chart.
Select "candlestick" as your visualization type:
Grafana turns the query into a candlestick chart that looks like this:
In this first example, we set the $bucket_interval to 1-min, you can see the price of AMZN ranges between $2120 and $2200. This chart uses hyperfunctions to query the
stock_real_timetable, with a bucket interval of 1 minute.
Retrieving this data took about 7+ seconds, over two weeks of data which is probably slower than most users would expect when analyzing data. This is where continuous aggregates are particularly useful for data-intensive, time-series applications.
With the use of the
$bucket_interval variable, we are able to switch intervals. For example, switching to a 15-minute bucket interval gives you this data.
Switch your bucket interval to 15-min from the dropdown
Refresh the dashboard to get the updated chart
The query execution took more than 6 seconds. To decrease query execution time to sub-seconds, use continuous aggregates. See the how-to guide on continuous aggregrates to learn more.
In addition to looking at the price changes for each stock, you can look at its traded volumes. This shows you how much the stock is being traded during the bucket interval.
stock_real_time hypertable contains a column with the daily cumulative traded volume. You can use this to calculate the volume of data for each bucket.
First, find the maximum
day_volume value for a symbol within a bucket.
Then, subtract each maximum from the previous bucket's maximum. The
difference gives the traded volume for that bucket.
Showing transaction volumes in a candlestick plot
Create a new candlestick panel with the following query:
SELECT time_bucket('$bucket_interval', time) AS time, symbol, FIRST(price, time) AS "open", MAX(price) AS high, MIN(price) AS low, LAST(price, time) AS "close", MAX(day_volume) - LAG(max(day_volume), 1) OVER( PARTITION BY symbol ORDER BY time_bucket('$bucket_interval', time) ) AS bucket_volume FROM stocks_real_time WHERE symbol = $symbol AND time > $__timeFrom()::timestamptz and time < $__timeTo()::timestamptz GROUP BY time_bucket('$bucket_interval', time), symbol;
Refresh the dashboard to get the updated chart.
At the bottom of the plot, you see the trade volume for each time bucket.
In conclusion, candlestick charts are a great way to visualize financial data. This tutorial shows you how to use TimescaleDB to generate candlestick values (open, high, low, close) from raw data in ahypertable. It also shows you how to query the traded volume for each time interval.
To see other examples of how you can use TimescaleDB and Grafana, check out all the Grafana tutorials.
Found an issue on this page?Report an issue!