Loading...

Candlestick

General

Let’s begin by importing VerticaPy.

import verticapy as vp

Let’s generate a dataset using the following data.

N = 30 # Number of records

data = vp.vDataFrame({
    "date": [1990 + i for i in range(N)] * 5,
    "population": [100 + i for i in range(N)] + [300 + i * 2 for i in range(N)] + [200 + i ** 2 - 3 * i for i in range(N)] + [50 + i ** 2 - 6 * i for i in range(N)] + [700 + i ** 2 - 10 * i for i in range(N)],
})

In the context of data visualization, we have the flexibility to harness multiple plotting libraries to craft a wide range of graphical representations. VerticaPy, as a versatile tool, provides support for several graphic libraries, such as Matplotlib, Highcharts, and Plotly. Each of these libraries offers unique features and capabilities, allowing us to choose the most suitable one for our specific data visualization needs.

_images/plotting_libs.png

Note

To select the desired plotting library, we simply need to use the set_option function. VerticaPy offers the flexibility to smoothly transition between different plotting libraries. In instances where a particular graphic is not supported by the chosen library or is not supported within the VerticaPy framework, the tool will automatically generate a warning and then switch to an alternative library where the graphic can be created.

Please click on the tabs to view the various graphics generated by the different plotting libraries.

We can switch to using the plotly module.

vp.set_option("plotting_lib", "plotly")

VerticaPy’s candlestick visualization feature empowers data analysts and financial professionals to gain valuable insights from time-series data. By representing open, close, high, and low price values for a specific period, candlestick charts offer a clear and intuitive view of price movements, helping users identify trends, reversals, and patterns in financial data. This tool enhances decision-making in areas such as stock trading, investment analysis, and financial forecasting, making it an indispensable asset for those working with time-series financial data.

data["population"].candlestick(ts = "date")

We load the VerticaPy chart extension.

%load_ext verticapy.chart

We write the SQL query using Jupyter magic cells.

%%chart -k candlestick
SELECT
    date,
    MIN(population) AS min,
    APPROXIMATE_PERCENTILE(population
                           USING PARAMETERS percentile = 0.25) AS q1,
    APPROXIMATE_PERCENTILE(population
                           USING PARAMETERS percentile = 0.50) AS q2,
    APPROXIMATE_PERCENTILE(population
                           USING PARAMETERS percentile = 0.75) AS q3,
    MAX(population) AS max,
    SUM(population) AS sum
FROM
:data
GROUP BY 1
ORDER BY 1

We can switch to using the highcharts module.

vp.set_option("plotting_lib", "highcharts")

VerticaPy’s candlestick visualization feature empowers data analysts and financial professionals to gain valuable insights from time-series data. By representing open, close, high, and low price values for a specific period, candlestick charts offer a clear and intuitive view of price movements, helping users identify trends, reversals, and patterns in financial data. This tool enhances decision-making in areas such as stock trading, investment analysis, and financial forecasting, making it an indispensable asset for those working with time-series financial data.

data["population"].candlestick(ts = "date")

We load the VerticaPy chart extension.

%load_ext verticapy.chart

We write the SQL query using Jupyter magic cells.

%%chart -k candlestick
SELECT
    date,
    MIN(population) AS min,
    APPROXIMATE_PERCENTILE(population
                           USING PARAMETERS percentile = 0.25) AS q1,
    APPROXIMATE_PERCENTILE(population
                           USING PARAMETERS percentile = 0.50) AS q2,
    APPROXIMATE_PERCENTILE(population
                           USING PARAMETERS percentile = 0.75) AS q3,
    MAX(population) AS max,
    SUM(population) AS sum
FROM
:data
GROUP BY 1
ORDER BY 1
Loading....

We can switch to using the matplotlib module.

vp.set_option("plotting_lib", "matplotlib")

VerticaPy’s candlestick visualization feature empowers data analysts and financial professionals to gain valuable insights from time-series data. By representing open, close, high, and low price values for a specific period, candlestick charts offer a clear and intuitive view of price movements, helping users identify trends, reversals, and patterns in financial data. This tool enhances decision-making in areas such as stock trading, investment analysis, and financial forecasting, making it an indispensable asset for those working with time-series financial data.

data["population"].candlestick(ts = "date")
Out[2]: <Axes: >
_images/plotting_matplotlib_candlestick.png

We load the VerticaPy chart extension.

%load_ext verticapy.chart

We write the SQL query using Jupyter magic cells.

%%chart -k candlestick
SELECT
    date,
    MIN(population) AS min,
    APPROXIMATE_PERCENTILE(population
                           USING PARAMETERS percentile = 0.25) AS q1,
    APPROXIMATE_PERCENTILE(population
                           USING PARAMETERS percentile = 0.50) AS q2,
    APPROXIMATE_PERCENTILE(population
                           USING PARAMETERS percentile = 0.75) AS q3,
    MAX(population) AS max,
    SUM(population) AS sum
FROM
:data
GROUP BY 1
ORDER BY 1;
_images/plotting_matplotlib_candlestick1.png

Custom Parameters

Hint

In VerticaPy, when working with candlestick visualizations, you have the flexibility to select the quantiles and the primary aggregation method that best suits your data analysis needs. This capability allows you to tailor your candlestick charts to precisely represent the data distribution and trends, providing a customized and insightful view of your financial data.

Note

In SQL, aggregations can be computed directly within the input SQL statement, but in Python, the process is a bit different.

Quantiles

data["population"].candlestick(ts = "date", q = (0.1, 0.9))