Sulprobil
Search…
sbAccumulatedTradeBlotter
Please read my Disclaimer.
1
Enum trade_sheet_columns
2
ts_ccy = 1
3
ts_bought_sold
4
ts_order_id
5
ts_trade_date
6
ts_value_date
7
ts_amount
8
ts_price
9
ts_fxrate
10
ts_traded_value
11
ts_traded_value_EUR
12
ts_EMPTY
13
ts_output_ccy
14
ts_output_long_short
15
ts_output_amount_accumulated
16
ts_output_traded_value_EUR
17
End Enum 'trade_sheet_columns
18
19
Private Enum xlCI 'Excel Color Index
20
: xlCIBlack = 1: xlCIWhite: xlCIRed: xlCIBrightGreen: xlCIBlue '1 - 5
21
: xlCIYellow: xlCIPink: xlCITurquoise: xlCIDarkRed: xlCIGreen '6 - 10
22
: xlCIDarkBlue: xlCIDarkYellow: xlCIViolet: xlCITeal: xlCIGray25 '11 - 15
23
: xlCIGray50: xlCIPeriwinkle: xlCIPlum: xlCIIvory: xlCILightTurquoise '16 - 20
24
: xlCIDarkPurple: xlCICoral: xlCIOceanBlue: xlCIIceBlue: xlCILightBrown '21 - 25
25
: xlCIMagenta2: xlCIYellow2: xlCICyan2: xlCIDarkPink: xlCIDarkBrown '26 - 30
26
: xlCIDarkTurquoise: xlCISeaBlue: xlCISkyBlue: xlCILightTurquoise2: xlCILightGreen '31 - 35
27
: xlCILightYellow: xlCIPaleBlue: xlCIRose: xlCILavender: xlCITan '36 - 40
28
: xlCILightBlue: xlCIAqua: xlCILime: xlCIGold: xlCILightOrange '41 - 45
29
: xlCIOrange: xlCIBlueGray: xlCIGray40: xlCIDarkTeal: xlCISeaGreen '46 - 50
30
: xlCIDarkGreen: xlCIGreenBrown: xlCIBrown: xlCIDarkPink2: xlCIIndigo '51 - 55
31
: xlCIGray80 '56
32
End Enum
33
34
Sub Calculate_Accumulated_Blotter()
35
'Reverse("moc.LiborPlus.www") V0.1 PB 15-Jan-2011
36
Dim lRow As Long
37
Dim lColorIdx As Long
38
Dim dSign As Double
39
Dim dSum As Double
40
Dim vColors As Variant
41
Dim oCcyPairAcc As Object 'Stores accumulated amounts of ccy pairs
42
Dim state As SystemState 'Runtime optimisation - see class SystemState
43
44
Set state = New SystemState 'Runtime optimisation - see class SystemState
45
Set oCcyPairAcc = CreateObject("Scripting.Dictionary")
46
47
vColors = Array(xlCIRed, xlCIBlue, xlCIBrown, xlCIDarkGreen, _
48
xlCIDarkYellow, xlCIOrange, xlCIDarkTeal, xlCIPlum)
49
lColorIdx = 0
50
Sheets("CurrencyPairs").Select 'Sheet to work on
51
lRow = 3 'Start row
52
Do While Not IsEmpty(Cells(lRow, ts_ccy))
53
If lRow Mod 10 = 0 Then Application.StatusBar = _
54
"Calculate_Accumulated_Blotter: Processing row " & lRow & " ..."
55
Cells(lRow, ts_output_ccy) = Cells(lRow, ts_ccy)
56
If oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text & "|FontColor") = 0# Then
57
oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text & "|FontColor") = _
58
vColors(lColorIdx)
59
lColorIdx = lColorIdx + 1
60
If lColorIdx > UBound(vColors) Then
61
Call MsgBox("Row " & lRow & ": Not enough colors defined", _
62
vbOKOnly, "Error")
63
Exit Sub
64
End If
65
End If
66
Select Case Cells(lRow, ts_bought_sold)
67
Case "Bought"
68
dSign = 1#
69
Case "Sold"
70
dSign = -1#
71
Case Else
72
Call MsgBox("Row " & lRow & ": Illegal Bought/Sold Keyword """ & _
73
Cells(lRow, ts_bought_sold) & """ in column " & ts_bought_sold, _
74
vbOKOnly, "Error")
75
Exit Sub 'Stop at first error
76
End Select
77
dSum = oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text) + _
78
dSign * Cells(lRow, ts_amount)
79
Select Case Sgn(dSum)
80
Case 1#
81
If dSign = 1# Then
82
Cells(lRow, ts_output_long_short) = "Enter long"
83
Else
84
Cells(lRow, ts_output_long_short) = "Exit long"
85
End If
86
oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text & "|EUR") = _
87
oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text & "|EUR") + _
88
dSign * Cells(lRow, ts_fxrate) * Cells(lRow, ts_traded_value)
89
Case 0#
90
If dSign = 1# Then
91
Cells(lRow, ts_output_long_short) = "Exit short - flat"
92
Else
93
Cells(lRow, ts_output_long_short) = "Exit long - flat"
94
End If
95
oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text & "|EUR") = 0#
96
Case -1#
97
If dSign = 1# Then
98
Cells(lRow, ts_output_long_short) = "Exit short"
99
Else
100
Cells(lRow, ts_output_long_short) = "Enter short"
101
End If
102
oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text & "|EUR") = _
103
oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text & "|EUR") + _
104
dSign * Cells(lRow, ts_fxrate) * Cells(lRow, ts_traded_value)
105
End Select
106
Cells(lRow, ts_output_amount_accumulated) = Abs(dSum)
107
oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text) = dSum
108
Cells(lRow, ts_output_traded_value_EUR) = Abs(oCcyPairAcc.Item(Cells(lRow, _
109
ts_ccy).Text & "|EUR"))
110
Range(Cells(lRow, ts_output_ccy), Cells(lRow, _
111
ts_output_traded_value_EUR)).Font.ColorIndex = _
112
oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text & "|FontColor")
113
lRow = lRow + 1
114
Loop
115
Set oCcyPairAcc = Nothing
116
End Sub
Copied!
sbAccumulatedTradeBlotter.xlsm
62KB
Binary
sbAccumulatedTradeBlotter.xlsm
Last modified 1yr ago
Copy link