Fisher Transform (VBA Code)

The code below provides a convenient sub routine to perform the Fisher transform. The FT_1 sub routine uses the high and low prices of your dataset as its inputs. You will also need to specify a number n representing the length of the normalising period in the transformation process. The sub routine produces 5 columns of outputs - the median price, normalised median price, smoothed normalised median price, Fisher transform and smoothed Fisher transform.

The exact steps of the Fisher transform and the meaning of the outputs can be obtained from here, where a more detailed explanation of the Fisher transform is provided. This article focuses on the VBA code for this indicator.

Sub Runthis()

Dim high As Range, low As Range

Dim output As Range, n As Long

Set high = Range("C2:C11955")

Set low = Range("D2:D11955")

Set output = Range("H2:H11955")

n = 5 'number of historical periods to look at

'needed for certain indicators

FT_1 high, low, output, n

End Sub

Sub FT_1(high As Range, low As Range, output As Range, n As Long)

output(0, 1).Value = "Median Price"

high0 = high(1, 1).Address(False, False)

low0 = low(1, 1).Address(False, False)

output(1, 1).Value = "=(" & high0 & "+" & low0 & ")/2"

output(1, 1).Copy output

output(0, 2).Value = "Normalized Price"

rangen = Range(output(1, 1), output(n, 1)).Address(False, False)

TP = output(n, 1).Address(False, False)

output(n, 2).Value = "=(((" & TP & "-min(" & rangen & "))/(max(" & rangen & ")-min(" & rangen & ")))-0.5)*2"

output(n, 2).Copy output.Offset(0, 1)

output(0, 3).Value = "Smoothed Normalized Price"

SNPtoday = output(n + 1, 2).Address(False, False)

SNPyesterday = output(n, 3).Address(False, False)

output(n + 1, 3).Value = "=max(-0.9999,min(0.9999,(0.5*" & SNPtoday & "+0.5*" & SNPyesterday & ")))"

output(n + 1, 3).Copy output.Offset(0, 2)

SNPyesterday = output(n, 2).Address(False, False)

output(n + 1, 3).Value = "=max(-0.9999,min(0.9999,(0.5*" & SNPtoday & "+0.5*" & SNPyesterday & ")))"

output(0, 4).Value = "Fisher Transform"

SNP = output(n + 1, 3).Address(False, False)

output(n + 1, 4).Value = "=0.5*ln((1+" & SNP & ")/(1-" & SNP & "))"

output(n + 1, 4).Copy output.Offset(0, 3)

output(0, 5).Value = "Smoothed Fisher Transform"

FTtoday = output(n + 2, 4).Address(False, False)

FTyesterday = output(n + 1, 5).Address(False, False)

output(n + 2, 5).Value = "=0.5*" & FTtoday & "+0.5*" & FTyesterday

output(n + 2, 5).Copy output.Offset(0, 4)

FTyesterday = output(n + 1, 4).Address(False, False)

output(n + 2, 5).Value = "=0.5*" & FTtoday & "+0.5*" & FTyesterday

Range(output(1, 2), output(n - 1, 5)).Clear

Range(output(n, 3), output(n, 5)).Clear

output(n + 1, 5).Clear

End Sub

The code provided above is definitely not efficient and will be updated over time. If you have any suggestions, do drop a comment below.