Online FIR filing

Y can't we have online FIR filing system in India for petty crimes like pickpocetting/loss of purse etc?
Even today if we lose a purse, we have to feed 100-200 rs to file and FIR and we need the FIR for even getting a sim blocked. So y not simplify the system for public's benifit. In case a person needs the FIR for blocking of sim and other related activites he/she can file it online. In addition to this, we can have the regular filing as well.
For this one needs to have a valid ID card. For this, it is required that all the govt bodies issuing such ID cards (viz driving license, PAN card, ration cards, Passport number, Visa number etc) needs to be integrated online so that one can choose the issuing body and the ID card from the govt body's database directly. It will require a lot of database to be put in place.

what happens when there is a small hole in a dam and that too in the deep? The force of water gushing out of this is so huge that it seems as if the water wants to tear this hole into a cavity. Result, the areas around the corners of the hole feel extremely high stress and may eventually give in.

wat happens to ppl who have been restrained to do certain things in life but have a willingness to do it? Well if the restrain is too big, the desire may keep on building(may sometime spill over the restrain). If someday the person finds a small opening through this restrain the person will try to use this opening with such ferosity that even the person does not know what she/he has lost bcoz of the usage of the opening.

There are game watchers and players watching the game. The watchers try to assosciate themselves with the players and wish for the corresponding players win. There are a few players who as well watch the game. They may support some players as other watchers. They may also keep on learning things to play better. Suppose there is a group of players watching the game and except one others pass the wall and get on the other side of game and start playing in the game? What will the only player watching the game feel............a moving surge to get into the game at the slightest opportunity??

What Famous Leader Are You?
personality tests by similarminds.com

CollateCsv

Public Sub AlignDate()
Dim X() As Variant
Dim shtArr() As Integer
Dim smallest_date As Date
Dim largest_date As Date
Dim FwdFill As Boolean
Dim BusDays As Boolean
Dim inputDate As Boolean
Sheets(3).Activate
Cells.ClearContents
ActiveWindow.FreezePanes = False
On Error Resume Next
Start:
If Sheet1.FileTypecsv.Value Then
FileType = Sheet1.FileTypecsv.Caption
mulSel = True
X = Application.GetOpenFilename(filefilter:="CSV Files,*.csv", _
MultiSelect:=True, Title:="File(s) to be aligned")
'Tests the variable X to see if it is valid
If UBound(X) = 0 Then Exit Sub
ReDim shtArr(1)
shtArr(1) = 1
If (MsgBox("Would you like to input min and max dates for alignment?", vbYesNo) = vbYes) Then
inputDate = False
Dim temp_smallest_date As Date
Dim temp_largest_date As Date
temp_smallest_date = minDateCSV(X())
temp_largest_date = maxDateCSV(X())
smallest_date = Application.InputBox("Enter min date(mm/dd/yyyy)", "Date Entry", , 250, 75, "", , 2)
If smallest_date = False Then Exit Sub
largest_date = Application.InputBox("Enter max date(mm/dd/yyyy)", "Date Entry", , 250, 75, "", , 2)
If largest_date = False Then Exit Sub
If temp_smallest_date < smallest_date Then smallest_date = temp_smallest_date
If temp_largest_date > largest_date Then largest_date = temp_largest_date
Else
Application.ScreenUpdating = False
inputDate = True
smallest_date = minDateCSV(X())
largest_date = maxDateCSV(X())
End If
ElseIf Sheet1.FileTypexls.Value Then
MsgBox "Still working on Excel file handling. Please use this tool for csv only."
Exit Sub
FileType = Sheet1.FileTypexls.Caption
mulSel = False
Target = Application.GetOpenFilename(filefilter:=FileType & " Files,*." & FileType, _
MultiSelect:=mulSel, Title:="File to be aligned")
If Target = False Then Exit Sub
ReDim X(1) As Variant
X(1) = Target
smallest_date = minDateXL(X(), shtArr())
largest_date = maxDateXL(X(), shtArr())
Else
MsgBox Prompt:="Select a File Type First", Buttons:=vbCritical
Exit Sub
End If
Dim fileName As String
fileName = ThisWorkbook.Name
currfileName = ThisWorkbook.Name
Application.ScreenUpdating = False
For Y = 1 To UBound(X)
Workbooks.Open X(Y)
currfileName1 = ActiveWorkbook.Name
Workbooks(currfileName1).Sheets(1).Copy After:=Workbooks(currfileName).Sheets(2 + Y)
Windows(currfileName1).Close
Next
X(1) = ActiveWorkbook.FullName

Windows(currfileName).Activate
Sheets(3).Activate
Cells("A3").Select
Dim range_len As Integer
Range("A1").Value = "Asset:"
Range("A2").Value = "date"
range_len = DateDiff("d", smallest_date, largest_date)
Range("A3").Value = smallest_date
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A" & range_len + 3), Type:=xlFillDefault
last_column = 2
Sheets(3).Activate
Dim datelocal As String
datelocal = Cells(row, 1).Value
For files2handle = 4 To ActiveWorkbook.Sheets.Count
Sheets(files2handle).Activate
Cells.End(xlEnd).Select
next_last_column = ActiveCell.Column
Range(Cells(1, ActiveCell.Column), Cells(2, 2)).Select
Selection.Copy
Sheets(3).Activate
Cells(1, last_column).Activate
ActiveCell.PasteSpecial (xlPasteAll)
Cells(3, last_column).Select
last_column = last_column + next_last_column - 1
Next

