' F CONVERTER
ChDir "E:\Macros\Input"
Workbooks.Open Filename:="E:\Macros\Input\fo" & Format(Workbooks("NSE Converter.xls").Sheets("Sheet1").Range("G2").Value, "ddmmmyyyy") & "bhav.csv", Origin:=xlWindows
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
With Range("A1:O" & LastRow)
.AutoFilter Field:=1, Criteria1:="<>FUTIDX", Operator:=xlAnd, Criteria2:="<>FUTSTK"
.Offset(1, 0).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("C").Insert
Range("C2").Value = "-I"
For i = 3 To LR
If Range("B" & i).Value = Range("B" & i - 1).Value Then
Range("C" & i).Value = Range("C" & i - 1).Value & "I"
Else
Range("C" & i).Value = "-I"
End If
Next i
For i = 2 To LR
Range("B" & i).Value = Range("B" & i).Value & Range("C" & i).Value
Range("D" & i).NumberFormat = "yyyymmdd"
Range("D" & i).Value = Range("P" & i).Value
Next i
Columns("C").Delete
Columns("N:O").Delete
Columns("L").Delete
Columns("J").Delete
Columns("D:E").Delete
Columns("A").Delete
Range("B1").Value = "TIMESTAMP"
Range("I2:I" & LR).Formula = "=G2*LOOKUP(9.99999999999999E+307,SEARCH(""#""&'[NSE Converter.xls]Sheet1'!A,""#""&SUBSTITUTE(TRIM(A2),CHAR(160),"""")),'[NSE Converter.xls]Sheet1'!B)"
For i = 2 To LR
Range("G" & i).Value = Range("I" & i).Value
Next i
Columns("I").Delete
Rows("1").Delete
Application.ScreenUpdating = False
ChDir "E:\Macros\Output"
ActiveWorkbook.SaveAs Filename:="E:\Macros\Output\Stocks\fo" & Format(Date, "ddmmmyyyy") & "bhav.txt", FileFormat:=xlCSV, CreateBackup:=False
Application.DisplayAlerts = False
Application.ScreenUpdating = True
ActiveWindow.Close
Application.ScreenUpdating = False
