Resolve issue with moving of notes from rm data
The macro below is designed to draw the notes from the Order History of the rm or pm data, or from the rmexport or pmexport data per the links in the related thread for those files, and then place those notes next to the appropriate row in the Output tab. The trouble with this is that for rm data the location of the correct row cannot be easily located. This is because the location is ONLY in the note itself. To make matters worse, the entire spread is combined together in the description. This IS the way it SHOULD be but it presents a problem when we were using a row-by-row method that I clearly think we should now abandon.
Since the information IN the note is the only way to determine the match for the RM data, I suggest we use that same approach for PM data as well, and abandon the previous method for matching the note. This time, I suggest we match in the following way.
So here goes... We use information at the beginning of the notes themselves to determine a match, and not for any individual leg, but for any position that contains "everything in the note besides the qty". The qty would need to be equal or less if we were to use it for matching because the Exec Time of the Order History comes before the Exec Time of the Trade History. The order of events is as follows: First the order is placed (Order History), and then it may or may not be FILLED (Trade History) and ALL NOTES for FILLED ORDERS should be accounted for.
The notes for each should be combined together into a single note with the newest one on the top of the others and related to the entire "Position" as opposed to locating each row individually.
Code below.
Since the information IN the note is the only way to determine the match for the RM data, I suggest we use that same approach for PM data as well, and abandon the previous method for matching the note. This time, I suggest we match in the following way.
So here goes... We use information at the beginning of the notes themselves to determine a match, and not for any individual leg, but for any position that contains "everything in the note besides the qty". The qty would need to be equal or less if we were to use it for matching because the Exec Time of the Order History comes before the Exec Time of the Trade History. The order of events is as follows: First the order is placed (Order History), and then it may or may not be FILLED (Trade History) and ALL NOTES for FILLED ORDERS should be accounted for.
The notes for each should be combined together into a single note with the newest one on the top of the others and related to the entire "Position" as opposed to locating each row individually.
Code below.
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
This is almost Mission Impossible !!
Let me get some clarification before we get hopes too high
1) the notes you are refering to are the 'Description' field in Col C of the original rm file under section Account Order History. pls confirm
2) If my assumption of 1) is correct then are these description Manually written or they are produced by coomputer (ie are they the result of people inputing data or they are generated by a program that follows a certain logic that if we can decript can lead us to a safe shore ???)
3) I assume that in 2) worst case scenario they are manually input which is what seems to me as a first look as there is no clear pattern on the location of the fields they relate to
HOWEVER I see some same patterns in certain section i give an example
if I look at
SELL -11 VERTICAL AMZN 100 APR 11 180/185 CALL @3.72 LMT CBOE
I notice that almost all the VERTICAL follows the same pattern
SIDE+QTY+SPREAD+SYMBOL+STI
I guess VALUEX is the spread from 180 to 190 that actually we do not need and the last column also not needed.
4) If my assumption of above notes for VERTICAL is correct and if all of the above 1 2 are also correct then I will need from you to do the exercise for me that I just did as I can read english and try to decript issue that somehow look a bit clear but I am definitively not an expert in stocks and don't know the terms and can't relate figures to which column they belong.
I would nee from you to give me for each pattern that you know are in the same pattern for notes to give me the layout same as I just did and I think then we can make the mission somehow possible !
gowflow
Let me get some clarification before we get hopes too high
1) the notes you are refering to are the 'Description' field in Col C of the original rm file under section Account Order History. pls confirm
2) If my assumption of 1) is correct then are these description Manually written or they are produced by coomputer (ie are they the result of people inputing data or they are generated by a program that follows a certain logic that if we can decript can lead us to a safe shore ???)
3) I assume that in 2) worst case scenario they are manually input which is what seems to me as a first look as there is no clear pattern on the location of the fields they relate to
HOWEVER I see some same patterns in certain section i give an example
if I look at
SELL -11 VERTICAL AMZN 100 APR 11 180/185 CALL @3.72 LMT CBOE
I notice that almost all the VERTICAL follows the same pattern
SIDE+QTY+SPREAD+SYMBOL+STI
I guess VALUEX is the spread from 180 to 190 that actually we do not need and the last column also not needed.
4) If my assumption of above notes for VERTICAL is correct and if all of the above 1 2 are also correct then I will need from you to do the exercise for me that I just did as I can read english and try to decript issue that somehow look a bit clear but I am definitively not an expert in stocks and don't know the terms and can't relate figures to which column they belong.
I would nee from you to give me for each pattern that you know are in the same pattern for notes to give me the layout same as I just did and I think then we can make the mission somehow possible !
gowflow
ASKER
Yep!
I'll do some thinking on how we might simplify this. I think putting the notes at the top of the POSITION might go along way toward not having to define as much. More thinking needed...
Thank you sir!
I'll do some thinking on how we might simplify this. I think putting the notes at the top of the POSITION might go along way toward not having to define as much. More thinking needed...
Thank you sir!
ASKER
gowflow,
Thank you for your help with this. I have defined an easier way to determine the match. I also think that the notes should be placed under each spread if that is possible but we can leave that out for now. Below are the results of my review. Please see that I have included three other data sets in the workbook that we are using to test. Only the original pm that we were using (now titled pm fast with notes) is processing when you run the macro. The others are not running correctly.
NOTE MOVING PROCEDURE FOR RM DATA
The following rules apply 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 moving procedure is for rm data only and should remain separate from the Notes moving 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.
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.
The first 7 items separated by spaces in the Description column of Order History (i.e. BUY +1 VERTICAL SPY 100 NOV 11) refers to the Side, Qty, Spread, Symbol, Strike, and Exp columns of the corresponding Spread in the Output tab in that order (Side, Qty, Spread, Symbol, Strike, and Exp). 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.
To avoid a matching conflict in the event that two similar trades exist, we have a choice of two approaches. If we wanted to avoid the complexity of matching what comes after the first 7 items to include items 8 and 9 (i.e. 125/130 Call/Put, or 125 Call, or 125/130 Call), then we might compare the Time Placed of Order History with Exec Time of Trade History to simply determine which are closest together. The Time Placed of the Order History will always be the same or come before Exec Time of the Trade History.
Thank you again, sir!
Thank you for your help with this. I have defined an easier way to determine the match. I also think that the notes should be placed under each spread if that is possible but we can leave that out for now. Below are the results of my review. Please see that I have included three other data sets in the workbook that we are using to test. Only the original pm that we were using (now titled pm fast with notes) is processing when you run the macro. The others are not running correctly.
NOTE MOVING PROCEDURE FOR RM DATA
The following rules apply 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 moving procedure is for rm data only and should remain separate from the Notes moving 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.
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.
The first 7 items separated by spaces in the Description column of Order History (i.e. BUY +1 VERTICAL SPY 100 NOV 11) refers to the Side, Qty, Spread, Symbol, Strike, and Exp columns of the corresponding Spread in the Output tab in that order (Side, Qty, Spread, Symbol, Strike, and Exp). 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.
To avoid a matching conflict in the event that two similar trades exist, we have a choice of two approaches. If we wanted to avoid the complexity of matching what comes after the first 7 items to include items 8 and 9 (i.e. 125/130 Call/Put, or 125 Call, or 125/130 Call), then we might compare the Time Placed of Order History with Exec Time of Trade History to simply determine which are closest together. The Time Placed of the Order History will always be the same or come before Exec Time of the Trade History.
Thank you again, sir!
ok will do, will revert
gowflow
gowflow
I looked at the workbook you attached and found the following sheets:
1 - rm full many spread no notes
2 - rm full few spreads some notes
3 - pm fast with notes
4 - pm full with notes
you say;
Quote
Only the original pm that we were using (now titled pm fast with notes) is processing when you run the macro. The others are not running correctly.
Unquote
1) Do you mean sheet # 3 here is running fine but #1 and #2 and # 4 are not running fine ?
2) Is format of all sheets the same ?? I see 1 and 2 are rm are they like the rm previously posted (I mean format not content as this is the most important) and is the pm here same format as the previous pm ???
3) I see that from now on your sheet will start with the first 2 letters rm or pm and that's what need to be tested to know hte format not necessarely the full name of the sheet to be 'rm' or 'pm' like it was designed in the pmacro so far pls confirm this also.
rgds/gowflow
1 - rm full many spread no notes
2 - rm full few spreads some notes
3 - pm fast with notes
4 - pm full with notes
you say;
Quote
Only the original pm that we were using (now titled pm fast with notes) is processing when you run the macro. The others are not running correctly.
Unquote
1) Do you mean sheet # 3 here is running fine but #1 and #2 and # 4 are not running fine ?
2) Is format of all sheets the same ?? I see 1 and 2 are rm are they like the rm previously posted (I mean format not content as this is the most important) and is the pm here same format as the previous pm ???
3) I see that from now on your sheet will start with the first 2 letters rm or pm and that's what need to be tested to know hte format not necessarely the full name of the sheet to be 'rm' or 'pm' like it was designed in the pmacro so far pls confirm this also.
rgds/gowflow
ASKER
Gowflow, Thank you!
With respect to your numbered items/questions, My response is as follows:
1. Correct. I mean that sheet #3 here is running fine, but #1, #2, and #4 are not running fine.
2. No, the format of pm is different with respect to the Order History which is only used to pull the notes. The Trade History section used in the majority of the macro is the same for both rm or pm. I sent you a CSV export at one point to show that you could not use the name rm or pm to tell.
3. The best way to tell them apart is to look whether the Order History contains a Description column. If it does, then it is an rm sheet and if it doesn't it is a pm sheet. The reason for this should be evident in the screenshot below that depicts the way the original data from either rm or pm is originally found.
export-data.png
With respect to your numbered items/questions, My response is as follows:
1. Correct. I mean that sheet #3 here is running fine, but #1, #2, and #4 are not running fine.
2. No, the format of pm is different with respect to the Order History which is only used to pull the notes. The Trade History section used in the majority of the macro is the same for both rm or pm. I sent you a CSV export at one point to show that you could not use the name rm or pm to tell.
3. The best way to tell them apart is to look whether the Order History contains a Description column. If it does, then it is an rm sheet and if it doesn't it is a pm sheet. The reason for this should be evident in the screenshot below that depicts the way the original data from either rm or pm is originally found.
export-data.png
ASKER
I appear to be misinformed on the Trade History as well.
pm data includes Exec Time Spread Side Qty Symbol Exp Strike Type Price Net Price Order Type
rm data includes Exec Time Spread Side Qty Symbol Exp Strike Type Price Net Price Order Type
They are different though with respect to the fact that the columns for the pm data contain additional unused columns on either side of the ones noted.
pm data includes Exec Time Spread Side Qty Symbol Exp Strike Type Price Net Price Order Type
rm data includes Exec Time Spread Side Qty Symbol Exp Strike Type Price Net Price Order Type
They are different though with respect to the fact that the columns for the pm data contain additional unused columns on either side of the ones noted.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hmm,
It is important that any sheet regardless of the name, be able to be processed. The idea behind this is that a user could simply download the csv export and run the macro against it.
Regarding the notes one would need to determine whether to use the rules for pms which is already implemented or the rules when the sheet is an rm which is not already implemented.
If you would prefer, I can split this into a separate question as I see that this may be a problem.
It is important that any sheet regardless of the name, be able to be processed. The idea behind this is that a user could simply download the csv export and run the macro against it.
Regarding the notes one would need to determine whether to use the rules for pms which is already implemented or the rules when the sheet is an rm which is not already implemented.
If you would prefer, I can split this into a separate question as I see that this may be a problem.
This is surely a major problem as your macro (as I see it and as I took it from who ever created it) is maily based on rm and pm checking !!! if you want we can now concentrate on getting the notes done to your satissfaction and then you can post a question to hv the macro modified to work regardless what sheet name it is !
gowflow
gowflow
ASKER
If rm then a description column will always be shown in the Order History. When that is not shown, then one can be certain that it is pm data, hence pm or rm could be added to the name if it is needed. Then all would be well, correct?
ASKER
Our posts are crossing. Your suggestion would be fine.
so pls re state based on your latest post what do you want me to check for this question
Here is my understanding so far
You need to have all sheets attached in the latest workbook namely
1 - rm full many spread no notes
2 - rm full few spreads some notes
3 - pm fast with notes
4 - pm full with notes
be processed successfully and notes beeing reflected correctly (for trial on the present version we will rename each before they are run to either rm or pm.
Pls confirm.
gowflow
Here is my understanding so far
You need to have all sheets attached in the latest workbook namely
1 - rm full many spread no notes
2 - rm full few spreads some notes
3 - pm fast with notes
4 - pm full with notes
be processed successfully and notes beeing reflected correctly (for trial on the present version we will rename each before they are run to either rm or pm.
Pls confirm.
gowflow
ASKER
Gowflow, Thank you!
I think we should add something that will put an rm or pm in front of the name for the tab if the data is rm or pm data. We can do that by looking to see if a Description column is there under Order History. If a Description column is there, then the sheet should have an rm in front of the name and if it isn't there, then it should have a pm in front of the name. We can then continue to use that criteria as we have been to determine how to process the sheet.
I hope that makes sense?
I think we should add something that will put an rm or pm in front of the name for the tab if the data is rm or pm data. We can do that by looking to see if a Description column is there under Order History. If a Description column is there, then the sheet should have an rm in front of the name and if it isn't there, then it should have a pm in front of the name. We can then continue to use that criteria as we have been to determine how to process the sheet.
I hope that makes sense?
you mean to say when you import your csv sheet into the workbook say the name is
financial analysis 120211
then you want in the macro a routine that will check each sheet and if it does not find rm or pm in the first 2 letters of the sheet then
quote
will put an rm or pm in front of the name for the tab if the data is rm or pm data. We can do that by looking to see if a Description column is there under Order History. If a Description column is there, then the sheet should have an rm in front of the name and if it isn't there, then it should have a pm in front of the name.
unquote
Is this what you want ? for sure then the whole code should be ammended to look for
first 2 letters of sheet name = rm or pm
and not as it is now
the whole sheet name = rm or pm !!!
gowflow
financial analysis 120211
then you want in the macro a routine that will check each sheet and if it does not find rm or pm in the first 2 letters of the sheet then
quote
will put an rm or pm in front of the name for the tab if the data is rm or pm data. We can do that by looking to see if a Description column is there under Order History. If a Description column is there, then the sheet should have an rm in front of the name and if it isn't there, then it should have a pm in front of the name.
unquote
Is this what you want ? for sure then the whole code should be ammended to look for
first 2 letters of sheet name = rm or pm
and not as it is now
the whole sheet name = rm or pm !!!
gowflow
ASKER
Correct
ASKER
closed in favor of defining what needs to happen here more clearly starting with what we were discussing here.
https://www.experts-exchange.com/questions/27511416/Define-rm-and-pm-data-types.html
https://www.experts-exchange.com/questions/27511416/Define-rm-and-pm-data-types.html
ASKER
Also detailing the notes issue, is this post >> https://www.experts-exchange.com/questions/27511508/Notes-for-rm-data-type.html. Thank you again sir.
ASKER