Skip to content
TheChatArchiveThe Chat Archive
gpt-4-0314· openaiHugging Face Suzhen/CodeChat

write a vba to calculate rsi with data in column d.write sub not function

  1. User

    write a vba to calculate rsi with data in column d.write sub not function

  2. Assistant

    Below is the VBA code for calculating RSI with data in column D. This is written as a subroutine, not a function.

    Option Explicit
    
    Sub CalculateRSI()
    
        Const period As Integer = 14
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim i As Long
        Dim gain As Double
        Dim loss As Double
        Dim avgGain As Double
        Dim avgLoss As Double
        Dim RS As Double
        Dim RSI As Double
        
        Set ws = ThisWorkbook.Worksheets("Sheet1") 'Change Sheet1 to your desired worksheet name
        
        lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
        
        'Ensure there is enough data for RSI calculation
        If lastRow < period + 1 Then
            MsgBox "There is not enough data for RSI calculation."
            Exit Sub
        End If
        
        'Calculate first gain and loss averages
        gain = 0
        loss = 0
        
        For i = 2 To period + 1
            If ws.Cells(i, "D").Value > ws.Cells(i - 1, "D").Value Then
                gain = gain + ws.Cells(i, "D").Value - ws.Cells(i - 1, "D").Value
            Else
                loss = loss + ws.Cells(i - 1, "D").Value - ws.Cells(i, "D").Value
            End If
        Next i
        
        avgGain = gain / period
        avgLoss = loss / period
        
        'Calculate and store initial RSI value in column E
        RS = avgGain / avgLoss
        RSI = 100 - (100 / (1 + RS))
        ws.Cells(period + 1, "E").Value = RSI
        
        'Calculate RSI for the remaining data points
        For i = period + 2 To lastRow
            If ws.Cells(i, "D").Value > ws.Cells(i - 1, "D").Value Then
                gain = ws.Cells(i, "D").Value - ws.Cells(i - 1, "D").Value
                loss = 0
            Else
                gain = 0
                loss = ws.Cells(i - 1, "D").Value - ws.Cells(i, "D").Value
            End If
            
            avgGain = ((avgGain * (period - 1)) + gain) / period
            avgLoss = ((avgLoss * (period - 1)) + loss) / period
            
            RS = avgGain / avgLoss
            RSI = 100 - (100 / (1 + RS))
            ws.Cells(i, "E").Value = RSI
        Next i
        
        MsgBox "RSI calculation complete."
        
    End Sub
    

    To use this code, make sure to modify the worksheet name to suit your needs (Set ws = ThisWorkbook.Worksheets("Sheet1")). Also, note that the RSI values are stored in column E.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.