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
Thank you very much for the detailed reply @Peter8519 it will take me a little while to work through your text and then I will reply. Thanks again.