Sheets(3).Activate
Dim mat_row As Integer
For row = 3 To range_len + 3
Sheets(3).Activate
next_last_column = 1
last_column = 2
Dim test_val As Date
test_val = Cells(row, 1).Value
On Error Resume Next
For files2handle = 4 To ActiveWorkbook.Sheets.Count
last_column = last_column + next_last_column - 1
Sheets(files2handle).Activate
Cells.End(xlEnd).Select
next_last_column = ActiveCell.Column
Range("A1").Select
Cells.Find(What:=test_val, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
If ActiveCell.row <> 1 Then
mat_row = ActiveCell.row
Cells(mat_row, 1).Activate
Range(Cells(mat_row, 2), Cells(mat_row, next_last_column)).Copy
Sheets(3).Activate
Cells(row, last_column).Activate
ActiveCell.PasteSpecial xlPasteAll
End If
done:

Next
Next
Sheets(3).Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="", Replacement:="NA", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B3").Select
Sheets(4).Select
For i = 4 To ActiveWorkbook.Sheets.Count
Sheets(i).Select False
Next
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets(2).Select
Application.ScreenUpdating = False
If Sheet1.FwdFillYes.Value Then
FwdFill = True
ElseIf Sheet1.FwdFillNo.Value Then
FwdFill = False
Else
MsgBox Prompt:="Select Whether to forward fill or not", Buttons:=vbCritical
Exit Sub
End If
If Sheet1.BusDaysYes.Value Then
BusDays = True
ElseIf Sheet1.BusDaysNo.Value Then
BusDays = False
Else
MsgBox Prompt:="Select Whether to have only business days or not", Buttons:=vbCritical
Exit Sub
End If




Sheets(3).Select
Range("B1").Select
Dim lastRow As Integer
lastRow = Selection.End(xlDown).row
Range("A1").Select
Dim lastCol As Integer
lastCol = Selection.End(xlToRight).Column
If FwdFill Then
Dim previousRow As Integer
Dim previousCol As Integer
previousRow = 0
'Cells.Find(What:="NA", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
'While (ActiveCell.row <= lastRow And (ActiveCell.row > previousRow) Or _
(ActiveCell.row = previousRow And ActiveCell.Column > previousCol))
For row = 1 To lastRow
For col = 2 To lastCol
Cells(row, col).Select
If (ActiveCell.Value = "NA") Then
previousRow = ActiveCell.row
previousCol = ActiveCell.Column
If (IsNumeric(ActiveCell.Offset(-1, 0).Value)) Then
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value
End If
End If
'Cells.Find(What:="NA", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Next
Next
End If
'den:
Range("A1").Select
'On Error Resume Next
If BusDays Then
Range("A1").Select
'Dim i As Integer
For i = 1 To lastRow
If (IsDate(Cells(i, 1).Value)) Then
If (DatePart("w", Cells(i, 1).Value) = 1 Or DatePart("w", Cells(i, 1).Value) = 7) Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
End If
Next
End If
Application.ScreenUpdating = True
Sheets(3).Select
Range("B3").Select
Cells.EntireColumn.AutoFit
Range("B3").Select
ActiveWindow.FreezePanes = True
Exit Sub






























Application.ScreenUpdating = True
If (DateDiff("d", smallest_date, largest_date) <= 0) Then
MsgBox Prompt:="Issue with data. Max date can not be less than or Equal to Min date. Check!!", Buttons:=vbCritical
Exit Sub
End If
If MsgBox("Date Range:- " & smallest_date & " To " & largest_date, vbOKCancel) = vbCancel Then
Exit Sub
End If
Call alignFiles(X(), smallest_date, largest_date, FwdFill, BusDays, fileName)
Windows(fileName).Activate
If Sheet1.CollDataYes.Value Then
If Sheet1.BusDaysYes.Value = True Then
If inputDate = True Then
For Z = 4 To ActiveWorkbook.Sheets.Count
If Z = 4 Then
Sheets(Z).Activate
Cells.Copy
Sheets(3).Activate
Range("A1").PasteSpecial (xlPasteAll)
Else
Sheets(Z).Activate
Range("B1").Select
Application.CutCopyMode = False
Range("IV1").Select
Selection.End(xlToLeft).Select
Range(Selection, "B1").Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets(3).Activate
Range("A1").Select
Selection.End(xlToRight).Offset(0, 1).Select
ActiveSheet.Paste
End If
Next
Sheets(3).Activate
If IsDate(ActiveSheet.Range("A2").Value) Then
Range("B2").Select
Else
Range("B3").Select
End If
Cells.EntireColumn.AutoFit
ActiveWindow.FreezePanes = True
Else
MsgBox "Data Can not be Collated if date range has been given as input by user"
End If
Else
MsgBox "Data can be collated only if OnlyBusinessDays is True"
End If
Else
Sheets("Run").Range("A4").Select
End If
Application.DisplayAlerts = False
For Z = 4 To ActiveWorkbook.Sheets.Count
Sheets(4).Delete
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'If Sheet1.CollDataNo Or Not (inputDate) Then
Sheets("Run").Activate
'End If
End Sub
Private Function minDateCSV(X() As Variant) As Date
Dim smallDates As Date
Dim smallDate As Date
For Y = 1 To UBound(X)
Workbooks.Open X(Y)
Sheets(1).Activate
' Assumption: The Csv file has atleast one line of header and the date is in the 1st column itself. Phew!wat a relief
If IsDate(ActiveSheet.Range("A2").Value) Then
smallDates = ActiveSheet.Range("A2").Value
Else
'If the date is not in the A2 then it is definitely in A3.
smallDates = ActiveSheet.Range("A3").Value
End If
If Y > 1 Then
If (DateDiff("d", smallDates, smallDate) > 0) Then
smallDate = smallDates
End If
Else
smallDate = smallDates
End If
ActiveWorkbook.Close
Next
minDateCSV = smallDate
End Function
Private Function maxDateCSV(X() As Variant) As Date
Dim largeDates As Date
Dim largeDate As Date
Dim sheet_index As Integer
For Y = 1 To UBound(X)
Workbooks.Open X(Y)
Sheets(1).Activate
' Assumption: The Csv file has more than two lines of input and the date is in column A
' and the data is continuous
largeDates = ActiveSheet.Range("A1").End(xlDown).Value
If Y > 1 Then
If (DateDiff("d", largeDates, largeDate) < 0) Then
largeDate = largeDates
End If
Else
largeDate = largeDates
End If
ActiveWorkbook.Close
Next
maxDateCSV = largeDate
End Function

