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 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 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 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.

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’ and we can notice the change the color combination of candlestick chart.

Color Blindness in candlestick chart

Similarly for ‘Achomatomaly’ chart looks as below

Conclusion

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.


Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
PowerBI

About Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features. While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs. He can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

467 Views