Rajendra Gupta
trendlines in Candlestick chart

Candlestick chart for stock data analysis in Power BI Desktop

November 14, 2018 by

Candlestick chart for stock data analysis in Power BI Desktop

Power BI Desktop contains a wide range of custom visualizations which helps to represent and analysis of the data with extensive formatting options. You can effectively showcase your data and save time as well by creating complex data chart with simple configuration options.

In the series of charts in PowerBI, we have explored below so far.

In this article, we will explore a Candlestick chart. Let us take an overview of this first.

Overview of Candlestick chart

Candlestick charts are used to show price movement for the securities in the stock market. These financial charts are commonly used by the technical analyst to analyze the trends of a particular security and do the trend analysis.

In this chart, we represent each day data for a stock with the candlestick. It contains information about open, high, low and closing price about a stock.

There are two kinds of candlestick available:

  • Bearish Candlestick: If the closing price is less than the opening price for the period then it shows Bearish Candlestick
  • Bullish Candlestick: If the closing price is higher than the opening price for the period then it shows Bullish Candlestick

Candlestick chart Overview

In Candlestick chart, both the bearish and bullish candlesticks are represented by the different color so that easily identify the stock pattern for the period. The candlestick body shows the price range in between the open and close price for the period.

Let us import sample data into Power BI Desktop to create the Candlestick charts.

Import data in Power BI Desktop

In the previous articles, we imported data into Power BI Desktop using the flat file (.CSV or .txt) or from the excel sheet (.xls or .xlsx).

In this article, we will import the data from the ‘Web’ data source. In web data source, we will import data directly from the web page.

Click on the ‘Web’ from the Get data as shown here.

Candlestick chart get sample data from web data source

In this example, we will import the data from the NASDAQ website. Paste the URL from which you want to import the data into Power BI dataset.

Candlestick chart get sample data from web data source

Click OK and it launches Navigator where we can see the table view of the data.

Candlestick chart - table view of data

We can get the web view of the URL as well from the ‘web view’ link.

Candlestick chart - web view of data

Click on ‘Edit’ and modify the column names. To modify column name, double-click on the particular column name and edit it.

Candlestick chart edit data

‘Close & Apply’ to save the formatted data.

Candlestick chart edit data

It applies the query changes and shows below pop up message.

Candlestick chart apply query changes

Now, in this step, the data set is now shown in the ‘Fields’ section. Our dataset contains close, data, high, low, close and volume the stock for that particular day.

Candlestick chart dataset

Candlestick chart is a custom visual so we need to download it from the Marketplace. In the menu bar, click on the ‘From Marketplace’ and search for the keyword ‘Candlestick’.

Add Candlestick chart  from marketplace

Add the visual by clicking on the ‘Add’ and below icon is added into visualization along with the success message.

Import custom visual

Double click on the Candlestick chart icon and adjust the size of the visual give it a proper visual.

Create Candlestick  chart

In the detail section, add the respective value as below.

  • Axis: – Date
  • Open: Open field from the dataset
  • Close: Close field from the dataset
  • High: High field from the dataset
  • Low: Low field from the dataset

This generates the candlestick chart as below.

view Candlestick  chart

To understand the candlestick chart better, let us reduce the data size. To do so, click on ‘Edit Queries’ from the menu bar.

edit queries for dataset

This opens the power query editor where we can make changes in the data set.

Now, we want to narrow the data set so click on the ‘Remove Rows’ and ‘Remove Bottom Rows’.

Remove bottom rows

Enter the number of rows we want to remove from the bottom of the dataset. In this example, we want to remove 30 rows from the bottom. Click on OK to apply the changes.

specify number of rows to remove from bottom

Click on ‘Close & Apply’ to save the changes in the data set.

save the data set changes

Now, Candlestick chart is generated with the updated data set. This chart is visible now to understand it properly.

View Candlestick chart

In the chart, candlesticks are in two colors. This is because of the bearish and bullish pattern for the timeframe. We can control the colors of these candlesticks, which we will see in a later section of the article.