Private Sub alignFiles(X() As Variant, smallest_date As Date, largest_date As Date, FwdFill As Boolean, BusDays As Boolean, fileName As String)
smallest_value = smallest_date
largest_value = largest_date
sheet_index = 1
tdate = smallest_value
'Populating NA and aligning the data
For Y = 1 To UBound(X)
'Workbooks.Open X(Y)
'fileName1 = ActiveWorkbook.Name
For sheet_index = 1 To ActiveWorkbook.Sheets.Count
row = Row_Col(sheet_index)
col = 1
tdate = smallest_value
If row = 0 Then
GoTo x2
End If
Do Until tdate > largest_value
If row = 65537 Then
GoTo x2:
End If
If Sheets(sheet_index).Cells(row, col).Value <= tdate And Sheets(sheet_index).Cells(row, col).Value <> "" Then
GoTo X:
'Ignoring Saturdays and Sundays
Else 'If (DatePart("w", tdate) <> 1 And DatePart("w", tdate) <> 7) Then
Sheets(sheet_index).Cells(row, col).EntireRow.Insert
Sheets(sheet_index).Cells(row, col) = tdate
Sheets(sheet_index).Activate
Sheets(sheet_index).Range(Cells(row, 2), Cells(row, _
Cells(row - 1, 1).End(xlToRight).Column)).Value = "NA"
End If
X:
If (tdate = Sheets(sheet_index).Cells(row, col).Value) Or DatePart("w", tdate) = 1 Or DatePart("w", tdate) = 7 Then
tdate = DateAdd("d", 1, tdate)
End If
If DatePart("w", tdate) <> 1 And DatePart("w", tdate) <> 7 Then
row = row + 1
End If
Loop
x2:
Next
'On Error GoTo den
Range("B1").Select
Dim lastRow As Integer
lastRow = Selection.End(xlDown).row
Range("A1").Select
Dim lastCol As Integer
lastCol = Selection.End(xlToRight).Column
If FwdFill Then
Dim previousRow As Integer
Dim previousCol As Integer
previousRow = 0
'Cells.Find(What:="NA", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
'While (ActiveCell.row <= lastRow And (ActiveCell.row > previousRow) Or _
(ActiveCell.row = previousRow And ActiveCell.Column > previousCol))
For row = 1 To lastRow
For col = 2 To lastCol
Cells(row, col).Select
If (ActiveCell.Value = "NA") Then
previousRow = ActiveCell.row
previousCol = ActiveCell.Column
If (IsNumeric(ActiveCell.Offset(-1, 0).Value)) Then
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value
End If
End If
'Cells.Find(What:="NA", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Next
Next
End If
'den:
Range("A1").Select
'On Error Resume Next
If BusDays Then
Range("A1").Select
Dim i As Integer
For i = 1 To lastRow
If (IsDate(Cells(i, 1).Value)) Then
If (DatePart("w", Cells(i, 1).Value) = 1 Or DatePart("w", Cells(i, 1).Value) = 7) Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
End If
Next
Workbooks(fileName1).Sheets(1).Copy After:=Workbooks(fileName).Sheets(2 + Y)
Windows(fileName1).Activate
Range("A1").Select
End If
Next
End Sub
Private Function Row_Col(i) As Integer
Dim row, col As Integer
row = 1
col = 1
Do Until Sheets(i).Cells(row, col).Value = ""
If IsDate(Sheets(i).Cells(row, col).Value) Then
Row_Col = row
Exit Function
Else
row = row + 1
End If
Loop
End Function
Private Function minDateXL(X() As Variant, shtArr() As Integer) As Date
Dim smallDates As Date
Dim smallDate As Date
For Y = 1 To UBound(X)
Workbooks.Open X(Y)
For sheet_index = 1 To ActiveWorkbook.Sheets.Count
Sheets(sheet_index).Activate
If (MsgBox("Do you want to include the tab " & ActiveSheet.Name & " in alignment process?", vbYesNo) = vbNo) Then
GoTo hmmm
End If
' Assumption: The Csv file has atleast one line of header and the date is in the 1st column itself. Phew!wat a relief
If IsDate(ActiveSheet.Range("A2").Value) Then
smallDates = ActiveSheet.Range("A2").Value
Else
'If the date is not in the A2 then it is definitely in A3.
smallDates = ActiveSheet.Range("A3").Value
End If
If Y > 1 Then
If (DateDiff("d", smallDates, smallDate) > 0) Then
smallDate = smallDates
End If
Else
smallDate = smallDates
End If
hmmm:
Next
ActiveWorkbook.Close
Next
minDateXL = smallDate
End Function
Private Function maxDateXL(X() As Variant, shtArr() As Integer) As Date
Dim largeDates As Date
Dim largeDate As Date
For Y = 1 To UBound(X)
Workbooks.Open X(Y)
For sheet_index = 1 To ActiveWorkbook.Sheets.Count
Sheets(sheet_index).Activate
If (MsgBox("Do you want to include the tab " & ActiveSheet.Name & " in alignment process?", vbYesNo) = vbNo) Then
GoTo hmmm
End If
' Assumption: The Csv file has more than two lines of input and the date is in column A
' and the data is continuous
largeDates = ActiveSheet.Range("A1").End(xlDown).Value
If Y > 1 Then
If (DateDiff("d", largeDates, largeDate) < 0) Then
largeDate = largeDates
End If
Else
largeDate = largeDates
End If
hmmm:
Next
ActiveWorkbook.Close
Next
maxDateXL = largeDate
End Function

book

http://www.bookgo.org/all.html
http://programmingebook.blogspot.com/2008/07/introduction-to-c-for-financial.html
http://www.esnips.com

VBA-Alignment of dates

