Best VBA sites for looping code?

C

Cat88

Hi there,

I need some help to improve my testing ability. I have End of Day excel price (OHLC) and volume data which I often need to check an idea by comparing historical examples which are similar.

Part of what I want to do is declare yesterday and today's OHLC data as variables and then add a condition (e.g. "if yesterday's close is < today's open" then "print value of the closing price in 60 trading sessions time")

I've tried using stack overflow however cannot seem to get it quite right. I thought maybe someone has experience building this type of looping request and provide an example which can get me over the hump.

thanks
 
I assume the following.
1. Date,O,H,L,C,V EOD data is in Sheet1.
2. First row is for label.
3. Sort data from oldest to newest.
4. Output to Sheet2.

Sub Scan()

Dim i As Integer, j As Integer, k As Integer
Dim total_row As Integer, output_ptr As Integer

' Delete output in Sheet2
If ThisWorkbook.Sheets("Sheet2").Cells(1, 1) <> "" Then
output_ptr = ThisWorkbook.Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To output_ptr
ThisWorkbook.Sheets("Sheet2").Columns(i).EntireColumn.Clear
Next i
End If

' EOD Data from yahoo finance and store in Sheet1
' Sort data from oldest to newest
' Store output to Sheet2

output_ptr = 1
total_row = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row 'Get total row of EOD data
ThisWorkbook.Sheets("Sheet1").Activate
ThisWorkbook.Sheets("Sheet1").Range("A1:G" & total_row).Sort key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes

For i = 2 To total_row
If ThisWorkbook.Sheets("Sheet1").Cells(i, 5) < ThisWorkbook.Sheets("Sheet1").Cells(i + 1, 5) Then
ThisWorkbook.Sheets("Sheet2").Cells(1, output_ptr) = ThisWorkbook.Sheets("Sheet1").Cells(i + 1, 1) 'Date to first row of Sheet2
ThisWorkbook.Sheets("Sheet2").Cells(1, output_ptr).NumberFormat = "mm/dd/yyyy" 'Format cell to date format
k = 2
For j = i + 1 To i + 60
ThisWorkbook.Sheets("Sheet2").Cells(k, output_ptr) = ThisWorkbook.Sheets("Sheet1").Cells(j, 5) 'Copy Close data to Sheet2
k = k + 1
If j > total_row Then 'Get out if hit end of data
Exit For
End If
Next j
output_ptr = output_ptr + 1 'Increment output index
End If
Next i

End Sub
 
You must log in or register to reply here.
Back
Top