Aggregating CandleSticks by Dates

What is a Candle Stick Chart?

A Candlestick Chart is a type of financial chart commonly used to represent the price movement of an asset, typically in stock, commodity, or cryptocurrency markets. It visually displays the opening, closing, high, and low prices for a specific time period (e.g., one day, one hour, etc.) in a simple, easy-to-read format.

CandleStick SQL Example

The data in our stocks table already contains OHLC format and can be simply queried to allow plotting in qStudio:

CREATE OR REPLACE TABLE stocks AS SELECT * FROM read_parquet('https://www.timestored.com/data/sample/stocks/hive/pre=X/stocks_0.parquet');

SELECT * FROM stocks WHERE SYM='XOM' ORDER BY date DESC;

OHLC by Month/Year

As you can see from the graph, there are too many data points to sensibly visualize fully zoomed out. What if we "zoomed out" the granularity. For example find the OHLC by month or year. We can do this using the below SQL:

date_trunc(precision, date) is used to truncate dates to the selected precision.
e.g. GROUP BY date_trunc('month', date) truncates to month and groups all the values to those boundaries.

SELECT min(date) as date, first(open) as open, max(high) as high, min(low) as low, last(close) as close
    FROM stocks WHERE SYM='XOM' 
        GROUP BY date_trunc('year', date)
        ORDER BY date DESC LIMIT 36;        
                
SELECT min(date) as date, first(open) as open, max(high) as high, min(low) as low, last(close) as close
    FROM stocks WHERE SYM='XOM' 
        GROUP BY date_trunc('month', date)
        ORDER BY date DESC LIMIT 36;

Aggregating CandleSticks by Dates

What is a Candle Stick Chart?

CandleStick SQL Example

OHLC by Month/Year

See Also