Public Sub AlignDate()
Dim X() As Variant
Dim shtArr() As Integer
Dim smallest_date As Date
Dim largest_date As Date
Dim fwdFill As Boolean
Application.ScreenUpdating = False
Start:
If Sheet1.OptionButton1.Value Then
fileType = Sheet1.OptionButton1.Caption
mulSel = True
X = Application.GetOpenFilename(filefilter:=fileType & " Files,*." & fileType, _
MultiSelect:=mulSel, Title:="File(s) to be aligned")
'Tests the variable X to see if it is valid
If UBound(X) = 0 Then Exit Sub
ReDim shtArr(1)
shtArr(1) = 1
smallest_date = minDateCSV(X(), shtArr())
largest_date = maxDateCSV(X(), shtArr())
ElseIf Sheet1.OptionButton2.Value Then
fileType = Sheet1.OptionButton2.Caption
mulSel = False
Target = Application.GetOpenFilename(filefilter:=fileType & " Files,*." & fileType, _
MultiSelect:=mulSel, Title:="File(s) to be aligned")
If Target = False Then Exit Sub
ReDim X(1) As Variant
X(1) = Target
smallest_date = minDateXL(X(), shtArr())
largest_date = maxDateXL(X(), shtArr())
Else
MsgBox Prompt:="Select a File Type First", Buttons:=vbCritical
Exit Sub
End If
If Sheet1.OptionButton3.Value Then
fwdFill = True
ElseIf Sheet1.OptionButton4.Value Then
fwdFill = False
Else
MsgBox Prompt:="Select Whether to forward fill or not", Buttons:=vbCritical
Exit Sub
End If
MsgBox smallest_date
Application.ScreenUpdating = True
MsgBox largest_date
If (DateDiff("d", smallest_date, largest_date) <= 0) Then
MsgBox Prompt:="Issue with data. Max date can not be less than or Equal to Min date. Check!!", Buttons:=vbCritical
Exit Sub
End If
If MsgBox("Date Range:- " & smallest_date & " To " & largest_date, vbOKCancel) = vbCancel Then
Exit Sub
End If
Call alignFiles(X(), smallest_date, largest_date, fwdFill)
End Sub
Private Function minDateCSV(X() As Variant, shtArr() As Integer) As Date
Dim smallDates As Date
Dim smallDate As Date
For Y = 1 To UBound(X)
Workbooks.Open X(Y)
Sheets(1).Activate
' Assumption: The Csv file has atleast one line of header and the date is in the 1st column itself. Phew!wat a relief
If IsDate(ActiveSheet.Range("A2").Value) Then
smallDates = ActiveSheet.Range("A2").Value
Else
'If the date is not in the A2 then it is definitely in A3.
smallDates = ActiveSheet.Range("A3").Value
End If
If Y > 1 Then
If (DateDiff("d", smallDates, smallDate) > 0) Then
smallDate = smallDates
End If
Else
smallDate = smallDates
End If
ActiveWorkbook.Close
Next
minDateCSV = smallDate
End Function
Private Function maxDateCSV(X() As Variant, shtArr() As Integer) As Date
Dim largeDates As Date
Dim largeDate As Date
Dim sheet_index As Integer
For Y = 1 To UBound(X)
Workbooks.Open X(Y)
Sheets(1).Activate
' Assumption: The Csv file has more than two lines of input and the date is in column A
' and the data is continuous
largeDates = ActiveSheet.Range("A1").End(xlDown).Value
If Y > 1 Then
If (DateDiff("d", largeDates, largeDate) < 0) Then
largeDate = largeDates
End If
Else
largeDate = largeDates
End If
ActiveWorkbook.Close
Next
maxDateCSV = largeDate
End Function
Private Sub alignFiles(X() As Variant, smallest_date As Date, largest_date As Date, fwdFill As Boolean)
smallest_value = smallest_date
largest_value = largest_date
sheet_index = 1
tdate = smallest_value
'Populating NA and aligning the data
For Y = 1 To UBound(X)
Workbooks.Open X(Y)
For sheet_index = 1 To ActiveWorkbook.Sheets.Count
row = Row_Col(sheet_index)
col = 1
tdate = smallest_value
If row = 0 Then
GoTo x2
End If
Do Until tdate > largest_value
If row = 65537 Then
GoTo x2:
End If
If Sheets(sheet_index).Cells(row, col).Value <= tdate And Sheets(sheet_index).Cells(row, col).Value <> "" Then
GoTo X:
'Ignoring Saturdays and Sundays
ElseIf (DatePart("w", tdate) <> 1 And DatePart("w", tdate) <> 7) Then
Sheets(sheet_index).Cells(row, col).EntireRow.Insert
Sheets(sheet_index).Cells(row, col) = tdate
Sheets(sheet_index).Activate
For count_col = 2 To Sheets(sheet_index).Cells(row - 1, 2).End(xlToRight).Column
If fwdFill Then
If IsNumeric(Sheets(sheet_index).Cells(row - 1, count_col).Value) Then
Sheets(sheet_index).Cells(row, count_col).Value = _
Sheets(sheet_index).Cells(row - 1, count_col).Value
Else
Sheets(sheet_index).Cells(row, count_col).Value = "NA"
End If
Else
Sheets(sheet_index).Cells(row, count_col).Value = "NA"
End If
Next
End If
X:
If (tdate = Sheets(sheet_index).Cells(row, col).Value) Or DatePart("w", tdate) = 1 Or DatePart("w", tdate) = 7 Then
tdate = DateAdd("d", 1, tdate)
End If
If DatePart("w", tdate) <> 1 And DatePart("w", tdate) <> 7 Then
row = row + 1
End If
Loop
x2:
Next
Next
End Sub
Private Function Row_Col(i) As Integer
Dim row, col As Integer
row = 1
col = 1
Do Until Sheets(i).Cells(row, col).Value = ""
If IsDate(Sheets(i).Cells(row, col).Value) Then
Row_Col = row
Exit Function
Else
row = row + 1
End If
Loop
End Function
Private Function minDateXL(X() As Variant, shtArr() As Integer) As Date
Dim smallDates As Date
Dim smallDate As Date
For Y = 1 To UBound(X)
Workbooks.Open X(Y)
For sheet_index = 1 To ActiveWorkbook.Sheets.Count
Sheets(sheet_index).Activate
If (MsgBox("Do you want to include the tab " & ActiveSheet.Name & " in alignment process?", vbYesNo) = vbNo) Then
GoTo hmmm
End If
' Assumption: The Csv file has atleast one line of header and the date is in the 1st column itself. Phew!wat a relief
If IsDate(ActiveSheet.Range("A2").Value) Then
smallDates = ActiveSheet.Range("A2").Value
Else
'If the date is not in the A2 then it is definitely in A3.
smallDates = ActiveSheet.Range("A3").Value
End If
If Y > 1 Then
If (DateDiff("d", smallDates, smallDate) > 0) Then
smallDate = smallDates
End If
Else
smallDate = smallDates
End If
hmmm:
Next
ActiveWorkbook.Close
Next
minDateXL = smallDate
End Function
Private Function maxDateXL(X() As Variant, shtArr() As Integer) As Date
Dim largeDates As Date
Dim largeDate As Date
For Y = 1 To UBound(X)
Workbooks.Open X(Y)
For sheet_index = 1 To ActiveWorkbook.Sheets.Count
Sheets(sheet_index).Activate
If (MsgBox("Do you want to include the tab " & ActiveSheet.Name & " in alignment process?", vbYesNo) = vbNo) Then
GoTo hmmm
End If
' Assumption: The Csv file has more than two lines of input and the date is in column A
' and the data is continuous
largeDates = ActiveSheet.Range("A1").End(xlDown).Value
If Y > 1 Then
If (DateDiff("d", largeDates, largeDate) < 0) Then
largeDate = largeDates
End If
Else
largeDate = largeDates
End If
hmmm:
Next
ActiveWorkbook.Close
Next
maxDateXL = largeDate
End Function

VBA-Netting

