Many people trade securities on the stock market nowadays because it became available on the Internet broker platforms since the Internet and Computer Systems allowed them to do so. A part of being successful in trading is to be able to analyse one’s trades. Since algorithmic trading became so popular in recent years even among individual traders due to the simplicity of the Python language quantum traders can easily automate processes and use Tableau as the mean of the analysis tool. As a part of the analysis, it is important to be able to analyse the candlestick charts which carry information about open, high, low and close prices (OHLC for short) on the chosen timeframe. You can distinguish green (bullish) and red (bearish) candles. The difference between these two candles is the former candle has an opening price lower than the closing price while the latter opening candle’s price is higher than the closing as it can be seen from the below image:
The timeframe represents the aggregation level of the time series meaning that, for example, having time-series OHLC for a 1-minute timeframe we can aggregate data to the high levels such that 10 min, 30 minutes, 1 hour, 4 hours, 1 day and higher. It means that each candle would have OHLC prices for every 10 minutes if the 10 minutes timeframe was chosen. There are different platforms where a trader can perform technical analysis of different stocks like Tradingview and an example of the candlestick chart for the S&P500 index from this platform can be found below where each candle represents one day of the price movements :
Building an analysis trading tool in Tableau, a trader might want to have the candlestick chart in his dashboard at least for some period of time as a part of the analysis. In this post, an interactive candlestick chart will be created.
Data Gathering and Aggregation
Many services provide stock prices for different timeframes. Some services are free like Yahoo Finance and others might charge you fees, for example, Nasdaq. As was above mentioned, the most flexible timeframe is one minute and it allows aggregate on higher levels. However, one can access a one-minute timeframe in Yahoo Finance only for the last 7 days via the Python library yfinance, in other services this period may vary. Another question that a trader want to consider is a delay in time. If a trader works on timeframes such as one or five minutes then a delay in 10 minutes is not acceptable. On the other hand, if a trader uses a one-day timeframe then 10 minutes delay would not impact his decision much.
In this post, we will use the Python library yfinance to obtain one-minute timeframe data online
import yfinance as yf
intraday_data = yf.download(tickers=[“AAPL”], period=”7d”, interval=”1m”, auto_adjust=True)
where tickers is a list of short names of stocks. In this post, we will consider only the FAANG stocks. According to Investopedia, “FAANG” is an acronym that refers to the stocks of five prominent American technology companies: Meta (FB) (formerly known as Facebook), Amazon (AMZN), Apple (AAPL), Netflix (NFLX); and Alphabet (GOOG). The period is a number of days to extract the one-minute timeframe data and the interval is refer to the one-minute timeframe. This is the output for the retrieved data:
One can note that this data also included volume, which is the number of bought (sold) shares.
I also used pandas’ resample method to aggregate the data to the higher level:
# Define the resampling logic
ohlcv_dict = {
‘Open’: ‘first’,
‘High’: ‘max’,
‘Low’: ‘min’,
‘Close’: ‘last’,
‘Volume’: ‘sum’ }# Resample the data
intraday_data_10 = intraday_data.resample(’10T’).agg(ohlcv_dict)
where ’10T’ refer to 10 min aggregation with the following output:
You can see a difference in the Datetime column. The increment in the second table is 10 minutes while in the first table it is 1 minute.
Prepare and Deploy Functions to Tabpy
I have written two posts about how to use Python in Tableau and how to deploy functions in Tabpy: Part1. Building a Bridge between Tableau and Python and Part 2. Deployment of Classification Model in Tableau. I have also prepared the Python script which you can find on Github here.
I developed 6 functions In my script which are deployed to Tabpy to be called eventually from Tableau. These functions in Tabpy collect Datetime information, Volume and OHLC prices data then, transform to the required timeframe. Finally, Tabpy passes obtained datasets to Tableau after being called from the Calculated Field.
Calculated Fields Development
To call the deployed functions from Tabpy we need to develop Calculated Fields. However, Calculated Fields can be called only after a dataset is imported. So, I just created an artificial dataset with one column consisting of numbers of rows. The number of rows should be equal or more to the number of rows obtained from Tabpy. After that, I was able to use Calculated Fields. The following Calculated Fields were created:
where [Parameters].[Ticker] is
[Candle Time <= 1H]:
and [Parameters].[Period]
Candlestick Chart
After the Calculated Fields were set up, we can create charts in Tableau. I followed the YouTube video of Andy Kriebel: How to Create a Candlestick Chart. So, we need to create two additional Calculated Fields:
To turn into the candlesticks plot, place [close – open] and [high – low] on Size on the Marks Card. As a result, we get the following chart:
The empty spaces between
Another characteristic of the stock market is Volume. This is the number of stocks Bought (Sold) during the considered timeframe. So, it is simply a bar chart.