gpt-4-0314· openai
Suzhen/CodeChat
write a vba to calculate rsi with data in column d.write sub not function
- User
write a vba to calculate rsi with data in column d.write sub not function
- 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 SubTo 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.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.