Sub netting()
'
' Macro netting
'
'
'------------------------------------------------------------------------------------------
'Prepairing the tabs to receive the data. The older data from the tabs will be cleared out.
'------------------------------------------------------------------------------------------
Sheets("Full").Select 'Clearing the Contents of Full tab
Cells.Select
Selection.ClearContents
Selection.ClearContents 'Cleared
Sheets("Validation").Select 'Clearing the Validation Tab
Range("A2:Q2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Selection.ClearContents
Sheets("Long").Select 'Clearing the contents of Long tab
Cells.Select
Selection.ClearContents
Selection.ClearContents 'Cleared
Sheets("Short").Select 'Clearing the contents of Short tab
Cells.Select
Selection.ClearContents
Selection.ClearContents 'Cleared
Sheets("Short_Re").Select 'Clearing the contents of Short_Re tab
Cells.Select
Selection.ClearContents
Selection.ClearContents 'Cleared
Sheets("Long_Re").Select 'Clearing the contents of Long_Re tab
Cells.Select
Selection.ClearContents
Selection.ClearContents 'Cleared
Sheets("Result").Select 'Clearing the contents of Result tab
Cells.Select
Selection.ClearContents
Selection.ClearContents 'Cleared
Sheets("Input").Select 'Copying Header from the Input Tab
Rows("1:1").Select
Selection.Copy
Sheets("Result").Select
Range("A1").Select
ActiveSheet.Paste 'Pasted the header in the Result Tab
Selection.Font.Bold = True
Range("Y1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents 'Clearing the end part of header after column Y
Range("A1").Select
Sheets("Input").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A1:X1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Full").Select
Range("A1").Select
ActiveSheet.Paste ' Copying all the data to Full tab. We will use this tab and not the Input tab now
'------------------------------------------------------------------------------------------------
'The data from the Full tab will be moved to Short and Long tab based on the LONG/SHORT Column
'------------------------------------------------------------------------------------------------
Sheets("Full").Select
Selection.AutoFilter Field:=21, Criteria1:="Short" 'Selecting the short trades
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Short").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Full").Select
Selection.AutoFilter Field:=21, Criteria1:="Long" 'Selecting the long trades
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Long").Select
Range("A1").Select
ActiveSheet.Paste
'------------------------------------------------------------------------------------------
'Calculating the coupon in column L from the PAYFORMULA in Long Tab
'------------------------------------------------------------------------------------------
Columns("L:L").Select
Selection.Insert shift:=xlToRight
Range("L1").Select
ActiveCell.FormulaR1C1 = "Coupon"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[6],SEARCH(""%"",RC[6])-1)"
Range("A1").Select
lastRow = Selection.End(xlDown).Row
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L" & lastRow)
Range("L2:L" & lastRow).Select
'------------------------------------------------------------------------------------------
'Calculating Residual Maturity=Maturity-today in Column AC in Long Tab
'------------------------------------------------------------------------------------------
Range("AC1").Select
ActiveCell.FormulaR1C1 = "Residual Maturity"
Range("AD1").Select
ActiveCell.FormulaR1C1 = "Maturity"
Range("AD2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-10],4),MID(RC[-10],5,2),RIGHT(RC[-10],2))"
Range("AC2").Select
ActiveCell.FormulaR1C1 = "=RC[1]-TODAY()"
Range("AC2").Select
Selection.NumberFormat = "General"
Range("AC2:AD2").Select
Selection.AutoFill Destination:=Range("AC2:AD" & lastRow)
ActiveSheet.Calculate
Range("AC2:AD" & lastRow).Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Short").Select
'------------------------------------------------------------------------------------------
'Calculating the coupon in column L from the RECFORMULA in Short Tab
'------------------------------------------------------------------------------------------
Columns("L:L").Select
Selection.Insert shift:=xlToRight
Range("L1").Select
ActiveCell.FormulaR1C1 = "Coupon"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],SEARCH(""%"",RC[-1])-1)"
Range("A1").Select
lastRow = Selection.End(xlDown).Row
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L" & lastRow)
Range("L2:L" & lastRow).Select
'------------------------------------------------------------------------------------------
'Calculating Residual Maturity=Maturity-today in Column AC in Short Tab
'------------------------------------------------------------------------------------------
Range("AC1").Select
ActiveCell.FormulaR1C1 = "Residual Maturity"
Range("AD1").Select
ActiveCell.FormulaR1C1 = "Maturity"
Range("AD2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-10],4),MID(RC[-10],5,2),RIGHT(RC[-10],2))"
Range("AC2").Select
ActiveCell.FormulaR1C1 = "=RC[1]-TODAY()"
Range("AC2").Select
Selection.NumberFormat = "General"
Range("AC2:AD2").Select
Selection.AutoFill Destination:=Range("AC2:AD" & lastRow)
ActiveSheet.Calculate
Range("AC2:AD" & lastRow).Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("L14").Select
Range("A1").Select
Range("A2:AD2").Select
'Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
'--------------------------------------------------------------------------------------------------------
'Sorting the data in the Short tab. The sort order is by PAYNotional,ResidualMaturity and then by Coupon
'--------------------------------------------------------------------------------------------------------
Selection.Sort Key1:=Range("U2"), Order1:=xlAscending, Key2:=Range _
("AC2"), Order2:=xlAscending, Key3:=Range("L2"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers _
, DataOption3:=xlSortNormal
Sheets("Long").Select
Range("O10").Select
Range("A2:AD2").Select
'Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
'--------------------------------------------------------------------------------------------------------
'Sorting the data in the Long tab. The sort order is by PAYNotional,ResidualMaturity and then by Coupon
'--------------------------------------------------------------------------------------------------------
Selection.Sort Key1:=Range("U2"), Order1:=xlAscending, Key2:=Range _
("AC2"), Order2:=xlAscending, Key3:=Range("L2"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers _
, DataOption3:=xlSortNormal
'-------------------------------------------------------------------------------------------------------------
'The netting process starts here.
'The Long and Short tab gets netted for exact maturity and rest trades are marked as Not Netted.
'These not netted trades will be netted off once again in the LOng_Re and Short_Re tabs using the maturity band
'-------------------------------------------------------------------------------------------------------------
Application.ScreenUpdating = False
Dim longCoupon, shortCoupon, longNotional, shortNotional, longMaturity, shortMaturity, longPV01, shortPV01 As Double
Dim shortResidualMaturity, longResidualMaturity As Double
Dim bigL, smallL, notnettedPoint, bigloopCount, smallloopCount, finalFlag, sm_i As Integer
Dim vali_Row_Ct As Integer
finalFlag = 0
Sheets("Short").Select
Range("A1").Select
shortRows = ActiveCell.End(xlDown).Row
Sheets("Long").Select
Range("A1").Select
longrows = ActiveCell.End(xlDown).Row
bigloopCount = longrows
smallloopCount = shortRows
notnettedPoint = 2
Sheets(1).Select
vali_Row_Ct = 2
For bigL = 2 To bigloopCount
For smallL = notnettedPoint To smallloopCount
If (bigL = bigloopCount And finalFlag = 1) Then
For sm_i = smallL To smallloopCount
Sheets("Short").Activate
Range("Z" & sm_i).Value = "N"
Next sm_i
Exit For
End If
Sheets("Long").Activate
longCoupon = Range("L" & bigL).Value + 0
longNotional = Range("U" & bigL).Value
longMaturity = Range("J" & bigL).Value
longResidualMaturity = Range("AC" & bigL).Value
Sheets("Short").Activate
shortCoupon = Range("L" & smallL).Value + 0
shortNotional = Range("U" & smallL).Value
shortMaturity = Range("J" & smallL).Value
shortResidualMaturity = Range("AC" & smallL).Value
If (longNotional = shortNotional) Then
If (longResidualMaturity = shortResidualMaturity) Then
If ((longCoupon - shortCoupon) > 0.15) Then
Sheets("Short").Activate
Range("z" & smallL).Value = "N"
notnettedPoint = smallL + 1
ElseIf ((shortCoupon - longCoupon) > 0.15) Then
Sheets("Long").Activate
Range("z" & bigL).Value = "N"
If (bigL = bigloopCount) Then
Sheets("Short").Activate
Range("z" & smallL).Value = "N"
Else
Exit For
End If
Else
Sheets("Long").Activate
longPV01 = Range("Y" & bigL).Value
Sheets("Short").Activate
shortPV01 = Range("Y" & smallL).Value
If ((longPV01 - shortPV01) > 0) Then
Sheets("Long").Select
Range("z" & bigL).Value = "Y"
Range("AA" & bigL).Value = Sheets("Short").Range("B" & smallL).Value
'--------------------------For Validation --------------------------------------
Sheets("Validation").Range("A" & vali_Row_Ct).Value = Sheets("Long").Range("B" & bigL).Value
Sheets("Validation").Range("B" & vali_Row_Ct).Value = Sheets("Short").Range("B" & smallL).Value
Sheets("Validation").Range("I" & vali_Row_Ct).Value = Sheets("Long").Range("L" & bigL).Value
Sheets("Validation").Range("J" & vali_Row_Ct).Value = Sheets("Short").Range("L" & smallL).Value
Sheets("Validation").Range("M" & vali_Row_Ct).Value = Sheets("Long").Range("AC" & bigL).Value
Sheets("Validation").Range("N" & vali_Row_Ct).Value = Sheets("Short").Range("AC" & smallL).Value
vali_Row_Ct = vali_Row_Ct + 1
'-------------------------------------------------------------------------------
If (bigL = bigloopCount) Then
finalFlag = 1
Else
Exit For
End If
Else
Sheets("Short").Select
Range("z" & smallL).Value = "Y"
Range("AA" & smallL).Value = Sheets("Long").Range("B" & bigL).Value
notnettedPoint = smallL + 1
'--------------------------For Validation --------------------------------------
Sheets("Validation").Range("A" & vali_Row_Ct).Value = Sheets("Short").Range("B" & smallL).Value
Sheets("Validation").Range("B" & vali_Row_Ct).Value = Sheets("Long").Range("B" & bigL).Value
Sheets("Validation").Range("I" & vali_Row_Ct).Value = Sheets("Short").Range("L" & smallL).Value
Sheets("Validation").Range("J" & vali_Row_Ct).Value = Sheets("Long").Range("L" & bigL).Value
Sheets("Validation").Range("M" & vali_Row_Ct).Value = Sheets("Short").Range("AC" & smallL).Value
Sheets("Validation").Range("N" & vali_Row_Ct).Value = Sheets("Long").Range("AC" & bigL).Value
vali_Row_Ct = vali_Row_Ct + 1
'-------------------------------------------------------------------------------
If (bigL = bigloopCount) Then
finalFlag = 1
Else
Exit For
End If
End If
End If
Else
If ((longResidualMaturity - shortResidualMaturity) > 0) Then
Sheets("Short").Activate
Range("z" & smallL).Value = "N"
notnettedPoint = smallL + 1
Else
Sheets("Long").Activate
Range("z" & bigL).Value = "N"
If (bigL = bigloopCount) Then
Sheets("Short").Activate
Range("z" & smallL).Value = "N"
Else
Exit For
End If
End If
End If
Else
If ((longNotional - shortNotional) > 0) Then
Sheets("Short").Range("Z" & smallL).Value = "N"
notnettedPoint = smallL + 1
Else
Sheets("Long").Range("Z" & bigL).Value = "N"
If (bigL = bigloopCount) Then
Sheets("Short").Activate
Range("z" & smallL).Value = "N"
Else
Exit For
End If
End If
End If
Next smallL
Next bigL
'----------------------------------------------------------------------------
' we need to do the netting of band of maturity after the full netting is over.
'
'------------------------------------------------------------------------------
Sheets("Long").Select
Range("A1:AD1").Select
Selection.AutoFilter
Range("K1").Select
Selection.End(xlToRight).Select
Range("Z1").Select
Selection.AutoFilter Field:=26, Criteria1:="N"
Cells.Select
Range("S1").Activate
Selection.Copy
Sheets("Long_Re").Select
Cells.Select
Application.CutCopyMode = False
Selection.ClearContents
'-------------------------------------------------------------------------------
' Copying not netted trades from Long tab to Long_Re tab
'-------------------------------------------------------------------------------
Sheets("Long").Select
Selection.Copy
Sheets("Long_Re").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Short").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A1:AD1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=26, Criteria1:="N"
Cells.Select
Selection.Copy
'-------------------------------------------------------------------------------
'Copying not netted trades from Short tab to Short_Re tab
'-------------------------------------------------------------------------------
Sheets("Short_Re").Select
Cells.Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Short").Select
Selection.Copy
Sheets("Short_Re").Select
Range("A1").Select
ActiveSheet.Paste
'--------------------------------------------------------------------------------
Sheets("Short_Re").Select
Range("A1").Select
shortRows = ActiveCell.End(xlDown).Row
Sheets("Long_Re").Select
Range("A1").Select
longrows = ActiveCell.End(xlDown).Row
bigloopCount = longrows
smallloopCount = shortRows
notnettedPoint = 2
For bigL = 2 To bigloopCount
For smallL = notnettedPoint To smallloopCount
Sheets("Long_Re").Activate
longCoupon = Range("L" & bigL).Value + 0
longNotional = Range("U" & bigL).Value
longMaturity = Range("J" & bigL).Value
longNettedFlag = Range("Z" & bigL).Value
longResidualMaturity = Range("AC" & bigL).Value
Sheets("Short_Re").Activate
shortCoupon = Range("L" & smallL).Value + 0
shortNotional = Range("U" & smallL).Value
shortMaturity = Range("J" & smallL).Value
shortNettedFlag = Range("Z" & smallL).Value
shortResidualMaturity = Range("AC" & smallL).Value
If (longNettedFlag = "Y") Then
Exit For
End If
If (longNettedFlag = "N") Then
If (shortNettedFlag = "N") Then
If (longNotional = shortNotional) Then
If ((longCoupon - shortCoupon) > 0.15) Then
Sheets("Short_Re").Activate
'Range("z" & smallL).Value = "N"
'notnettedPoint = smallL + 1
ElseIf ((shortCoupon - longCoupon) > 0.15) Then
Sheets("Long_Re").Activate
Range("z" & bigL).Value = "N"
'Exit For
Else
Sheets("Long_Re").Activate
longPV01 = Range("Y" & bigL).Value
Sheets("Short_Re").Activate
shortPV01 = Range("Y" & smallL).Value
Range("AT" & smallL).Value = "Diff is less than 0.15"
If (longResidualMaturity < 30) Then
Exit For
ElseIf (shortResidualMaturity < 30) Then
'notnettedPoint = smallL + 1
ElseIf ((longResidualMaturity < 365 And shortResidualMaturity < 365 _
And (((longResidualMaturity - shortResidualMaturity) <> 0) _
Or ((shortResidualMaturity - longResidualMaturity) <> 0))) _
_
Or (longResidualMaturity > 365 And shortResidualMaturity > 365 _
And (((longResidualMaturity - shortResidualMaturity) <> 0) _
Or ((shortResidualMaturity - longResidualMaturity) <> 0)))) Then
If ((longPV01 - shortPV01) > 0) Then
Sheets("Long_Re").Select
Range("z" & bigL).Value = "Y"
Range("AA" & bigL).Value = Sheets("Short_Re").Range("B" & smallL).Value
Sheets("Short_Re").Select
Range("z" & smallL).Value = ""
'notnettedPoint = smallL + 1
'--------------------------For Validation --------------------------------------
Sheets("Validation").Range("A" & vali_Row_Ct).Value = Sheets("Long_Re").Range("B" & bigL).Value
Sheets("Validation").Range("B" & vali_Row_Ct).Value = Sheets("Short_Re").Range("B" & smallL).Value
Sheets("Validation").Range("I" & vali_Row_Ct).Value = Sheets("Long_Re").Range("L" & bigL).Value
Sheets("Validation").Range("J" & vali_Row_Ct).Value = Sheets("Short_Re").Range("L" & smallL).Value
Sheets("Validation").Range("M" & vali_Row_Ct).Value = Sheets("Long_Re").Range("AC" & bigL).Value
Sheets("Validation").Range("N" & vali_Row_Ct).Value = Sheets("Short_Re").Range("AC" & smallL).Value
vali_Row_Ct = vali_Row_Ct + 1
'-------------------------------------------------------------------------------
Exit For
Else
Sheets("Short_Re").Select
Range("z" & smallL).Value = "Y"
Range("AA" & smallL).Value = Sheets("Long_Re").Range("B" & bigL).Value
Sheets("Long_Re").Select
Range("z" & bigL).Value = ""
'notnettedPoint = smallL + 1
'--------------------------For Validation --------------------------------------
Sheets("Validation").Range("A" & vali_Row_Ct).Value = Sheets("Short_Re").Range("B" & smallL).Value
Sheets("Validation").Range("B" & vali_Row_Ct).Value = Sheets("Long_Re").Range("B" & bigL).Value
Sheets("Validation").Range("I" & vali_Row_Ct).Value = Sheets("Short_Re").Range("L" & smallL).Value
Sheets("Validation").Range("J" & vali_Row_Ct).Value = Sheets("Long_Re").Range("L" & bigL).Value
Sheets("Validation").Range("M" & vali_Row_Ct).Value = Sheets("Short_Re").Range("AC" & smallL).Value
Sheets("Validation").Range("N" & vali_Row_Ct).Value = Sheets("Long_Re").Range("AC" & bigL).Value
vali_Row_Ct = vali_Row_Ct + 1
'-------------------------------------------------------------------------------
Exit For
End If
End If
End If
Else
If ((longNotional - shortNotional) > 0) Then
'Sheets("Short_Re").Range("Z" & smallL).Value = "N"
'notnettedPoint = smallL + 1
Else
'Sheets("Long_Re").Range("Z" & bigL).Value = "N"
Exit For
End If
End If
Else
'notnettedPoint = smallL + 1
End If
End If
Next
Next
'--------------------------------------------------------------------------------------
' Data needs to be moved from the Long and Short tabs to the Result tab
'--------------------------------------------------------------------------------------
Sheets("Long_Re").Select
Range("A1:AA1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=26, Criteria1:="N"
'Range(Selection, Selection.End(xlToRight)).Select
Range("A1:AA1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Result").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("2:2").Select
Selection.Delete shift:=xlUp
Sheets("Short_Re").Select
Range("A1:AA1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=26, Criteria1:="N"
'Range(Selection, Selection.End(xlToRight)).Select
Range("A1:AA1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Result").Select
Range("A65536").Select
Selection.End(xlUp).Select
Selection.Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("Z2").Select
row_delete = Selection.End(xlDown).Row + 1
If (row_delete >= 65536) Then
row_delete = 2
End If
Cells(row_delete, 1).Select
Range(Selection, "AB" & row_delete).Select
Application.CutCopyMode = False
Selection.Cut
Rows("1:1").Select
ActiveSheet.Paste
Cells(row_delete, 1).Select
Range(Selection, "AB" & row_delete).Select
Selection.Delete shift:=xlUp
Rows("1:1").Select
Selection.Font.Bold = True
Range("A1").Select
'----------------------------------------Validation Sheet Checks ----------------------------
Sheets("Validation").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Input!C2:C24,23,0)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Input!C2:C24,23,0)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Input!C2:C10,9,0)"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Input!C2:C10,9,0)"
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-2],4),MID(RC[-2],5,2),RIGHT(RC[-2],2))-DATE(LEFT(RC[-1],4),MID(RC[-1],5,2),RIGHT(RC[-1],2))"
Range("H3").Select
Range("C2:H2").Select
If vali_Row_Ct > 3 Then Selection.AutoFill Destination:=Range("C2:H" & vali_Row_Ct - 1)
Range("K2").Select
ActiveCell.FormulaR1C1 = "=IF(ABS(RC[-2]-RC[-1])<0.15,""Ok"",""Check"")"
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-11],Input!C[-10]:C[2],13,0)-VLOOKUP(RC[-10],Input!C[-10]:C[2],13,0)"
Range("K2:L2").Select
If vali_Row_Ct > 3 Then Selection.AutoFill Destination:=Range("K2:L" & vali_Row_Ct - 1)
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]>365,IF(ABS(RC[-1]-RC[-2])<31,""ok"",""check""),if(rc[-1]>30,IF(ABS(RC[-1]-RC[-2])<8,""Ok"",""Check""),IF(RC[-1]=RC[-2],""Ok"",""Check"")))"
Range("O2").Select
If vali_Row_Ct > 3 Then Selection.AutoFill Destination:=Range("O2:O" & vali_Row_Ct - 1)
ActiveSheet.Calculate
Range("A1").Select
'----------------------------------Validation in results sheets ------------------------------
Application.ScreenUpdating = True
Sheets("Result").Select
Range("A1").Select
fin_row = Selection.End(xlDown).Row
Range("Z1").Select
ActiveCell.FormulaR1C1 = "PV01 Orig"
Range("AA1").Select
ActiveCell.FormulaR1C1 = "PV01 Check with Orig"
Range("AB1").Select
ActiveCell.FormulaR1C1 = "Check for inclusion of netted deals"
Range("AC1").Select
ActiveCell.FormulaR1C1 = "Zone Orig"
Range("AD1").Select
ActiveCell.FormulaR1C1 = "Zone Check"
Range("Z2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-24],Input!C[-24]:C[-2],23,0)"
Range("AA2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("AB2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-26],Validation!C[-27],1,0)),""Ok"",""Check"")"
Range("AB3").Select
Range("AC2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-27],Input!C[-27]:C[-6],22,0)"
Range("AD2").Select
ActiveCell.FormulaR1C1 = "=RC[-6]=RC[-1]"
Range("Z2:AD2").Select
Selection.AutoFill Destination:=Range("Z2:AD" & fin_row)
ActiveSheet.Calculate
Range("A1").Select
End Sub

Excel to csv Vb

Dim fso
Set fso = CreateObject("scripting.filesystemobject")
Dim fldr
Dim extn
Dim flename
Dim oxlApp
Set oxlApp =CreateObject("Excel.Application")
Dim oxlWB
Set fldr = fso.GetFolder("Folder path")
oxlApp.Visible = True
For Each flename In fldr.Files
extn = Right(flename.Name, 4)
If extn = ".xls" Then
Set oxlWB = oxlApp.Workbooks.Open(flename)
for j = 1 to oxlWB.Sheets.Count
oxlWB.Sheets(j).Activate
Dim Name
Name = oxlWB.Sheets(j).Name
Name = "Folder Path" & Name & ".csv"
oxlWB.SaveAs Name, -4158
Next
oxlWB.Close False
End If
Next
oxlApp.Quit
Set oxlApp = Nothing

--

There are ppl who tend to be curious by nature and there are ppl who are curiosity solver. There is a bit of difference between the two seemingly close groups. One will raise question and lot of questions. The other will try to give an answe to these. Many a times we get to know sth and then have some doubt. Certain ppl will immediately go to smbdy who gave them the initial knowledge regarding the subject and will get the matter clarified/ask the question there and will ask for an explanation. Some ppl will look for the answers themselves and will try not to ask anybdy regarding these. Then, they may get an answer or may not. Both these kinds have their benefit and drawbacks. The first group knows the answer more easily and quickly and probably the discussion with some knowledgeable person helps them get a better understanding. The other group will know all the hurdles while looking for the solution. Sometimes this grp will not have a solution and will know abt a lot more problems. But, the time will be definitely large.

Does every thing which bothers us really a part of our thought process or we are made to think about these things by other factors? Are we really concerned about certain things or are we responding to an external stimuli?
For example, are we really concerned when a boy goes down in a well or are we just responding to the sensationalization of the news being fed down our throat by the all powerful media?
Do all the ppl taking out a march/protest/dharna really believe in its cause? Ya many poor come to these because they are lured by leaders to come. But, are the so called educated ones support the causes because these are right as per their conscience? Or are there conscience skewed because of external factors like friends, society, media, etc.? We keep on hearing that India has not made much of development/has made sufficient development and we will find a lot of ppl supporting both part of it. But, how many percentage of both these groups really have cared to get the data on their own to validate their support? I hope majority does!!
There are a few things where one turns blind eye to a part of issue and raise a lot of concern to the other part of the same issue:
Politicans - they are in general bad ppl and they make huge amt of money and make undue advantage of their power and positions. PPl think they shd be removed but nobody is ready to take their place even if they are removed. The ppl who are ready to take their place are in the same category as the current set of politicians. So what will happend even if the current politicians go? The new set will be same only the faces will be changed!
Reservations: These should go as India has come too far and these are not at all useful in current scenario and are creating much problem. Well what about the reservations given by religion? It seems the priests post is 100% reserved.

Thoughts

Rent in powai -18k for a 2BHK last year, owner changes in a yr. New owner asks for 25k. Some corporate is ready to pay!!

Work in a I-Bank - Fill some data in some excel , graph it and then send to different ppl...wow, interesting high end work!!

Raw Petrol Price increases - Govt cant increase the price in mkt, the elections are near. Put a surcharge in the income tax => The common man travelling on bus is paying for a business man travelling in Merc.

A Journey to Bihar

Well, I went to my village in north Bihar and on my way back, I got the opportunity to catch the glimpses of some parts of life in Bihar and here it is.

Patna Sahib Gurudwara

Ganges from Mahatma Gandhi Setu

A Home in Village

Village Nukkad

Travelling on roof-top

This is just a vague glimpse of an exciting life in Bihar which is way different from what it looks from outside.

Ideas

I will try to list down some nice ideas and related issues which I heard of:

---- Putting wind turbines over the trains: - Won't the wind resistance increase and hence the speed decrease because of this. I think it will definitely cause the energy to be drained from the trains.

---- Giving some sort of locating device over the TV remote: This will make the remotes costlier and may not seem as a good idea to those who are quite methodical bcoz they will always know where the remote is but this shd come as a huge relief for bachelors

---- Palm/Finger print enabled door opening device: This device will have the functionality to open automatically if the person whose Palm/Finger print has been entered into device's database. Actually it will try to match the print as soon as somebody touches the device, it will match it with the database and based on match it will open the door. IT ALREADY EXISTS IN MOVIES, I guess. Drawback: Ppl will have to take off gloves.

----- Presenting holographic image of the person standing on the door. This will take the holographic technology to the homes and will make the door alarms/viewers too costly but it will be for the higher levels.

----- Making a rules engine for comparing two excel files. This is a tool where a repetitive taks of reconciliation of two excel file will be done. One needs to define which cells/rows needs to be compared and the difference may be written in a third excel file. This software must be there somewhere already.

----- Giving a coating of non-inflammable layer on the carriers of inflammable products. What may be different here may be the layering. Probably teh carriers need to have two walls and between the walls there should be a non-inflammable gas or liquid in between the two walls.

----- Converting Sound Energy to Electrical/Mechanical Energy. We should try to capture the noise pollution being generated by ever increasing traffic on roads and try to put this energy to some good use, may be to lighting the streets or running something which can absorb CO2 or SO2 etc.

Who is She?

Apparently this picture is doing rounds and is being claimed by many as Bill gates' daughter. But, Infact she is Rachael Leigh Cook, an actress..... the picture is anyways too pretty!!