rtod2
asked on
Notes for rm data type
Once the rm data type issue has been fixed here, for this workbook we need to employ a new method for moving notes. What needs to happen is clearly understood by Gowflow based on some discussion here.
Thank you Goflow, for your continued assistance on this!
A restatement of what should take place and of the code itself has been re-pasted below. It is important that the workbook itself not be posted as an attachment but rather only the corrected code.
NOTE COPYING PROCEDURE FOR RM DATA
The following applies to matching the Notes from the correct Spread in the Order History with the correct Spread in the Trade History and placing them into the Output tab once the Trade History has been moved over.
Note also that this Notes copying procedure is for rm data only and should remain separate from the Notes copying procedure for pm data, and it is not possible to use the name of the tab to determine which is rm and which is pm data because that name will change to reflect whatever the name of the originating CSV file happens to be. Here we discuss only the note copying procedure for the rm data type.
I think the process of matching the Notes is somewhat less painful than once thought because we can at least use the Description column of the Order History to separate the Notes from the actual Order. We will be trying to use this Description column to match the Notes in the Order History with the correct Spread in Trade History data after it has been copied to the Output tab.
Below is an example of one broken out that can be found in "line 824 of rm full few spreads some notes" (i.e. BUY +1 VERTICAL SPY 100 NOV 11 121/118 PUT) and refer to the following columns in Trade History as follows.
Side (BUY)
Qty (+1)
Spread (VERTICAL)
Symbol (SPY)
Not Referenced (100 is not referred to in Trade History)
Exp (NOV 11)
Strike (121/118 would could just as easily be 121/118/110 or just 121 or more or less)
Type (PUT which could just as easily be PUT/CALL/PUT or more or less)
In some cases data in the description for the Exp and Strike columns may not exist at all.
After the macro is run, the Notes should appear underneath the entire Spread in the Output tab and only one note is allocated for any one Spread. Note that sometimes there will not be any data for Strike or Exp (i.e. 100 NOV 11 in the example above).
Thank you again, sir!
Thank you Goflow, for your continued assistance on this!
A restatement of what should take place and of the code itself has been re-pasted below. It is important that the workbook itself not be posted as an attachment but rather only the corrected code.
NOTE COPYING PROCEDURE FOR RM DATA
The following applies to matching the Notes from the correct Spread in the Order History with the correct Spread in the Trade History and placing them into the Output tab once the Trade History has been moved over.
Note also that this Notes copying procedure is for rm data only and should remain separate from the Notes copying procedure for pm data, and it is not possible to use the name of the tab to determine which is rm and which is pm data because that name will change to reflect whatever the name of the originating CSV file happens to be. Here we discuss only the note copying procedure for the rm data type.
I think the process of matching the Notes is somewhat less painful than once thought because we can at least use the Description column of the Order History to separate the Notes from the actual Order. We will be trying to use this Description column to match the Notes in the Order History with the correct Spread in Trade History data after it has been copied to the Output tab.
Below is an example of one broken out that can be found in "line 824 of rm full few spreads some notes" (i.e. BUY +1 VERTICAL SPY 100 NOV 11 121/118 PUT) and refer to the following columns in Trade History as follows.
Side (BUY)
Qty (+1)
Spread (VERTICAL)
Symbol (SPY)
Not Referenced (100 is not referred to in Trade History)
Exp (NOV 11)
Strike (121/118 would could just as easily be 121/118/110 or just 121 or more or less)
Type (PUT which could just as easily be PUT/CALL/PUT or more or less)
In some cases data in the description for the Exp and Strike columns may not exist at all.
After the macro is run, the Notes should appear underneath the entire Spread in the Output tab and only one note is allocated for any one Spread. Note that sometimes there will not be any data for Strike or Exp (i.e. 100 NOV 11 in the example above).
Thank you again, sir!
Option Explicit
'--> This macro takes either pm or rm tabs and creates a properly formatted table in a new tab'
'called Output with the data from the Trade History section of the original tab. It also takes'
'the notes from the Order History section of the original tab and then indexes and sorts each'
'Spread into meaningful positions.'
'Future development will include hiding the Leg and Spread numbers, as well as making some
'calculations on each overall position.'
Const AOH = "Account Order History"
Const ATH = "Account Trade History"
Const PAL = "Profits and Losses"
Const EQT = "Equities"
Const OPT = "Options"
Dim wsopt As Worksheet
Private Sub AddMOColumn()
'This Sub will Add A new Column before Exp Column J that should indicate either of the following
'M for Monthly when the date in Exp is formated as Mmm-yy the date should be the 3rd friday of the month
'O for Weekly or Quarterly options
Dim MaxRow As Long, I As Long
Dim dDate As Date
Dim ThirdThuOfTheMonth As Date
Dim a
MaxRow = wsopt.Rows(wsopt.Rows.Count).End(xlUp).Row
wsopt.Select
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
wsopt.Range("J11") = "OT"
For I = 12 To MaxRow
If wsopt.Cells(I, "K") <> "" Then
If wsopt.Cells(I, "K").NumberFormat = "mmm-dd-yy" Then
wsopt.Cells(I, "J") = "O"
Else
wsopt.Cells(I, "J") = "M"
dDate = wsopt.Cells(I, "K")
dDate = DateSerial(Year(dDate), Month(dDate), 1)
Select Case Weekday(dDate)
Case 5 'If Date is a Thursday then add 14 days
ThirdThuOfTheMonth = DateValue(dDate + 14)
Case Is > 5 'If Date is Friday or saturday then add 21+1 days
ThirdThuOfTheMonth = DateValue(dDate + 21 - (Weekday(dDate) - vbThursday))
Case Is < 5 'If Date is Sunday to Thurthday then add 14+ diffrence to friday days
ThirdThuOfTheMonth = DateValue(dDate + 14 + vbThursday - Weekday(dDate))
End Select
If Weekday(ThirdThuOfTheMonth) <> vbThursday Then
MsgBox ("this date: " & ThirdThuOfTheMonth & " does not corespond to a Thursday! on row " & I & " Please check that date in Col K is actually a valid date then re-run the whole macro.")
End If
wsopt.Cells(I, "K") = ThirdThuOfTheMonth
End If
End If
Next I
End Sub
Private Sub AddDaysandPLColumn()
'This Sub will Add 3 new Columns
'Days to Exp in Col N next to type Column which should be the diffrence in days between Exec Time and Exp
'P/L Balance Col R
'P/L% Col S
Dim MaxRow As Long, I As Long
Dim dDate As Date
Dim ThirdThuOfTheMonth As Date
Dim a
MaxRow = wsopt.Rows(wsopt.Rows.Count).End(xlUp).Row
wsopt.Select
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
wsopt.Range("N11") = "Days to Exp"
Columns("R:S").Select
Selection.Insert Shift:=xlToRight
wsopt.Range("R11") = "P/L Balance"
wsopt.Range("S11") = "P/L %"
For I = 12 To MaxRow
If wsopt.Cells(I, "K") <> "" Then
wsopt.Cells(I, "N") = DateValue(wsopt.Cells(I, "C")) - DateValue(wsopt.Cells(I, "K"))
End If
Next I
End Sub
Sub TradeReport()
Dim wsPM As Worksheet
Dim aCell As Range, bCell As Range, cCell As Range, delRange As Range
Dim AOHRow As Long, PALRow As Long, ATHRow As Long
Dim LastRow As Long, I As Long, J As Long
Dim shName As String
Dim SearchString As String, MatchString As String, init1String As String, init2String As String
Dim intNum As Long
Dim FirstAddress As String
On Error GoTo Whoa
Application.ScreenUpdating = True
Set wsPM = ActiveSheet
shName = wsPM.Name
'--> Make New "Output" Sheet if one already exists'
On Error Resume Next
Application.DisplayAlerts = False
Set wsopt = Sheets("Output")
intNum = 0
While Err.Number = 0
Err.Clear
intNum = intNum + 1
Set wsopt = Sheets("Output" & intNum)
Wend
Err.Clear
On Error GoTo 0
Application.DisplayAlerts = True
'--> Recreate "Output" Sheet and move it to the right'
Set wsopt = Worksheets.Add(after:=Worksheets(Worksheets.Count))
If intNum = 0 Then
wsopt.Name = "Output"
Else
wsopt.Name = "Output" & intNum
End If
'--> Find the "Account Trade History" cell in Sheet PM'
Set aCell = wsPM.Columns(1).Find(What:=ATH, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'--> If "Account Trade History" is found'
If Not aCell Is Nothing Then
'--> Get the starting row of "Account Trade History"'
ATHRow = aCell.Row
'--> Find the "Profits and Losses" cell in Sheet PM'
Set bCell = wsPM.Columns(1).Find(What:=PAL, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If shName = "rm" Then
'--> Find the "Equities" cell in Sheet RM'
Set bCell = wsPM.Columns(1).Find(What:=EQT, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
Else
'--> Find the "Options" cell in Sheet PM'
Set bCell = wsPM.Columns(1).Find(What:=OPT, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End If
'--> If "Profits and Losses" is found'
If Not bCell Is Nothing Then
'--> Get the starting row of "Profits and Losses"
PALRow = bCell.Row
'--> Output Trade History to new tab.'
wsPM.Rows((ATHRow + 1) & ":" & (PALRow - 1)).Copy wsopt.Rows(1)
If shName = "rm" Then
wsopt.Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
End If
With wsopt
'--> Remove last three columns because they are duplicates.'
.Columns("M:O").Delete Shift:=xlToLeft
'--> Define the notes column.'
.Range("A1").Value = "Notes"
.Columns("A:A").Replace What:="DEFAULT", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
'--> Get the last row of Output Sheet'
LastRow = .Cells.Find(What:="*", after:=[A1], SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'--> Unsure of what this section does?'
Set cCell = wsPM.Columns(1).Find(What:=AOH, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not cCell Is Nothing Then
AOHRow = cCell.Row + 1
'--> Copy the notes into the new notes column.
wsPM.Range("IV:IV").ClearContents
For I = AOHRow To ATHRow
SearchString = wsPM.Range("D" & I).Value & "###" & _
wsPM.Range("F" & I).Value & "###" & _
wsPM.Range("G" & I).Value & "###" & _
wsPM.Range("H" & I).Value & "###" & _
wsPM.Range("I" & I).Value
wsPM.Range("IV" & I).Value = SearchString
Next I
For J = 2 To LastRow
MatchString = .Range("D" & J).Value & "###" & _
.Range("F" & J).Value & "###" & _
.Range("G" & J).Value & "###" & _
.Range("H" & J).Value & "###" & _
.Range("I" & J).Value
Set cCell = wsPM.Range("IV:IV").Find(What:=MatchString, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not cCell Is Nothing Then
FirstAddress = cCell.Address
Do
If wsopt.Range("F" & J).Value = wsPM.Range("F" & cCell.Row).Value Then
.Range("A" & J).Value = wsPM.Range("A" & cCell.Row).Value
Exit Do
Else
Set cCell = wsPM.Range("IV:IV").FindNext(cCell)
End If
Loop While Not cCell Is Nothing And cCell.Address <> FirstAddress
End If
Next J
End If
GoTo There
'--> Copy the notes into the new notes column. old'
For I = AOHRow To ATHRow
SearchString = wsPM.Range("D" & I).Value & "###" & _
wsPM.Range("F" & I).Value & "###" & _
wsPM.Range("G" & I).Value & "###" & _
wsPM.Range("H" & I).Value & "###" & _
wsPM.Range("I" & I).Value
init1String = wsPM.Range("F" & I).Value
For J = 2 To LastRow
init2String = wsopt.Range("F" & J).Value
If init1String = init2String Then
MatchString = .Range("D" & J).Value & "###" & _
.Range("F" & J).Value & "###" & _
.Range("G" & J).Value & "###" & _
.Range("H" & J).Value & "###" & _
.Range("I" & J).Value
If MatchString = SearchString Then
.Range("A" & J).Value = wsPM.Range("A" & I).Value
End If
End If
Next J
Next I
'End If
There:
'--> Make the Output tab into a table that can be filtered by the column headers.'
.ListObjects.Add(xlSrcRange, Range("$A$1:$L$" & LastRow), , xlYes).Name = "Table1"
.ListObjects("Table1").ShowTableStyleRowStripes = False
'ListObjects("Table1").TableStyle = "TableStyleLight1"'
.Columns("B:L").EntireColumn.AutoFit
End With
End If
End If
Application.ScreenUpdating = False
NewUpdates
FixDate
Indexing
Sorting
AddMOColumn
AddDaysandPLColumn
Application.ScreenUpdating = True
'--> Clean Up and Exit.'
LetsContinue:
Application.ScreenUpdating = True
Set aCell = Nothing: Set bCell = Nothing: Set cCell = Nothing: Set delRange = Nothing
On Error Resume Next
Set wsopt = Nothing: Set wsPM = Nothing
On Error GoTo 0
Exit Sub
'--> Error Handling'
Whoa:
MsgBox Err.Description
Resume LetsContinue
End Sub
Private Sub FixDate()
'This Sub will go thru Exp Column Col J and will Fix the date to allow for either mm-yy or mmm-dd-yy.
Dim MaxRow As Long, I As Long
Dim tmpM As String, tmpD As String, tmpY As String
MaxRow = wsopt.Rows(wsopt.Rows.Count).End(xlUp).Row
For I = 12 To MaxRow
'---> Strip Dates and Fix them
If wsopt.Cells(I, "J") <> "" Then
If IsDate(wsopt.Cells(I, "J")) Then
'---> Format normally if normal Date format as 'mmm-dd-yy'
wsopt.Cells(I, "J").NumberFormat = "Mmm-yy"
Else
'---> Strip the date in Day month Year then re-group so system would recognize
' it as a date and then apply format 'mmm-yy'
tmpM = Left(wsopt.Cells(I, "J"), 3)
tmpY = Right(wsopt.Cells(I, "J"), 2)
tmpD = Mid(wsopt.Cells(I, "J"), 4, Len(wsopt.Cells(I, "J")) - 6)
wsopt.Cells(I, "J") = tmpM & " " & Format(Val(tmpD), "") & ", " & Format(Val(tmpY), "")
wsopt.Cells(I, "J").NumberFormat = "Mmm-dd-yy"
End If
End If
Next I
End Sub
Private Sub NewUpdates()
Dim I As Integer
'---> Create extra space at the top of the table of approximately 10 rows.
wsopt.Rows("1:10").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'---> Modify the date format of the Exec Time column to reflect the format of ddd mmm dd, yyyy hh:mm'
'and the Exp column to reflect the format of mm-yy.'
For I = 12 To wsopt.Cells(wsopt.Rows.Count, "K").End(xlUp).Row
wsopt.Range("B" & I).NumberFormat = "ddd mmm-dd-yyyy hh:mm"
wsopt.Range("G" & I).NumberFormat = "mmm-yy"
Next I
'--> Add Columns for Postion, Spread, and Leg Numbers'
'Add P# column before the Exec Time column with the comment "Position #" in the header for the P# column.'
'Add S# column between the Exec Time and Spread columns with the comment "Spread #" in the header for the S# column.'
'Add L# column between the Spread and Side columns with the comment "Leg #" in the header for the L# column.'
wsopt.Columns("B:B").Insert Shift:=xlToRight
wsopt.Columns("D:D").Insert Shift:=xlToRight
wsopt.Columns("F:F").Insert Shift:=xlToRight
'---> Format column D and E to number format
wsopt.Range("D12:D" & wsopt.Cells(wsopt.Rows.Count, "K").End(xlUp).Row).NumberFormat = "0"
wsopt.Range("F12:F" & wsopt.Cells(wsopt.Rows.Count, "K").End(xlUp).Row).NumberFormat = "0"
wsopt.Range("B12:B" & wsopt.Cells(wsopt.Rows.Count, "K").End(xlUp).Row).NumberFormat = "0"
'---> Rename the header of the added Position, Spread, and Leg Number columns'
wsopt.Range("B11") = "P#": wsopt.Range("D11") = "S#": wsopt.Range("F11") = "L#"
'---> Set autofit to the width of column D and E
wsopt.Columns("D:D").EntireColumn.AutoFit
wsopt.Columns("F:F").EntireColumn.AutoFit
wsopt.Columns("B:B").EntireColumn.AutoFit
'---> Add comments
wsopt.Range("B11").AddComment
wsopt.Range("B11").Comment.Visible = False
wsopt.Range("B11").Comment.Text Text:="Position No"
wsopt.Range("D11").AddComment
wsopt.Range("D11").Comment.Visible = False
wsopt.Range("D11").Comment.Text Text:="Spread No"
wsopt.Range("F11").AddComment
wsopt.Range("F11").Comment.Visible = False
wsopt.Range("F11").Comment.Text Text:="Leg No"
'---> Shrink the printout so all column will be printed in the same page
ActiveSheet.PageSetup.PrintArea = ""
wsopt.PageSetup.FitToPagesWide = 1
wsopt.PageSetup.FitToPagesTall = 1
End Sub
'DEFINITIONS - Definitions to understand prior to Indexing and Ordering'
'1. Positions - Positions can be made up of more than one Spread and the newest Positions appear at the top of the sheet.'
'2. Spreads - Spreads can be made up of more than one Leg and their order with a position can change.'
'3. Legs - Legs are made up of individual rows within a Spread and there order within the spread never changes.'
Private Sub Indexing()
'Each Position, Spread, and Leg is indexed with a number according to the value in its Exec Date column.'
'P# - Positions are numbered sequentially with 1 being meaning that it contains a newer Exec Date than any other positions.'
'S# - Spreads are numbered sequentially with 1 meaning that it contains the a newer Exec Date than the other spreads.'
'L# - Legs are numbered sequentially with 1 containing the oldest Exec Date within it's particular spread and starting over at the next spread.'
'If the Exec Date column (which is actually a date and time stamp) contains the same values, then the Symbol is used in alphabetical order.'
Dim I As Integer, k As Double, z As Double, J As Double, l As Double, test As Boolean, test1 As Boolean
Dim WS As Worksheet, q As Double, Rcount As Double, x As Double, SpreadNo As Double
Sheets.Add after:=Sheets(Sheets.Count)
Set WS = Sheets(Sheets.Count)
k = 0: z = -1: Rcount = wsopt.Cells(wsopt.Rows.Count, "K").End(xlUp).Row: SpreadNo = 0
For I = 12 To Rcount
If k = 0 And z = -1 Then
k = k + 1: z = z + 2:
WS.Cells(k, z) = wsopt.Range("I" & I) & wsopt.Range("J" & I) & wsopt.Range("L" & I) & " " & wsopt.Range("K" & I)
For x = I + 1 To Rcount
If wsopt.Range("C" & x) = "" Then
If InStr(1, WS.Cells(k, z), wsopt.Range("K" & x)) = 0 Then WS.Cells(k, z) = WS.Cells(k, z) & " " & wsopt.Range("K" & x)
Else
Exit For
End If
Next x
WS.Cells(k + 1, z) = wsopt.Range("C" & I) & wsopt.Range("E" & I)
WS.Cells(k + 1, z + 1) = WS.Cells(k, z + 1) + 1
wsopt.Range("B" & I) = 1: wsopt.Range("F" & I) = 1
Else
If J = 0 Then J = 1
If wsopt.Range("C" & I) = "" Then
test = True
Else
test = False: q = -1
For J = 1 To z Step 2
q = q + 1
If InStr(1, WS.Cells(1, J), wsopt.Range("I" & I) & wsopt.Range("J" & I) & wsopt.Range("L" & I)) > 0 Then
If InStr(1, WS.Cells(1, J), wsopt.Range("K" & I)) > 0 Then
wsopt.Range("B" & I) = J - q: test = True
For x = I + 1 To Rcount
If wsopt.Range("C" & x) = "" Then
If InStr(1, WS.Cells(k, J), wsopt.Range("K" & x)) = 0 Then WS.Cells(k, J) = WS.Cells(k, J) & " " & wsopt.Range("K" & x)
Else
Exit For
End If
Next x
Else
For x = I + 1 To Rcount
If wsopt.Range("C" & x) = "" Then
If InStr(1, WS.Cells(k, J), wsopt.Range("K" & x)) > 0 Then wsopt.Range("B" & I) = J - q: test = True
Else
Exit For
End If
Next x
If test = True Then
WS.Cells(k, J) = WS.Cells(k, J) & " " & wsopt.Range("K" & I)
For x = I + 1 To Rcount
If wsopt.Range("C" & x) = "" Then
If InStr(1, WS.Cells(k, J), wsopt.Range("K" & x)) = 0 Then WS.Cells(k, J) = WS.Cells(k, z) & " " & wsopt.Range("K" & x)
Else
Exit For
End If
Next x
End If
End If
If test = True Then Exit For
End If
Next J
End If
If test = True Then
l = WS.Cells(WS.Rows.Count, J).End(xlUp).Row + 1
If wsopt.Range("C" & I) = "" Then
If J = 0 Then wsopt.Range("B" & I) = 1 Else wsopt.Range("B" & I) = J - q
WS.Cells(l - 1, J + 1) = WS.Cells(l - 1, J + 1) + 1
wsopt.Range("F" & I) = WS.Cells(l - 1, J + 1)
Else
WS.Cells(l, J) = wsopt.Range("C" & I) & wsopt.Range("E" & I)
WS.Cells(l, J + 1) = WS.Cells(l, J + 1) + 1
wsopt.Range("F" & I) = 1
End If
Else
k = 1: z = z + 2: q = q + 1
WS.Cells(k, z) = wsopt.Range("I" & I) & wsopt.Range("J" & I) & wsopt.Range("L" & I) & " " & wsopt.Range("K" & I)
For x = I + 1 To Rcount
If wsopt.Range("C" & x) = "" Then
If InStr(1, WS.Cells(k, z), wsopt.Range("K" & x)) = 0 Then WS.Cells(k, z) = WS.Cells(k, z) & " " & wsopt.Range("K" & x)
Else
Exit For
End If
Next x
WS.Cells(k + 1, z) = wsopt.Range("C" & I) & wsopt.Range("E" & I)
WS.Cells(k + 1, z + 1) = WS.Cells(k, z + 1) + 1
wsopt.Range("B" & I) = z - q: wsopt.Range("F" & I) = 1
End If
End If
Next I
Application.DisplayAlerts = False
WS.Delete
Application.DisplayAlerts = True
'---> The empty cells in the Exec Time and Spread columns should take on the information'
'of the preceding cell because they are part of the same Spread.'
For I = 12 To Rcount
If wsopt.Range("E" & I) = "" And I > 12 Then
wsopt.Range("E" & I) = wsopt.Range("E" & I - 1)
wsopt.Range("C" & I) = wsopt.Range("C" & I - 1)
wsopt.Range("C" & I & ":E" & I).Font.Color = -5395027
End If
Next I
wsopt.Sort.SortFields.Add Key:=Range("C11"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A12:O" & Rcount)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
For I = 12 To Rcount
If wsopt.Range("F" & I) = 1 Then
SpreadNo = SpreadNo + 1: wsopt.Range("D" & I) = SpreadNo
Else
wsopt.Range("D" & I) = SpreadNo
End If
Next I
End Sub
Private Sub Sorting()
'Positions can contain single or multi-legged spreads but the sort order of the legs 'within' each'
'spread never changes. This is an important point because by contrast, the sort order in which each
'spread appears within the overall position does indeed change. Here the oldest spreads should'
'appear at the top of the position while newer spreads should appear at the bottom of the position.'
Dim I As Double, Brow As Double, Erow As Double, Prow As Double
Dim Rcount As Double
'On Error Resume Next
Rcount = wsopt.Cells(wsopt.Rows.Count, "B").End(xlUp).Row
'---> Sorting the output by the Position No.
wsopt.Sort.SortFields.Clear
wsopt.Sort.SortFields.Add Key:=Range("B11"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With wsopt.Sort
.SetRange Range("A11:O" & Rcount)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'--> Skipping a row between each major position
For I = Rcount To 13 Step -1
If wsopt.Range("B" & I) <> wsopt.Range("B" & I - 1) And wsopt.Range("B" & I) <> "" Then
wsopt.Rows(I & Chr(58) & I).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'--> Added by gowflow to keep correct counting of last row
Rcount = Rcount + 1
End If
Next I
'--> Sorting the output by Date descending
Brow = 0
'--> Changed by gowflow from upper Rcount to Rcount +1
wsopt.Sort.SortFields.Add Key:=wsopt.Range("C11"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
For I = 12 To Rcount
If wsopt.Range("B" & I) <> "" Then
If Brow = 0 Then
Brow = I: Erow = I
Else
Erow = I
End If
Else
With wsopt.Sort
.SetRange Range("A" & Brow & ":O" & Erow)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Brow = 0
wsopt.Range("A" & I - 1 & ":O" & I - 1).Copy Destination:=wsopt.Range("A" & I & ":O" & I)
wsopt.Range("A" & I & ":O" & I).Font.ThemeColor = xlThemeColorDark1
End If
Next I
wsopt.Range("P" & 12) = wsopt.Range("D" & 12): Prow = wsopt.Range("D" & 12)
For I = 13 To Rcount
If wsopt.Range("B" & I) = wsopt.Range("B" & I - 1) Then
wsopt.Range("P" & I) = Prow
Else
Prow = wsopt.Range("D" & I)
wsopt.Range("P" & I) = Prow
End If
Next I
wsopt.Range("B" & 12) = 1: Prow = 1
For I = 13 To Rcount
If wsopt.Range("P" & I) = wsopt.Range("P" & I - 1) Then
wsopt.Range("B" & I) = Prow
If wsopt.Range("P" & I) = wsopt.Range("P" & I + 1) Then wsopt.Range("B" & I).Font.Color = -5395027
Else
Prow = Prow + 1
wsopt.Range("B" & I) = Prow
End If
Next I
wsopt.Columns("P:P").Delete Shift:=xlToLeft
End Sub
ASKER
Yes. Thank you sir!
re your post
Below is an example of one broken out that can be found in "line 824 of rm full few spreads some notes" (i.e. BUY +1 VERTICAL SPY 100 NOV 11 121/118 PUT) and refer to the following columns in Trade History as follows.
>>>
1) Can you please tell me to which line this note corespond in the output file ? and where you want to see it ?
2) in line 824 the note is in column A but also there is a description in Col C I was under the impression that we need to match the description in Col C to the data in output so what is it ? I am a bit confused.
gowflow
Below is an example of one broken out that can be found in "line 824 of rm full few spreads some notes" (i.e. BUY +1 VERTICAL SPY 100 NOV 11 121/118 PUT) and refer to the following columns in Trade History as follows.
>>>
1) Can you please tell me to which line this note corespond in the output file ? and where you want to see it ?
2) in line 824 the note is in column A but also there is a description in Col C I was under the impression that we need to match the description in Col C to the data in output so what is it ? I am a bit confused.
gowflow
ASKER
Below is an example of one broken out that can be found in "line 824 of rm full few spreads some notes" (i.e. BUY +1 VERTICAL SPY 100 NOV 11 121/118 PUT) and refer to the following columns in Trade History as follows.
Side (BUY)
Qty (+1)
Spread (VERTICAL)
Symbol (SPY)
Not Referenced (100 is not referred to in Trade History)
Exp (NOV 11)
Strike (121/118 would could just as easily be 121/118/110 or just 121 or more or less)
Type (PUT which could just as easily be PUT/CALL/PUT, or more or less, or even Stock, or Future, or other for that item)
In some cases data in the description for the Exp and Strike columns may not exist at all.
To simplify, the following statement 'might' be enough to accomplish the match.
STATEMENT - "All words in each line (like the line 824 mentioned in the above example) would need to match with an entire Spread (which can be multiple rows). This might work as there is never more than one note per spread."
Side (BUY)
Qty (+1)
Spread (VERTICAL)
Symbol (SPY)
Not Referenced (100 is not referred to in Trade History)
Exp (NOV 11)
Strike (121/118 would could just as easily be 121/118/110 or just 121 or more or less)
Type (PUT which could just as easily be PUT/CALL/PUT, or more or less, or even Stock, or Future, or other for that item)
In some cases data in the description for the Exp and Strike columns may not exist at all.
To simplify, the following statement 'might' be enough to accomplish the match.
STATEMENT - "All words in each line (like the line 824 mentioned in the above example) would need to match with an entire Spread (which can be multiple rows). This might work as there is never more than one note per spread."
sorry but you haven't answered my question
clearly your line 824 would show in the output sheet in line 12 and line 15 as these 2 lines have a SPY/BUY
pls confirm
gowflow
clearly your line 824 would show in the output sheet in line 12 and line 15 as these 2 lines have a SPY/BUY
pls confirm
gowflow
Well here it is.
Pls chk this version and pls be patient when you run the first sheet
rm full many spread no notes
as it will take easy depending on your pc speed some 3 to 5 min to process the entire sheet.
Chek the notes if this is what you were looking for. Still it is not 100% as there are some cases that need to be finetuned I tried as much as possible to find a common ground to dissec the notes.
Let me know your comments.
gowflow
Pls chk this version and pls be patient when you run the first sheet
rm full many spread no notes
as it will take easy depending on your pc speed some 3 to 5 min to process the entire sheet.
Chek the notes if this is what you were looking for. Still it is not 100% as there are some cases that need to be finetuned I tried as much as possible to find a common ground to dissec the notes.
Let me know your comments.
gowflow
Option Explicit
'--> This macro takes either pm or rm tabs and creates a properly formatted table in a new tab'
'called Output with the data from the Trade History section of the original tab. It also takes'
'the notes from the Order History section of the original tab and then indexes and sorts each'
'Spread into meaningful positions.'
'Future development will include hiding the Leg and Spread numbers, as well as making some
'calculations on each overall position.'
Const AOH = "Account Order History"
Const ATH = "Account Trade History"
Const PAL = "Profits and Losses"
Const EQT = "Equities"
Const OPT = "Options"
Const OTH = "Others"
Const FRX = "Forex"
Dim wsopt As Worksheet
Private Sub AddMOColumn()
'This Sub will Add A new Column before Exp Column J that should indicate either of the following
'M for Monthly when the date in Exp is formated as Mmm-yy the date should be the 3rd friday of the month
'O for Weekly or Quarterly options
Dim MaxRow As Long, I As Long
Dim dDate As Date
Dim ThirdThuOfTheMonth As Date
Dim a
MaxRow = wsopt.Rows(wsopt.Rows.Count).End(xlUp).Row
wsopt.Select
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
wsopt.Range("J11") = "OT"
For I = 12 To MaxRow
If wsopt.Cells(I, "K") <> "" Then
If wsopt.Cells(I, "K").NumberFormat = "mmm-dd-yy" Then
wsopt.Cells(I, "J") = "O"
Else
wsopt.Cells(I, "J") = "M"
dDate = wsopt.Cells(I, "K")
dDate = DateSerial(Year(dDate), Month(dDate), 1)
Select Case Weekday(dDate)
Case 5 'If Date is a Thursday then add 14 days
ThirdThuOfTheMonth = DateValue(dDate + 14)
Case Is > 5 'If Date is Friday or saturday then add 21+1 days
ThirdThuOfTheMonth = DateValue(dDate + 21 - (Weekday(dDate) - vbThursday))
Case Is < 5 'If Date is Sunday to Thurthday then add 14+ diffrence to friday days
ThirdThuOfTheMonth = DateValue(dDate + 14 + vbThursday - Weekday(dDate))
End Select
If Weekday(ThirdThuOfTheMonth) <> vbThursday Then
MsgBox ("this date: " & ThirdThuOfTheMonth & " does not corespond to a Thursday! on row " & I & " Please check that date in Col K is actually a valid date then re-run the whole macro.")
End If
wsopt.Cells(I, "K") = ThirdThuOfTheMonth
End If
End If
Next I
End Sub
Private Sub AddDaysandPLColumn()
'This Sub will Add 3 new Columns
'Days to Exp in Col N next to type Column which should be the diffrence in days between Exec Time and Exp
'P/L Balance Col R
'P/L% Col S
Dim MaxRow As Long, I As Long
Dim dDate As Date
Dim ThirdThuOfTheMonth As Date
Dim a
MaxRow = wsopt.Rows(wsopt.Rows.Count).End(xlUp).Row
wsopt.Select
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
wsopt.Range("N11") = "Days to Exp"
Columns("R:S").Select
Selection.Insert Shift:=xlToRight
wsopt.Range("R11") = "P/L Balance"
wsopt.Range("S11") = "P/L %"
For I = 12 To MaxRow
If wsopt.Cells(I, "K") <> "" Then
wsopt.Cells(I, "N") = DateValue(wsopt.Cells(I, "C")) - DateValue(wsopt.Cells(I, "K"))
End If
Next I
End Sub
Sub TradeReport()
Dim wsPM As Worksheet
Dim aCell As Range, bCell As Range, cCell As Range, delRange As Range
Dim AOHRow As Long, PALRow As Long, ATHRow As Long
Dim LastRow As Long, I As Long, J As Long
Dim shName As String
Dim SearchString As String, MatchString As String, init1String As String, init2String As String
Dim intNum As Long
Dim FirstAddress As String
On Error GoTo Whoa
Application.ScreenUpdating = True
Set wsPM = ActiveSheet
shName = wsPM.Name
'--> Make New "Output" Sheet if one already exists'
On Error Resume Next
Application.DisplayAlerts = False
Set wsopt = Sheets("Output")
intNum = 0
While Err.Number = 0
Err.Clear
intNum = intNum + 1
Set wsopt = Sheets("Output" & intNum)
Wend
Err.Clear
On Error GoTo 0
Application.DisplayAlerts = True
'--> Recreate "Output" Sheet and move it to the right'
Set wsopt = Worksheets.Add(after:=Worksheets(Worksheets.Count))
If intNum = 0 Then
wsopt.Name = "Output"
Else
wsopt.Name = "Output" & intNum
End If
'--> Find the "Account Trade History" cell in Sheet PM'
Set aCell = wsPM.Columns(1).Find(What:=ATH, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'--> If "Account Trade History" is found'
If Not aCell Is Nothing Then
'--> Get the starting row of "Account Trade History"'
ATHRow = aCell.Row
If LCase(Left(shName, 2)) = "pm" Then
'--> Find the "Options" cell in Sheet PM'
Set bCell = wsPM.Columns(1).Find(What:=OPT, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If bCell Is Nothing Then
'--> Find the "Forex" cell in Sheet PM if Options is not found'
Set bCell = wsPM.Columns(1).Find(What:=FRX, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End If
If bCell Is Nothing Then
'--> Find the "Profits and Losses" cell in Sheet PM if Forex is not found'
Set bCell = wsPM.Columns(1).Find(What:=PAL, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End If
End If
If LCase(Left(shName, 2)) = "rm" Then
'--> Find the "Equities" cell in Sheet RM'
Set bCell = wsPM.Columns(1).Find(What:=EQT, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If bCell Is Nothing Then
'--> Find the "Others" cell in Sheet RM if Equities is not found
Set bCell = wsPM.Columns(1).Find(What:=OTH, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End If
End If
'--> If "Profits and Losses" is found'
If Not bCell Is Nothing Then
'--> Get the starting row of "Profits and Losses"
PALRow = bCell.Row
'--> Output Trade History to new tab.'
wsPM.Rows((ATHRow + 1) & ":" & (PALRow - 1)).Copy wsopt.Rows(1)
If LCase(Left(shName, 2)) = "rm" Then
wsopt.Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
End If
With wsopt
'--> Remove last three columns because they are duplicates.'
.Columns("M:O").Delete Shift:=xlToLeft
'--> Define the notes column.'
.Range("A1").Value = "Notes"
.Columns("A:A").Replace What:="DEFAULT", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
'--> Get the last row of Output Sheet'
LastRow = .Cells.Find(What:="*", after:=[A1], SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'--> Unsure of what this section does?'
Set cCell = wsPM.Columns(1).Find(What:=AOH, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not cCell Is Nothing Then
AOHRow = cCell.Row + 1
'--> Copy the notes into the new notes column.
wsPM.Range("IV:IV").ClearContents
For I = AOHRow To ATHRow
SearchString = wsPM.Range("D" & I).Value & "###" & _
wsPM.Range("F" & I).Value & "###" & _
wsPM.Range("G" & I).Value & "###" & _
wsPM.Range("H" & I).Value & "###" & _
wsPM.Range("I" & I).Value
wsPM.Range("IV" & I).Value = SearchString
Next I
For J = 2 To LastRow
MatchString = .Range("D" & J).Value & "###" & _
.Range("F" & J).Value & "###" & _
.Range("G" & J).Value & "###" & _
.Range("H" & J).Value & "###" & _
.Range("I" & J).Value
Set cCell = wsPM.Range("IV:IV").Find(What:=MatchString, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not cCell Is Nothing Then
FirstAddress = cCell.Address
Do
If wsopt.Range("F" & J).Value = wsPM.Range("F" & cCell.Row).Value Then
.Range("A" & J).Value = wsPM.Range("A" & cCell.Row).Value
Exit Do
Else
Set cCell = wsPM.Range("IV:IV").FindNext(cCell)
End If
Loop While Not cCell Is Nothing And cCell.Address <> FirstAddress
End If
Next J
End If
GoTo There
'--> Copy the notes into the new notes column. old'
For I = AOHRow To ATHRow
SearchString = wsPM.Range("D" & I).Value & "###" & _
wsPM.Range("F" & I).Value & "###" & _
wsPM.Range("G" & I).Value & "###" & _
wsPM.Range("H" & I).Value & "###" & _
wsPM.Range("I" & I).Value
init1String = wsPM.Range("F" & I).Value
For J = 2 To LastRow
init2String = wsopt.Range("F" & J).Value
If init1String = init2String Then
MatchString = .Range("D" & J).Value & "###" & _
.Range("F" & J).Value & "###" & _
.Range("G" & J).Value & "###" & _
.Range("H" & J).Value & "###" & _
.Range("I" & J).Value
If MatchString = SearchString Then
.Range("A" & J).Value = wsPM.Range("A" & I).Value
End If
End If
Next J
Next I
'End If
There:
'--> Make the Output tab into a table that can be filtered by the column headers.'
.ListObjects.Add(xlSrcRange, Range("$A$1:$L$" & LastRow), , xlYes).Name = "Table1"
.ListObjects("Table1").ShowTableStyleRowStripes = False
'ListObjects("Table1").TableStyle = "TableStyleLight1"'
.Columns("B:L").EntireColumn.AutoFit
End With
End If
End If
Application.ScreenUpdating = False
NewUpdates
FixDate
Indexing
Sorting
AddMOColumn
AddDaysandPLColumn
If LCase(Left(shName, 2)) = "rm" Then
UpdateNotes wsPM, AOHRow, ATHRow
End If
wsopt.Cells(1, 1).Select
Application.ScreenUpdating = True
'--> Clean Up and Exit.'
LetsContinue:
Application.ScreenUpdating = True
Set aCell = Nothing: Set bCell = Nothing: Set cCell = Nothing: Set delRange = Nothing
On Error Resume Next
Set wsopt = Nothing: Set wsPM = Nothing
On Error GoTo 0
Exit Sub
'--> Error Handling'
Whoa:
MsgBox Err.Description
Resume LetsContinue
End Sub
Private Sub FixDate()
'This Sub will go thru Exp Column Col J and will Fix the date to allow for either mm-yy or mmm-dd-yy.
Dim MaxRow As Long, I As Long
Dim tmpM As String, tmpD As String, tmpY As String
MaxRow = wsopt.Rows(wsopt.Rows.Count).End(xlUp).Row
For I = 12 To MaxRow
'---> Strip Dates and Fix them
If wsopt.Cells(I, "J") <> "" Then
If IsDate(wsopt.Cells(I, "J")) Then
'---> Format normally if normal Date format as 'mmm-dd-yy'
wsopt.Cells(I, "J").NumberFormat = "Mmm-yy"
Else
'---> Strip the date in Day month Year then re-group so system would recognize
' it as a date and then apply format 'mmm-yy'
tmpM = Left(wsopt.Cells(I, "J"), 3)
tmpY = Right(wsopt.Cells(I, "J"), 2)
tmpD = Mid(wsopt.Cells(I, "J"), 4, Len(wsopt.Cells(I, "J")) - 6)
wsopt.Cells(I, "J") = tmpM & " " & Format(Val(tmpD), "") & ", " & Format(Val(tmpY), "")
wsopt.Cells(I, "J").NumberFormat = "Mmm-dd-yy"
End If
End If
Next I
End Sub
Private Sub NewUpdates()
Dim I As Integer
'---> Create extra space at the top of the table of approximately 10 rows.
wsopt.Rows("1:10").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'---> Modify the date format of the Exec Time column to reflect the format of ddd mmm dd, yyyy hh:mm'
'and the Exp column to reflect the format of mm-yy.'
For I = 12 To wsopt.Cells(wsopt.Rows.Count, "K").End(xlUp).Row
wsopt.Range("B" & I).NumberFormat = "ddd mmm-dd-yyyy hh:mm"
wsopt.Range("G" & I).NumberFormat = "mmm-yy"
Next I
'--> Add Columns for Postion, Spread, and Leg Numbers'
'Add P# column before the Exec Time column with the comment "Position #" in the header for the P# column.'
'Add S# column between the Exec Time and Spread columns with the comment "Spread #" in the header for the S# column.'
'Add L# column between the Spread and Side columns with the comment "Leg #" in the header for the L# column.'
wsopt.Columns("B:B").Insert Shift:=xlToRight
wsopt.Columns("D:D").Insert Shift:=xlToRight
wsopt.Columns("F:F").Insert Shift:=xlToRight
'---> Format column D and E to number format
wsopt.Range("D12:D" & wsopt.Cells(wsopt.Rows.Count, "K").End(xlUp).Row).NumberFormat = "0"
wsopt.Range("F12:F" & wsopt.Cells(wsopt.Rows.Count, "K").End(xlUp).Row).NumberFormat = "0"
wsopt.Range("B12:B" & wsopt.Cells(wsopt.Rows.Count, "K").End(xlUp).Row).NumberFormat = "0"
'---> Rename the header of the added Position, Spread, and Leg Number columns'
wsopt.Range("B11") = "P#": wsopt.Range("D11") = "S#": wsopt.Range("F11") = "L#"
'---> Set autofit to the width of column D and E
wsopt.Columns("D:D").EntireColumn.AutoFit
wsopt.Columns("F:F").EntireColumn.AutoFit
wsopt.Columns("B:B").EntireColumn.AutoFit
'---> Add comments
wsopt.Range("B11").AddComment
wsopt.Range("B11").Comment.Visible = False
wsopt.Range("B11").Comment.Text Text:="Position No"
wsopt.Range("D11").AddComment
wsopt.Range("D11").Comment.Visible = False
wsopt.Range("D11").Comment.Text Text:="Spread No"
wsopt.Range("F11").AddComment
wsopt.Range("F11").Comment.Visible = False
wsopt.Range("F11").Comment.Text Text:="Leg No"
'---> Shrink the printout so all column will be printed in the same page
ActiveSheet.PageSetup.PrintArea = ""
wsopt.PageSetup.FitToPagesWide = 1
wsopt.PageSetup.FitToPagesTall = 1
End Sub
'DEFINITIONS - Definitions to understand prior to Indexing and Ordering'
'1. Positions - Positions can be made up of more than one Spread and the newest Positions appear at the top of the sheet.'
'2. Spreads - Spreads can be made up of more than one Leg and their order with a position can change.'
'3. Legs - Legs are made up of individual rows within a Spread and there order within the spread never changes.'
Private Sub Indexing()
'Each Position, Spread, and Leg is indexed with a number according to the value in its Exec Date column.'
'P# - Positions are numbered sequentially with 1 being meaning that it contains a newer Exec Date than any other positions.'
'S# - Spreads are numbered sequentially with 1 meaning that it contains the a newer Exec Date than the other spreads.'
'L# - Legs are numbered sequentially with 1 containing the oldest Exec Date within it's particular spread and starting over at the next spread.'
'If the Exec Date column (which is actually a date and time stamp) contains the same values, then the Symbol is used in alphabetical order.'
Dim I As Integer, k As Double, z As Double, J As Double, l As Double, test As Boolean, test1 As Boolean
Dim WS As Worksheet, q As Double, Rcount As Double, x As Double, SpreadNo As Double
Sheets.Add after:=Sheets(Sheets.Count)
Set WS = Sheets(Sheets.Count)
k = 0: z = -1: Rcount = wsopt.Cells(wsopt.Rows.Count, "K").End(xlUp).Row: SpreadNo = 0
For I = 12 To Rcount
If k = 0 And z = -1 Then
k = k + 1: z = z + 2:
WS.Cells(k, z) = wsopt.Range("I" & I) & wsopt.Range("J" & I) & wsopt.Range("L" & I) & " " & wsopt.Range("K" & I)
For x = I + 1 To Rcount
If wsopt.Range("C" & x) = "" Then
If InStr(1, WS.Cells(k, z), wsopt.Range("K" & x)) = 0 Then WS.Cells(k, z) = WS.Cells(k, z) & " " & wsopt.Range("K" & x)
Else
Exit For
End If
Next x
WS.Cells(k + 1, z) = wsopt.Range("C" & I) & wsopt.Range("E" & I)
WS.Cells(k + 1, z + 1) = WS.Cells(k, z + 1) + 1
wsopt.Range("B" & I) = 1: wsopt.Range("F" & I) = 1
Else
If J = 0 Then J = 1
If wsopt.Range("C" & I) = "" Then
test = True
Else
test = False: q = -1
For J = 1 To z Step 2
q = q + 1
If InStr(1, WS.Cells(1, J), wsopt.Range("I" & I) & wsopt.Range("J" & I) & wsopt.Range("L" & I)) > 0 Then
If InStr(1, WS.Cells(1, J), wsopt.Range("K" & I)) > 0 Then
wsopt.Range("B" & I) = J - q: test = True
For x = I + 1 To Rcount
If wsopt.Range("C" & x) = "" Then
If InStr(1, WS.Cells(k, J), wsopt.Range("K" & x)) = 0 Then WS.Cells(k, J) = WS.Cells(k, J) & " " & wsopt.Range("K" & x)
Else
Exit For
End If
Next x
Else
For x = I + 1 To Rcount
If wsopt.Range("C" & x) = "" Then
If InStr(1, WS.Cells(k, J), wsopt.Range("K" & x)) > 0 Then wsopt.Range("B" & I) = J - q: test = True
Else
Exit For
End If
Next x
If test = True Then
WS.Cells(k, J) = WS.Cells(k, J) & " " & wsopt.Range("K" & I)
For x = I + 1 To Rcount
If wsopt.Range("C" & x) = "" Then
If InStr(1, WS.Cells(k, J), wsopt.Range("K" & x)) = 0 Then WS.Cells(k, J) = WS.Cells(k, z) & " " & wsopt.Range("K" & x)
Else
Exit For
End If
Next x
End If
End If
If test = True Then Exit For
End If
Next J
End If
If test = True Then
l = WS.Cells(WS.Rows.Count, J).End(xlUp).Row + 1
If wsopt.Range("C" & I) = "" Then
If J = 0 Then wsopt.Range("B" & I) = 1 Else wsopt.Range("B" & I) = J - q
WS.Cells(l - 1, J + 1) = WS.Cells(l - 1, J + 1) + 1
wsopt.Range("F" & I) = WS.Cells(l - 1, J + 1)
Else
WS.Cells(l, J) = wsopt.Range("C" & I) & wsopt.Range("E" & I)
WS.Cells(l, J + 1) = WS.Cells(l, J + 1) + 1
wsopt.Range("F" & I) = 1
End If
Else
k = 1: z = z + 2: q = q + 1
WS.Cells(k, z) = wsopt.Range("I" & I) & wsopt.Range("J" & I) & wsopt.Range("L" & I) & " " & wsopt.Range("K" & I)
For x = I + 1 To Rcount
If wsopt.Range("C" & x) = "" Then
If InStr(1, WS.Cells(k, z), wsopt.Range("K" & x)) = 0 Then WS.Cells(k, z) = WS.Cells(k, z) & " " & wsopt.Range("K" & x)
Else
Exit For
End If
Next x
WS.Cells(k + 1, z) = wsopt.Range("C" & I) & wsopt.Range("E" & I)
WS.Cells(k + 1, z + 1) = WS.Cells(k, z + 1) + 1
wsopt.Range("B" & I) = z - q: wsopt.Range("F" & I) = 1
End If
End If
Next I
Application.DisplayAlerts = False
WS.Delete
Application.DisplayAlerts = True
'---> The empty cells in the Exec Time and Spread columns should take on the information'
'of the preceding cell because they are part of the same Spread.'
For I = 12 To Rcount
If wsopt.Range("E" & I) = "" And I > 12 Then
wsopt.Range("E" & I) = wsopt.Range("E" & I - 1)
wsopt.Range("C" & I) = wsopt.Range("C" & I - 1)
wsopt.Range("C" & I & ":E" & I).Font.Color = -5395027
End If
Next I
wsopt.Sort.SortFields.Add Key:=Range("C11"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A12:O" & Rcount)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
For I = 12 To Rcount
If wsopt.Range("F" & I) = 1 Then
SpreadNo = SpreadNo + 1: wsopt.Range("D" & I) = SpreadNo
Else
wsopt.Range("D" & I) = SpreadNo
End If
Next I
End Sub
Private Sub Sorting()
'Positions can contain single or multi-legged spreads but the sort order of the legs 'within' each'
'spread never changes. This is an important point because by contrast, the sort order in which each
'spread appears within the overall position does indeed change. Here the oldest spreads should'
'appear at the top of the position while newer spreads should appear at the bottom of the position.'
Dim I As Double, Brow As Double, Erow As Double, Prow As Double
Dim Rcount As Double
'On Error Resume Next
Rcount = wsopt.Cells(wsopt.Rows.Count, "B").End(xlUp).Row
'---> Sorting the output by the Position No.
wsopt.Sort.SortFields.Clear
wsopt.Sort.SortFields.Add Key:=Range("B11"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With wsopt.Sort
.SetRange Range("A11:O" & Rcount)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'--> Skipping a row between each major position
For I = Rcount To 13 Step -1
If wsopt.Range("B" & I) <> wsopt.Range("B" & I - 1) And wsopt.Range("B" & I) <> "" Then
wsopt.Rows(I & Chr(58) & I).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'--> Added by gowflow to keep correct counting of last row
Rcount = Rcount + 1
End If
Next I
'--> Sorting the output by Date descending
Brow = 0
'--> Changed by gowflow from upper Rcount to Rcount +1
wsopt.Sort.SortFields.Add Key:=wsopt.Range("C11"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
For I = 12 To Rcount
If wsopt.Range("B" & I) <> "" Then
If Brow = 0 Then
Brow = I: Erow = I
Else
Erow = I
End If
Else
With wsopt.Sort
.SetRange Range("A" & Brow & ":O" & Erow)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Brow = 0
wsopt.Range("A" & I - 1 & ":O" & I - 1).Copy Destination:=wsopt.Range("A" & I & ":O" & I)
wsopt.Range("A" & I & ":O" & I).Font.ThemeColor = xlThemeColorDark1
End If
Next I
wsopt.Range("P" & 12) = wsopt.Range("D" & 12): Prow = wsopt.Range("D" & 12)
For I = 13 To Rcount
If wsopt.Range("B" & I) = wsopt.Range("B" & I - 1) Then
wsopt.Range("P" & I) = Prow
Else
Prow = wsopt.Range("D" & I)
wsopt.Range("P" & I) = Prow
End If
Next I
wsopt.Range("B" & 12) = 1: Prow = 1
For I = 13 To Rcount
If wsopt.Range("P" & I) = wsopt.Range("P" & I - 1) Then
wsopt.Range("B" & I) = Prow
If wsopt.Range("P" & I) = wsopt.Range("P" & I + 1) Then wsopt.Range("B" & I).Font.Color = -5395027
Else
Prow = Prow + 1
wsopt.Range("B" & I) = Prow
End If
Next I
wsopt.Columns("P:P").Delete Shift:=xlToLeft
End Sub
Sub UpdateNotes(WS As Worksheet, FMRow As Long, TORow As Long)
'This Sub will update noted for rm sheets from the Account Order History section in Col A
'To match with data in the Output file created under wsopt variable as follows:
'BUY +1 VERTICAL SPY 100 NOV 11 121/118 PUT) and refer to the following columns in Trade History as follows.
'0 1 2 3 4 5 6 7 8
'
'or
'SELL -1 NKE 100 APR 11 80 PUT @3.50 LMT AMEX [TO CLOSE]
'0 1 2 3 4 5 6 7
'Side Col G - (BUY)
'Qty Col H - (1)
'Spread Col E - (VERTICAL)
'Symbol Col I - (SPY)
'Not Referenced (100 is not referred to in Trade History)
'Exp Col K - (NOV 11)
'Strike Col L - (121/118 would could just as easily be 121/118/110 or just 121 or more or less)
'Type Col M - (PUT which could just as easily be PUT/CALL/PUT or more or less)
'
'We will concentrate on matching following items only:
'Incase in position 2 the value is not VERTICAL then all items except 0 should be -1
'Col G on position 0
'Col E on position 2
'Col I on position 3
'Col M on position 8
'as these are the most stable and clear values that can be matched.
Dim MaxRow As Long, I As Long, J As Long
Dim Note As String
Dim NoteItem
MaxRow = wsopt.Rows(wsopt.Rows.Count).End(xlUp).Row
'---> Loop thru all the notes in the Account Order History section
For I = FMRow + 1 To TORow
'And InStr(1, UCase(WS.Cells(I, "C")), "REPLACING") = 0
If WS.Cells(I, "C") <> "" And WS.Cells(I, "D") = "FILLED" Then
Note = WS.Cells(I, "C")
NoteItem = Split(Note, " ")
If UBound(NoteItem) >= 7 Then
wsopt.UsedRange.AutoFilter 7, NoteItem(0)
'---> Depending on value of the 3rd item the sequence differs.
If NoteItem(2) <> "VERTICAL" Then
wsopt.UsedRange.AutoFilter 9, NoteItem(2)
wsopt.UsedRange.AutoFilter 13, NoteItem(7)
Else
wsopt.UsedRange.AutoFilter 5, NoteItem(2)
wsopt.UsedRange.AutoFilter 9, NoteItem(3)
wsopt.UsedRange.AutoFilter 13, NoteItem(8)
End If
For J = 12 To MaxRow
If wsopt.Cells(J, "A").EntireRow.Hidden = False And wsopt.Cells(J, "C") <> "" Then
wsopt.Cells(J, "A") = Note
End If
Next J
'---> Clear All Filters
wsopt.UsedRange.AutoFilter 5
wsopt.UsedRange.AutoFilter 7
wsopt.UsedRange.AutoFilter 9
wsopt.UsedRange.AutoFilter 13
End If
End If
Next I
End Sub
ASKER
Gowflow,
Thank you sir! This is quite a complicated problem to solve.
Here is a video of further analysis and simplification http://screencast.com/t/oyERW6EgHSq. I wrote the notation below after creating the video.
To recap: Matching rules for Notes found in Order History.
1. Take "every" term before of the description column before MKT or LMT with the exception of the @ sign, the value that follows the + or -.
2. Ignore descriptions for items that have not been "filled".
3. Once each term is defined above look for the spread that contains each term on a single row.
4. If any of the terms identified, are separated by a slash (/), then those terms can appear on separate rows but must still appear within the same Spread.
Thank you sir! This is quite a complicated problem to solve.
Here is a video of further analysis and simplification http://screencast.com/t/oyERW6EgHSq. I wrote the notation below after creating the video.
To recap: Matching rules for Notes found in Order History.
1. Take "every" term before of the description column before MKT or LMT with the exception of the @ sign, the value that follows the + or -.
2. Ignore descriptions for items that have not been "filled".
3. Once each term is defined above look for the spread that contains each term on a single row.
4. If any of the terms identified, are separated by a slash (/), then those terms can appear on separate rows but must still appear within the same Spread.
ASKER
Oops. I left one thing out.
When processing notes for either data type, we should ensure that all "Filled" notes get matched and copied. If they are unable to be matched, then a warning that says "x number of notes" could not be matched should appear. This will allow us to fine tune the process over time. Once we are reasonably sure we are catching them all, I want to move on to some calculations and fine tuning the look and filtering of the output.
When processing notes for either data type, we should ensure that all "Filled" notes get matched and copied. If they are unable to be matched, then a warning that says "x number of notes" could not be matched should appear. This will allow us to fine tune the process over time. Once we are reasonably sure we are catching them all, I want to move on to some calculations and fine tuning the look and filtering of the output.
ok for both your last 2 post but you did not give me a feedback on the version proposed like on your quick estimation how good is it or bad is it or ... ??? Are the note matched correct ? not correct ?
I know very well that will need to flag the ones not matched so we can finetune this was my mext step but had to get a first assessment to know if I hit it so I can move on
gowflow
I know very well that will need to flag the ones not matched so we can finetune this was my mext step but had to get a first assessment to know if I hit it so I can move on
gowflow
ASKER
None of the notes transfered on the example in the video.
Well I am surprised the notes didn't come out on the last code I posted anyhow I am working on discecting it thouroully and will get back to you once done. It is much more complex than you could think but will nail it !!!
gowflow
gowflow
ASKER
Thank you sir! You are quite talented.
ASKER
If the macro were able to be run locally from outside the sheet, then that would be perfect because I could make processing the original CSV export into a one-step process.
ASKER
Also, if the macro didn't have to live inside the sheet to run properly, then passing the resulting output back to the customer would also be a one-step process.
lets first finish with the notes successfully then if you want we can address separately once this issue is done the change of the workbook functionning to be independant of the sheets.
gowflow
gowflow
ASKER
I don't see your solution on the notes??
comming up ... after these messages !!! stay tuned
gowflow
gowflow
check out this version and let me know.
I had to cancel checking the price as many instances where in the note the price is say 1.48 but in the file it is 1.5 and only the price was not matching and it was giving lot of items unmatched so I figured it is a rounding issue so removed the checking of the price.
Anyway whn you run it each time it does not find a match it will tell you and give you the note description and at the end will give oyu stats.
Pls chk and let me know
gowflow
I had to cancel checking the price as many instances where in the note the price is say 1.48 but in the file it is 1.5 and only the price was not matching and it was giving lot of items unmatched so I figured it is a rounding issue so removed the checking of the price.
Anyway whn you run it each time it does not find a match it will tell you and give you the note description and at the end will give oyu stats.
Pls chk and let me know
gowflow
Option Explicit
'--> This macro takes either pm or rm tabs and creates a properly formatted table in a new tab'
'called Output with the data from the Trade History section of the original tab. It also takes'
'the notes from the Order History section of the original tab and then indexes and sorts each'
'Spread into meaningful positions.'
'Future development will include hiding the Leg and Spread numbers, as well as making some
'calculations on each overall position.'
Const AOH = "Account Order History"
Const ATH = "Account Trade History"
Const PAL = "Profits and Losses"
Const EQT = "Equities"
Const OPT = "Options"
Const OTH = "Others"
Const FRX = "Forex"
Dim wsopt As Worksheet
Private Sub AddMOColumn()
'This Sub will Add A new Column before Exp Column J that should indicate either of the following
'M for Monthly when the date in Exp is formated as Mmm-yy the date should be the 3rd friday of the month
'O for Weekly or Quarterly options
Dim MaxRow As Long, I As Long
Dim dDate As Date
Dim ThirdThuOfTheMonth As Date
Dim a
MaxRow = wsopt.Rows(wsopt.Rows.Count).End(xlUp).Row
wsopt.Select
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
wsopt.Range("J11") = "OT"
For I = 12 To MaxRow
If wsopt.Cells(I, "K") <> "" Then
If wsopt.Cells(I, "K").NumberFormat = "mmm-dd-yy" Then
wsopt.Cells(I, "J") = "O"
Else
wsopt.Cells(I, "J") = "M"
dDate = wsopt.Cells(I, "K")
dDate = DateSerial(Year(dDate), Month(dDate), 1)
Select Case Weekday(dDate)
Case 5 'If Date is a Thursday then add 14 days
ThirdThuOfTheMonth = DateValue(dDate + 14)
Case Is > 5 'If Date is Friday or saturday then add 21+1 days
ThirdThuOfTheMonth = DateValue(dDate + 21 - (Weekday(dDate) - vbThursday))
Case Is < 5 'If Date is Sunday to Thurthday then add 14+ diffrence to friday days
ThirdThuOfTheMonth = DateValue(dDate + 14 + vbThursday - Weekday(dDate))
End Select
If Weekday(ThirdThuOfTheMonth) <> vbThursday Then
MsgBox ("this date: " & ThirdThuOfTheMonth & " does not corespond to a Thursday! on row " & I & " Please check that date in Col K is actually a valid date then re-run the whole macro.")
End If
wsopt.Cells(I, "K") = ThirdThuOfTheMonth
End If
End If
Next I
End Sub
Private Sub AddDaysandPLColumn()
'This Sub will Add 3 new Columns
'Days to Exp in Col N next to type Column which should be the diffrence in days between Exec Time and Exp
'P/L Balance Col R
'P/L% Col S
Dim MaxRow As Long, I As Long
Dim dDate As Date
Dim ThirdThuOfTheMonth As Date
Dim a
MaxRow = wsopt.Rows(wsopt.Rows.Count).End(xlUp).Row
wsopt.Select
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
wsopt.Range("N11") = "Days to Exp"
Columns("R:S").Select
Selection.Insert Shift:=xlToRight
wsopt.Range("R11") = "P/L Balance"
wsopt.Range("S11") = "P/L %"
For I = 12 To MaxRow
If wsopt.Cells(I, "K") <> "" Then
wsopt.Cells(I, "N") = DateValue(wsopt.Cells(I, "C")) - DateValue(wsopt.Cells(I, "K"))
End If
Next I
End Sub
Sub TradeReport()
Dim wsPM As Worksheet
Dim aCell As Range, bCell As Range, cCell As Range, delRange As Range
Dim AOHRow As Long, PALRow As Long, ATHRow As Long
Dim LastRow As Long, I As Long, J As Long
Dim shName As String
Dim SearchString As String, MatchString As String, init1String As String, init2String As String
Dim intNum As Long
Dim FirstAddress As String
On Error GoTo Whoa
Application.ScreenUpdating = True
Set wsPM = ActiveSheet
shName = wsPM.Name
'--> Make New "Output" Sheet if one already exists'
On Error Resume Next
Application.DisplayAlerts = False
Set wsopt = Sheets("Output")
intNum = 0
While Err.Number = 0
Err.Clear
intNum = intNum + 1
Set wsopt = Sheets("Output" & intNum)
Wend
Err.Clear
On Error GoTo 0
Application.DisplayAlerts = True
'--> Recreate "Output" Sheet and move it to the right'
Set wsopt = Worksheets.Add(after:=Worksheets(Worksheets.Count))
If intNum = 0 Then
wsopt.Name = "Output"
Else
wsopt.Name = "Output" & intNum
End If
'--> Find the "Account Trade History" cell in Sheet PM'
Set aCell = wsPM.Columns(1).Find(What:=ATH, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'--> If "Account Trade History" is found'
If Not aCell Is Nothing Then
'--> Get the starting row of "Account Trade History"'
ATHRow = aCell.Row
If LCase(Left(shName, 2)) = "pm" Then
'--> Find the "Options" cell in Sheet PM'
Set bCell = wsPM.Columns(1).Find(What:=OPT, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If bCell Is Nothing Then
'--> Find the "Forex" cell in Sheet PM if Options is not found'
Set bCell = wsPM.Columns(1).Find(What:=FRX, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End If
If bCell Is Nothing Then
'--> Find the "Profits and Losses" cell in Sheet PM if Forex is not found'
Set bCell = wsPM.Columns(1).Find(What:=PAL, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End If
End If
If LCase(Left(shName, 2)) = "rm" Then
'--> Find the "Equities" cell in Sheet RM'
Set bCell = wsPM.Columns(1).Find(What:=EQT, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If bCell Is Nothing Then
'--> Find the "Others" cell in Sheet RM if Equities is not found
Set bCell = wsPM.Columns(1).Find(What:=OTH, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End If
End If
'--> If "Profits and Losses" is found'
If Not bCell Is Nothing Then
'--> Get the starting row of "Profits and Losses"
PALRow = bCell.Row
'--> Output Trade History to new tab.'
wsPM.Rows((ATHRow + 1) & ":" & (PALRow - 1)).Copy wsopt.Rows(1)
If LCase(Left(shName, 2)) = "rm" Then
wsopt.Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
End If
With wsopt
'--> Remove last three columns because they are duplicates.'
.Columns("M:O").Delete Shift:=xlToLeft
'--> Define the notes column.'
.Range("A1").Value = "Notes"
.Columns("A:A").Replace What:="DEFAULT", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
'--> Get the last row of Output Sheet'
LastRow = .Cells.Find(What:="*", after:=[A1], SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'--> Unsure of what this section does?'
Set cCell = wsPM.Columns(1).Find(What:=AOH, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not cCell Is Nothing Then
AOHRow = cCell.Row + 1
'--> Copy the notes into the new notes column.
wsPM.Range("IV:IV").ClearContents
For I = AOHRow To ATHRow
SearchString = wsPM.Range("D" & I).Value & "###" & _
wsPM.Range("F" & I).Value & "###" & _
wsPM.Range("G" & I).Value & "###" & _
wsPM.Range("H" & I).Value & "###" & _
wsPM.Range("I" & I).Value
wsPM.Range("IV" & I).Value = SearchString
Next I
For J = 2 To LastRow
MatchString = .Range("D" & J).Value & "###" & _
.Range("F" & J).Value & "###" & _
.Range("G" & J).Value & "###" & _
.Range("H" & J).Value & "###" & _
.Range("I" & J).Value
Set cCell = wsPM.Range("IV:IV").Find(What:=MatchString, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not cCell Is Nothing Then
FirstAddress = cCell.Address
Do
If wsopt.Range("F" & J).Value = wsPM.Range("F" & cCell.Row).Value Then
.Range("A" & J).Value = wsPM.Range("A" & cCell.Row).Value
Exit Do
Else
Set cCell = wsPM.Range("IV:IV").FindNext(cCell)
End If
Loop While Not cCell Is Nothing And cCell.Address <> FirstAddress
End If
Next J
End If
GoTo There
'--> Copy the notes into the new notes column. old'
For I = AOHRow To ATHRow
SearchString = wsPM.Range("D" & I).Value & "###" & _
wsPM.Range("F" & I).Value & "###" & _
wsPM.Range("G" & I).Value & "###" & _
wsPM.Range("H" & I).Value & "###" & _
wsPM.Range("I" & I).Value
init1String = wsPM.Range("F" & I).Value
For J = 2 To LastRow
init2String = wsopt.Range("F" & J).Value
If init1String = init2String Then
MatchString = .Range("D" & J).Value & "###" & _
.Range("F" & J).Value & "###" & _
.Range("G" & J).Value & "###" & _
.Range("H" & J).Value & "###" & _
.Range("I" & J).Value
If MatchString = SearchString Then
.Range("A" & J).Value = wsPM.Range("A" & I).Value
End If
End If
Next J
Next I
'End If
There:
'--> Make the Output tab into a table that can be filtered by the column headers.'
.ListObjects.Add(xlSrcRange, Range("$A$1:$L$" & LastRow), , xlYes).Name = "Table1"
.ListObjects("Table1").ShowTableStyleRowStripes = False
'ListObjects("Table1").TableStyle = "TableStyleLight1"'
.Columns("B:L").EntireColumn.AutoFit
End With
End If
End If
Application.ScreenUpdating = False
NewUpdates
FixDate
Indexing
Sorting
AddMOColumn
AddDaysandPLColumn
If LCase(Left(shName, 2)) = "rm" Then
UpdateNotes wsPM, AOHRow, ATHRow
End If
wsopt.Cells(1, 1).Select
Application.ScreenUpdating = True
'--> Clean Up and Exit.'
LetsContinue:
Application.ScreenUpdating = True
Set aCell = Nothing: Set bCell = Nothing: Set cCell = Nothing: Set delRange = Nothing
On Error Resume Next
Set wsopt = Nothing: Set wsPM = Nothing
On Error GoTo 0
Exit Sub
'--> Error Handling'
Whoa:
MsgBox Err.Description
Resume LetsContinue
End Sub
Private Sub FixDate()
'This Sub will go thru Exp Column Col J and will Fix the date to allow for either mm-yy or mmm-dd-yy.
Dim MaxRow As Long, I As Long
Dim tmpM As String, tmpD As String, tmpY As String
MaxRow = wsopt.Rows(wsopt.Rows.Count).End(xlUp).Row
For I = 12 To MaxRow
'---> Strip Dates and Fix them
If wsopt.Cells(I, "J") <> "" Then
If IsDate(wsopt.Cells(I, "J")) Then
'---> Format normally if normal Date format as 'mmm-dd-yy'
wsopt.Cells(I, "J").NumberFormat = "Mmm-yy"
Else
'---> Strip the date in Day month Year then re-group so system would recognize
' it as a date and then apply format 'mmm-yy'
tmpM = Left(wsopt.Cells(I, "J"), 3)
tmpY = Right(wsopt.Cells(I, "J"), 2)
tmpD = Mid(wsopt.Cells(I, "J"), 4, Len(wsopt.Cells(I, "J")) - 6)
wsopt.Cells(I, "J") = tmpM & " " & Format(Val(tmpD), "") & ", " & Format(Val(tmpY), "")
wsopt.Cells(I, "J").NumberFormat = "Mmm-dd-yy"
End If
End If
Next I
End Sub
Private Sub NewUpdates()
Dim I As Integer
'---> Create extra space at the top of the table of approximately 10 rows.
wsopt.Rows("1:10").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'---> Modify the date format of the Exec Time column to reflect the format of ddd mmm dd, yyyy hh:mm'
'and the Exp column to reflect the format of mm-yy.'
For I = 12 To wsopt.Cells(wsopt.Rows.Count, "K").End(xlUp).Row
wsopt.Range("B" & I).NumberFormat = "ddd mmm-dd-yyyy hh:mm"
wsopt.Range("G" & I).NumberFormat = "mmm-yy"
Next I
'--> Add Columns for Postion, Spread, and Leg Numbers'
'Add P# column before the Exec Time column with the comment "Position #" in the header for the P# column.'
'Add S# column between the Exec Time and Spread columns with the comment "Spread #" in the header for the S# column.'
'Add L# column between the Spread and Side columns with the comment "Leg #" in the header for the L# column.'
wsopt.Columns("B:B").Insert Shift:=xlToRight
wsopt.Columns("D:D").Insert Shift:=xlToRight
wsopt.Columns("F:F").Insert Shift:=xlToRight
'---> Format column D and E to number format
wsopt.Range("D12:D" & wsopt.Cells(wsopt.Rows.Count, "K").End(xlUp).Row).NumberFormat = "0"
wsopt.Range("F12:F" & wsopt.Cells(wsopt.Rows.Count, "K").End(xlUp).Row).NumberFormat = "0"
wsopt.Range("B12:B" & wsopt.Cells(wsopt.Rows.Count, "K").End(xlUp).Row).NumberFormat = "0"
'---> Rename the header of the added Position, Spread, and Leg Number columns'
wsopt.Range("B11") = "P#": wsopt.Range("D11") = "S#": wsopt.Range("F11") = "L#"
'---> Set autofit to the width of column D and E
wsopt.Columns("D:D").EntireColumn.AutoFit
wsopt.Columns("F:F").EntireColumn.AutoFit
wsopt.Columns("B:B").EntireColumn.AutoFit
'---> Add comments
wsopt.Range("B11").AddComment
wsopt.Range("B11").Comment.Visible = False
wsopt.Range("B11").Comment.Text Text:="Position No"
wsopt.Range("D11").AddComment
wsopt.Range("D11").Comment.Visible = False
wsopt.Range("D11").Comment.Text Text:="Spread No"
wsopt.Range("F11").AddComment
wsopt.Range("F11").Comment.Visible = False
wsopt.Range("F11").Comment.Text Text:="Leg No"
'---> Shrink the printout so all column will be printed in the same page
ActiveSheet.PageSetup.PrintArea = ""
wsopt.PageSetup.FitToPagesWide = 1
wsopt.PageSetup.FitToPagesTall = 1
End Sub
'DEFINITIONS - Definitions to understand prior to Indexing and Ordering'
'1. Positions - Positions can be made up of more than one Spread and the newest Positions appear at the top of the sheet.'
'2. Spreads - Spreads can be made up of more than one Leg and their order with a position can change.'
'3. Legs - Legs are made up of individual rows within a Spread and there order within the spread never changes.'
Private Sub Indexing()
'Each Position, Spread, and Leg is indexed with a number according to the value in its Exec Date column.'
'P# - Positions are numbered sequentially with 1 being meaning that it contains a newer Exec Date than any other positions.'
'S# - Spreads are numbered sequentially with 1 meaning that it contains the a newer Exec Date than the other spreads.'
'L# - Legs are numbered sequentially with 1 containing the oldest Exec Date within it's particular spread and starting over at the next spread.'
'If the Exec Date column (which is actually a date and time stamp) contains the same values, then the Symbol is used in alphabetical order.'
Dim I As Integer, K As Double, Z As Double, J As Double, l As Double, test As Boolean, test1 As Boolean
Dim WS As Worksheet, q As Double, Rcount As Double, x As Double, SpreadNo As Double
Sheets.Add after:=Sheets(Sheets.Count)
Set WS = Sheets(Sheets.Count)
K = 0: Z = -1: Rcount = wsopt.Cells(wsopt.Rows.Count, "K").End(xlUp).Row: SpreadNo = 0
For I = 12 To Rcount
If K = 0 And Z = -1 Then
K = K + 1: Z = Z + 2:
WS.Cells(K, Z) = wsopt.Range("I" & I) & wsopt.Range("J" & I) & wsopt.Range("L" & I) & " " & wsopt.Range("K" & I)
For x = I + 1 To Rcount
If wsopt.Range("C" & x) = "" Then
If InStr(1, WS.Cells(K, Z), wsopt.Range("K" & x)) = 0 Then WS.Cells(K, Z) = WS.Cells(K, Z) & " " & wsopt.Range("K" & x)
Else
Exit For
End If
Next x
WS.Cells(K + 1, Z) = wsopt.Range("C" & I) & wsopt.Range("E" & I)
WS.Cells(K + 1, Z + 1) = WS.Cells(K, Z + 1) + 1
wsopt.Range("B" & I) = 1: wsopt.Range("F" & I) = 1
Else
If J = 0 Then J = 1
If wsopt.Range("C" & I) = "" Then
test = True
Else
test = False: q = -1
For J = 1 To Z Step 2
q = q + 1
If InStr(1, WS.Cells(1, J), wsopt.Range("I" & I) & wsopt.Range("J" & I) & wsopt.Range("L" & I)) > 0 Then
If InStr(1, WS.Cells(1, J), wsopt.Range("K" & I)) > 0 Then
wsopt.Range("B" & I) = J - q: test = True
For x = I + 1 To Rcount
If wsopt.Range("C" & x) = "" Then
If InStr(1, WS.Cells(K, J), wsopt.Range("K" & x)) = 0 Then WS.Cells(K, J) = WS.Cells(K, J) & " " & wsopt.Range("K" & x)
Else
Exit For
End If
Next x
Else
For x = I + 1 To Rcount
If wsopt.Range("C" & x) = "" Then
If InStr(1, WS.Cells(K, J), wsopt.Range("K" & x)) > 0 Then wsopt.Range("B" & I) = J - q: test = True
Else
Exit For
End If
Next x
If test = True Then
WS.Cells(K, J) = WS.Cells(K, J) & " " & wsopt.Range("K" & I)
For x = I + 1 To Rcount
If wsopt.Range("C" & x) = "" Then
If InStr(1, WS.Cells(K, J), wsopt.Range("K" & x)) = 0 Then WS.Cells(K, J) = WS.Cells(K, Z) & " " & wsopt.Range("K" & x)
Else
Exit For
End If
Next x
End If
End If
If test = True Then Exit For
End If
Next J
End If
If test = True Then
l = WS.Cells(WS.Rows.Count, J).End(xlUp).Row + 1
If wsopt.Range("C" & I) = "" Then
If J = 0 Then wsopt.Range("B" & I) = 1 Else wsopt.Range("B" & I) = J - q
WS.Cells(l - 1, J + 1) = WS.Cells(l - 1, J + 1) + 1
wsopt.Range("F" & I) = WS.Cells(l - 1, J + 1)
Else
WS.Cells(l, J) = wsopt.Range("C" & I) & wsopt.Range("E" & I)
WS.Cells(l, J + 1) = WS.Cells(l, J + 1) + 1
wsopt.Range("F" & I) = 1
End If
Else
K = 1: Z = Z + 2: q = q + 1
WS.Cells(K, Z) = wsopt.Range("I" & I) & wsopt.Range("J" & I) & wsopt.Range("L" & I) & " " & wsopt.Range("K" & I)
For x = I + 1 To Rcount
If wsopt.Range("C" & x) = "" Then
If InStr(1, WS.Cells(K, Z), wsopt.Range("K" & x)) = 0 Then WS.Cells(K, Z) = WS.Cells(K, Z) & " " & wsopt.Range("K" & x)
Else
Exit For
End If
Next x
WS.Cells(K + 1, Z) = wsopt.Range("C" & I) & wsopt.Range("E" & I)
WS.Cells(K + 1, Z + 1) = WS.Cells(K, Z + 1) + 1
wsopt.Range("B" & I) = Z - q: wsopt.Range("F" & I) = 1
End If
End If
Next I
Application.DisplayAlerts = False
WS.Delete
Application.DisplayAlerts = True
'---> The empty cells in the Exec Time and Spread columns should take on the information'
'of the preceding cell because they are part of the same Spread.'
For I = 12 To Rcount
If wsopt.Range("E" & I) = "" And I > 12 Then
wsopt.Range("E" & I) = wsopt.Range("E" & I - 1)
wsopt.Range("C" & I) = wsopt.Range("C" & I - 1)
wsopt.Range("C" & I & ":E" & I).Font.Color = -5395027
End If
Next I
wsopt.Sort.SortFields.Add Key:=Range("C11"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A12:O" & Rcount)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
For I = 12 To Rcount
If wsopt.Range("F" & I) = 1 Then
SpreadNo = SpreadNo + 1: wsopt.Range("D" & I) = SpreadNo
Else
wsopt.Range("D" & I) = SpreadNo
End If
Next I
End Sub
Private Sub Sorting()
'Positions can contain single or multi-legged spreads but the sort order of the legs 'within' each'
'spread never changes. This is an important point because by contrast, the sort order in which each
'spread appears within the overall position does indeed change. Here the oldest spreads should'
'appear at the top of the position while newer spreads should appear at the bottom of the position.'
Dim I As Double, Brow As Double, Erow As Double, Prow As Double
Dim Rcount As Double
'On Error Resume Next
Rcount = wsopt.Cells(wsopt.Rows.Count, "B").End(xlUp).Row
'---> Sorting the output by the Position No.
wsopt.Sort.SortFields.Clear
wsopt.Sort.SortFields.Add Key:=Range("B11"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With wsopt.Sort
.SetRange Range("A11:O" & Rcount)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'--> Skipping a row between each major position
For I = Rcount To 13 Step -1
If wsopt.Range("B" & I) <> wsopt.Range("B" & I - 1) And wsopt.Range("B" & I) <> "" Then
wsopt.Rows(I & Chr(58) & I).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'--> Added by gowflow to keep correct counting of last row
Rcount = Rcount + 1
End If
Next I
'--> Sorting the output by Date descending
Brow = 0
'--> Changed by gowflow from upper Rcount to Rcount +1
wsopt.Sort.SortFields.Add Key:=wsopt.Range("C11"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
For I = 12 To Rcount
If wsopt.Range("B" & I) <> "" Then
If Brow = 0 Then
Brow = I: Erow = I
Else
Erow = I
End If
Else
With wsopt.Sort
.SetRange Range("A" & Brow & ":O" & Erow)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Brow = 0
wsopt.Range("A" & I - 1 & ":O" & I - 1).Copy Destination:=wsopt.Range("A" & I & ":O" & I)
wsopt.Range("A" & I & ":O" & I).Font.ThemeColor = xlThemeColorDark1
End If
Next I
wsopt.Range("P" & 12) = wsopt.Range("D" & 12): Prow = wsopt.Range("D" & 12)
For I = 13 To Rcount
If wsopt.Range("B" & I) = wsopt.Range("B" & I - 1) Then
wsopt.Range("P" & I) = Prow
Else
Prow = wsopt.Range("D" & I)
wsopt.Range("P" & I) = Prow
End If
Next I
wsopt.Range("B" & 12) = 1: Prow = 1
For I = 13 To Rcount
If wsopt.Range("P" & I) = wsopt.Range("P" & I - 1) Then
wsopt.Range("B" & I) = Prow
If wsopt.Range("P" & I) = wsopt.Range("P" & I + 1) Then wsopt.Range("B" & I).Font.Color = -5395027
Else
Prow = Prow + 1
wsopt.Range("B" & I) = Prow
End If
Next I
wsopt.Columns("P:P").Delete Shift:=xlToLeft
End Sub
Sub UpdateNotes(WS As Worksheet, FMRow As Long, TORow As Long)
'This Sub will update noted for rm sheets from the Account Order History section in Col A
'To match with data in the Output file created under wsopt variable.
Dim MaxRow As Long, I As Long, J As Long, K As Long, Z As Long
Dim Note As String
Dim NoteItem
Dim Row As Range
Dim Status
Dim ParClose As Long, RowMatched As Long, UnMatchedNotes As Long, NoteFound As Long
Dim BkRatio As Boolean, FoundIT As Boolean
Dim STMonth As Date, ENDMonth As Date
MaxRow = wsopt.Rows(wsopt.Rows.Count).End(xlUp).Row
'---> Clean the Area F to Z to dissec Notes
WS.Range("F" & FMRow & ":ZZ" & TORow).ClearContents
'---> Clean the notes and copy to Col F only for Status Filled Notes
For I = FMRow + 1 To TORow
Status = Split(WS.Cells(I, "D"), " ")
For J = 0 To UBound(Status)
If UCase(Status(J)) = "FILLED" Then
'---> If a note start with (Replacing ... then remove the first part till the first space
If Left(WS.Cells(I, "C"), 10) = "(Replacing" Then
ParClose = InStr(1, WS.Cells(I, "C"), ")")
Else
ParClose = 0
End If
If ParClose <> 0 Then
WS.Cells(I, "F") = Mid(WS.Cells(I, "C"), ParClose + 2, Len(WS.Cells(I, "C")) - ParClose - 1)
Else
WS.Cells(I, "F") = WS.Cells(I, "C")
End If
Note = WS.Cells(I, "F")
NoteItem = Split(Note, " ")
Z = 0
'---> Check to see if BACKRATIO is found then treat the sequence diffrently
If InStr(1, WS.Cells(I, "F"), "BACKRATIO") <> 0 Then
BkRatio = True
Else
BkRatio = False
End If
'---> Loop thru all items in the Note
For K = 0 To UBound(NoteItem)
'---> Remove any items that has open/close brakets as this is comment
If Left(NoteItem(K), 1) = "(" Or Right(NoteItem(K), 1) = ")" Then
K = K + 1
Z = Z - 1
End If
'---> Remove any first letter / at first position
If Left(NoteItem(K), 1) = "/" Then NoteItem(K) = Right(NoteItem(K), Len(NoteItem(K)) - 1)
'---> Keep only VERTICAL/BUTTERFLY/STRANGLE/DIAGONAL in Second position
If K = 2 Then
If UCase(NoteItem(K)) = "VERTICAL" Or _
UCase(NoteItem(K)) = "BUTTERFLY" Or _
UCase(NoteItem(K)) = "STRANGLE" Or _
UCase(NoteItem(K)) = "DIAGONAL" Then
Z = 0
Else
Z = 1
End If
End If
If BkRatio Then
'---> If BACKRATIO encountered then treat the sequence diffrently.
Select Case K
Case 0
WS.Cells(I, K + 7).NumberFormat = "@"
WS.Cells(I, K + 7) = Format(NoteItem(0), "@")
Case 1, 2
WS.Cells(I, 7 + 1).NumberFormat = "@"
WS.Cells(I, 7 + 1) = Format(NoteItem(1) & " " & NoteItem(2), "@")
Case Else
WS.Cells(I, K + 7 - 1).NumberFormat = "@"
WS.Cells(I, K + 7 - 1) = Format(NoteItem(K), "@")
End Select
Else
If InStr(1, Note, "@") <> 0 Then
'---> in any sequence is @ is encountered make sure it is positioned
' in Col P as it is the Price Column the rest will follow.
If Left(NoteItem(K), 1) = "@" And K + 7 + Z <> 16 Then
Z = 16 - 7 - K
End If
Else
If K + 7 + Z = 16 Then
Z = 16 - 7 - K + 1
End If
End If
WS.Cells(I, K + 7 + Z).NumberFormat = "@"
WS.Cells(I, K + 7 + Z) = Format(NoteItem(K), "@")
End If
Next K
End If
Next J
Next I
'---> Loop Again thru all the notes in the Account Order History section
' and match the columns where there is data to find the threads in sheet output
' Columns in Sheet G H I J K L M N O P Q
' Columns in Output G H E I - [K] L M P Q
' Column Num Output 7 5 9 11 12 13 16 17
' Columns to check Y Y Y Y Y Y Y Y
For I = FMRow + 1 To TORow
If WS.Cells(I, "F") <> "" Then
If WS.Cells(I, "G") <> "" Then wsopt.UsedRange.AutoFilter 7, WS.Cells(I, "G")
If WS.Cells(I, "I") <> "" Then wsopt.UsedRange.AutoFilter 5, WS.Cells(I, "I")
If WS.Cells(I, "J") <> "" Then wsopt.UsedRange.AutoFilter 9, WS.Cells(I, "J")
If WS.Cells(I, "L") <> "" Then
If Len(WS.Cells(I, "L")) = 3 Then
On Error Resume Next
STMonth = DateSerial(WS.Cells(I, "M"), Month(DateValue(WS.Cells(I, "L") & " 1," & Year(Now))), 1)
ENDMonth = DateSerial(Year(STMonth), Month(STMonth), Day(Application.WorksheetFunction.EoMonth(STMonth, 0)))
wsopt.UsedRange.AutoFilter 11, Criteria1:=">=" & STMonth, Operator:=xlAnd, Criteria2:="<=" & ENDMonth
Else
STMonth = DateValue(Left(WS.Cells(I, "L"), 3) & " " & Right(WS.Cells(I, "L"), Len(WS.Cells(I, "L")) - 3) & "," & WS.Cells(I, "M"))
wsopt.UsedRange.AutoFilter 11, Criteria1:=">=" & STMonth, Operator:=xlAnd, Criteria2:="<=" & STMonth
End If
End If
On Error GoTo 0
If WS.Cells(I, "N") <> "" And InStr(1, WS.Cells(I, "N"), "/") = 0 Then wsopt.UsedRange.AutoFilter 12, WS.Cells(I, "N")
If WS.Cells(I, "O") <> "" And InStr(1, WS.Cells(I, "N"), "/") = 0 Then wsopt.UsedRange.AutoFilter 13, WS.Cells(I, "O")
'If WS.Cells(I, "P") <> "" Then wsopt.UsedRange.AutoFilter 16, Val(Right(WS.Cells(I, "P"), Len(WS.Cells(I, "P")) - 1))
If WS.Cells(I, "Q") <> "" Then wsopt.UsedRange.AutoFilter 17, WS.Cells(I, "Q")
NoteFound = NoteFound + 1
FoundIT = True
For Each Row In wsopt.Range("11:" & MaxRow).EntireRow.SpecialCells(xlCellTypeVisible).Rows
If wsopt.Cells(Row.Row, "A").EntireRow.Hidden = False And wsopt.Cells(Row.Row, "C") <> "" And Row.Row <> 11 Then
wsopt.Cells(Row.Row, "A") = WS.Cells(I, "F")
RowMatched = RowMatched + 1
FoundIT = False
End If
Next Row
If FoundIT Then
MsgBox (WS.Cells(I, "F") & Chr(10) & "Was not mached in sheet Output !")
UnMatchedNotes = UnMatchedNotes + 1
End If
'---> Clear All Filters
wsopt.UsedRange.AutoFilter 5
wsopt.UsedRange.AutoFilter 7
wsopt.UsedRange.AutoFilter 9
wsopt.UsedRange.AutoFilter 11
wsopt.UsedRange.AutoFilter 12
wsopt.UsedRange.AutoFilter 13
wsopt.UsedRange.AutoFilter 16
wsopt.UsedRange.AutoFilter 17
End If
Next I
MsgBox ("A total of " & RowMatched & " Spread items were matched with notes." & Chr(10) _
& "A total of " & NoteFound & " Notes FILLED were found in the Order History Section." & Chr(10) _
& "A total of " & UnMatchedNotes & " Notes were not Matched in Sheet Output.")
End Sub
ASKER
hmm,
This is interesting but definitely complicating.
The numbers should always balance. If there is only one note, then there is no reason to use it twice. It refers to only one spread and the logic should sort that out. In the resulting error checking message, either all 29 filled notes were matched or they weren't, but there is certainly no reason for 29 filled items to have notes and a higher number of notes copied. Let's take one more crack at it and give up in favor of more important things.
Thanks for your help.
This is interesting but definitely complicating.
The numbers should always balance. If there is only one note, then there is no reason to use it twice. It refers to only one spread and the logic should sort that out. In the resulting error checking message, either all 29 filled notes were matched or they weren't, but there is certainly no reason for 29 filled items to have notes and a higher number of notes copied. Let's take one more crack at it and give up in favor of more important things.
Thanks for your help.
sorry did not understand your post. What's the problem ?
gowflow
gowflow
ASKER
The number of filled notes in Order history should always produce the same number of notes in the Output tab. There should not be any difference.
yes but the problem is when you read a note in the order history the information there give you the possibility of several items in the Output tab So I was looping thru all the items in output tab and affecting the note this is why you get more in the output tab !!! some times you have a one to one match but most of the times you have more than one item this is due to the fact that we are not taking the price and the quantity in concideration.
Now if we take the price then you will have in output tab less notes than there is in the order history this is due to the price not beeing EXACTLY the same in both sometimes it is couple of cents diffrence !
You tell me how you want to takcle this shal we say that the price should match at 1 decimal ? maybe we can then have more accurate matches ?
gowflow
Now if we take the price then you will have in output tab less notes than there is in the order history this is due to the price not beeing EXACTLY the same in both sometimes it is couple of cents diffrence !
You tell me how you want to takcle this shal we say that the price should match at 1 decimal ? maybe we can then have more accurate matches ?
gowflow
ASKER
Ah ok.
Qty could be up to but not over and Price could also be up to but not over.
Qty could be up to but not over and Price could also be up to but not over.
one more the price is Col O Price or Col P net price ? I supposed it is Net Price Col P
gowflow
gowflow
ASKER
Correct, but both could be used to come up with a more accurate match.
ASKER
Oh I see now. Yes, just net price.
so if I am getting this right let me summarize
you mean to say that if you have 100 notes you should have maximum (if they all find a match in output) have a total of 100 rows matched no more.
If they don't all match you should have
100 notes were found
70 were matched
30 were not matched
that is how it should be ?
gowflow
you mean to say that if you have 100 notes you should have maximum (if they all find a match in output) have a total of 100 rows matched no more.
If they don't all match you should have
100 notes were found
70 were matched
30 were not matched
that is how it should be ?
gowflow
ASKER
That is correct. The descriptions that are not matched along with it's row # in the original order history should be listed to assist us in troubleshooting why it isn't matching..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Looks great for the rm tab that I ran it against. I want to clarify something about your comment regarding "typed incorrectly." This can't happen and I have illustrated why. I'm going to give up on this after your next tweak IF you need to make any. If you feel like it's ok for now, then I'll accept this and we can move on in favor of some asthetics and ending calculations.
Let me know if you see anything else that needs to be changed after watching my video illustration http://screencast.com/t/E7sqZqTf of why something can't be incorrectly typed.
Let me know if you see anything else that needs to be changed after watching my video illustration http://screencast.com/t/E7sqZqTf of why something can't be incorrectly typed.
ASKER
Great job. More work on the notes needed but there are other things needed as well. Got to move on.
ok saw the video and now confused !
You first say work based on the description whcih is Col C and put that in the note Col A of output then now you say you need to check col A and put that in Col A of output ???
If this is what you want I think there is a problem coz on the so manynotes there is there are only a few that are in Col A and in the other rm sheet the one to the left of which your pointing at there is NONE in Col A !!!! so if we base te whole macro on Col A you will get NOTHING in Col A output !!
Areyou sure this is how you wnat to procees this ?
gowlfow
You first say work based on the description whcih is Col C and put that in the note Col A of output then now you say you need to check col A and put that in Col A of output ???
If this is what you want I think there is a problem coz on the so manynotes there is there are only a few that are in Col A and in the other rm sheet the one to the left of which your pointing at there is NONE in Col A !!!! so if we base te whole macro on Col A you will get NOTHING in Col A output !!
Areyou sure this is how you wnat to procees this ?
gowlfow
ASKER
We probably need to talk via skype before we can make any further progress on the notes.
I dont hv skype. Rules on EE unfortunately do not favor outside communications whcih should be all done thru EE. You can ofcourse if you wish so activate on my profile the HIRE ME button and I will be notified of your requesst and we can take it from that point on that would be no problem as regulated thru EE.
I hope it is no inconvenience to you as we need to play by the rules set out in this environment.
Rgds/gowflow
I hope it is no inconvenience to you as we need to play by the rules set out in this environment.
Rgds/gowflow
ASKER
I completely understand. The notes are going to be one tough nut to crack and requires a deeper level of communication than what I was able to make clear. Hopefully in time, we can get it hashed out. I was just in a hurry to move on toward making some other less difficult changes. Do you think you could help with the notes sub while I am still working able to tackle other issues in other threads?
ASKER
gowflow, Thank you again sir.
Can I entice you to chime in on this colorization issue >> https://www.experts-exchange.com/questions/27524637/Colorize-to-match-trading-platform.html
Can I entice you to chime in on this colorization issue >> https://www.experts-exchange.com/questions/27524637/Colorize-to-match-trading-platform.html
gowflow