Power BI Power Query & R: Calculate MACD Indicators With Assumed Initial Values

 

The video shares a method by running R script in power query to calculate MACD (Moving Average Convergence Divergence) Indicator with Assumed Initial Value which come from the first closing price data point. In video the advantages and disadvantages of the two MACD calculations, one with initials and one without initials, are compared in a table list. The calculation R scripts are well explained in details with each line in the video as well. You may find and copy the R code lines in my blog site which URL is attached in following.

The R code lines as:

# Step 1: Load dataset and format data 

Dataset <- dataset

# format date type as expected date format day/month/year

Dataset$Date <- as.Date(Dataset$Date, format = "%d/%m/%Y")

# Ensure close_price is numeric

Dataset$Close <- as.numeric(Dataset$Close)  

# Step 2: Creat Function to calculate EMA

calculate_EMA <- function(x, n = 9) {

  multiplier <- 2 / (n + 1)

  ema <- numeric(length(x))

  ema[1] <- x[1]

  for (i in 2:length(x)) {

    ema[i] <- (x[i] - ema[i - 1]) * multiplier + ema[i - 1]

  }

  return(ema)

}

# Step 3: Calculate MACD indcators by calling created EMA function

library(dplyr)

Dataset <- Dataset %>%

  group_by(StockCode) %>%

  arrange(Date) %>%

     mutate(EMA12 = calculate_EMA(Close, 12),

            EMA26 = calculate_EMA(Close, 26),

            MACD = EMA12-EMA26,

            SignalLine = calculate_EMA(MACD, 9),

            MACD_Histogram = MACD - SignalLine) %>%

   ungroup()

# Step 4: Return the dataset

Dataset