If we hover the mouse over a candlestick, it shows the complete detail of the stock open, close, high and low values.

view values from the Candlestick chart

Now let us do some formatting to customize the chart and get details in the much better way.

Trend lines: we can add trend lines to under the stock trend for a particular value. For example, in below we specified trend lines for the ‘High’ value in the dataset.

trendlines in  Candlestick chart

We can add multiple trend lines in a candlestick chart. In below screenshot, you can see trend lines for both the high and low values.

add multiple trend lines in Candlestick chart

Candles colors: By default, it shows the below colors for the bullish and bearish candlestick chart pattern.

Default Candles colours

Let us change the color for both the bearish and bullish candles. You can view the changes quickly in the chart.

Modify Candles colours

High/Low Caps: we can place the high and low-value caps. To do so, turn on the ‘High/Low caps’ and you can find the caps as pointed in the chart.

high and low value caps in candlestick chart

Borders color: we can change the borders colors for the candlesticks with the option ‘Borders color’. Choose an appropriate color and it is applied to the candlestick chart.

Borders colour in candlesticks  chart

Trend lines: By default, trend lines appearance is straight and we have common colors for both the high and low-value trend lines.

Trend lines colors and formatting in Candlestick chart

If we change the appearance to ‘Monotone’, you can notice the difference in the trend lines shape.

Trend lines colors and formatting in Candlestick chart

Similarly, for ‘Smooth’ appearance the trend line appears as below.

Trend lines colors and formatting in Candlestick chart

As mentioned earlier, by default trend line color is common for both high and low values. Let us change different color for both the trend lines as shown here.

Trend lines colors and formatting in Candlestick chart

Legend: By default, the legend is set to off. If we set it ‘On’, we can see a legend as highlighted below.

legend in Candlestick chart

We can give a name to legend as well. In below example, I gave legend name as ‘Stock Price’ and you can see legend name highlighted here.

legend in Candlestick chart

The x-axis and Y-Axis Formatting: We can do the formatting for the x-axis and y-axis such as color, text size, text font etc.

In below example, I change the formatting for x-axis and you can notice the change.

X-Axis and Y-Axis Formatting

Similarly, for y-axis formatting, the chart looks as below.

X-Axis and Y-Axis Formatting

Color Blindness: we have different option to set color formatting by vision. By default, vision is set to Normal in candlestick chart.

Color Blindness in candlestick chart

Below are the options to choose for the vision:

  • Normal
  • Protanopia
  • Deuteranopia
  • Deuteranomaly
  • Tritanopia
  • Tritanomaly
  • Achromatopsia
  • Achromatomaly
  • Low Contrast

Color Blindness in candlestick chart

Let us change the vision to ‘Deuteranomaly’ in Candelstick chart and we can notice the change the color combination of candlestick chart.

Color Blindness in candlestick chart

Similarly for ‘Achomatomaly’ chart looks as below


Candlesticks chart in Power BI Desktop helps to analyze the security (stock) data for the data analyst or the traders so that they can read the stock price movement and technical charts effectively. You can explore this chart with your data set. We will cover more useful charts in upcoming articles.

Table of contents

Power BI Desktop Interactive chord diagrams
Power BI Desktop Pulse Charts
How to create a Word Cloud generator in Power BI Desktop
Power BI desktop Mekko Charts
Hexbin Scatterplot in Power BI Desktop
Candlestick chart for stock data analysis in Power BI Desktop
Enlighten World Flag Slicer in Power BI Desktop
Flow Map Chart in Power BI Desktop
Ask a Question feature in Power BI Desktop
Power BI Desktop Q&A data interaction examples
Power BI Desktop and Python; like Peanut Butter and Chocolate
Power BI Desktop Pie Chart Tree
Importing data from a PDF file in Power BI Desktop

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

About Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience. I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines. I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020. Personal Blog: https://www.dbblogger.com I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta