Option Explicit
    
Enum trade_sheet_columns
    ts_ccy = 1
    ts_bought_sold
    ts_order_id
    ts_trade_date
    ts_value_date
    ts_amount
    ts_price
    ts_fxrate
    ts_traded_value
    ts_traded_value_EUR
    ts_EMPTY
    ts_output_ccy
    ts_output_long_short
    ts_output_amount_accumulated
    ts_output_traded_value_EUR
End Enum 'trade_sheet_columns

Private Enum xlCI 'Excel Color Index
: xlCIBlack = 1: xlCIWhite: xlCIRed: xlCIBrightGreen: xlCIBlue '1 - 5
: xlCIYellow: xlCIPink: xlCITurquoise: xlCIDarkRed: xlCIGreen '6 - 10
: xlCIDarkBlue: xlCIDarkYellow: xlCIViolet: xlCITeal: xlCIGray25 '11 - 15
: xlCIGray50: xlCIPeriwinkle: xlCIPlum: xlCIIvory: xlCILightTurquoise '16 - 20
: xlCIDarkPurple: xlCICoral: xlCIOceanBlue: xlCIIceBlue: xlCILightBrown '21 - 25
: xlCIMagenta2: xlCIYellow2: xlCICyan2: xlCIDarkPink: xlCIDarkBrown '26 - 30
: xlCIDarkTurquoise: xlCISeaBlue: xlCISkyBlue: xlCILightTurquoise2: xlCILightGreen '31 - 35
: xlCILightYellow: xlCIPaleBlue: xlCIRose: xlCILavender: xlCITan '36 - 40
: xlCILightBlue: xlCIAqua: xlCILime: xlCIGold: xlCILightOrange '41 - 45
: xlCIOrange: xlCIBlueGray: xlCIGray40: xlCIDarkTeal: xlCISeaGreen '46 - 50
: xlCIDarkGreen: xlCIGreenBrown: xlCIBrown: xlCIDarkPink2: xlCIIndigo '51 - 55
: xlCIGray80 '56
End Enum

Sub Calculate_Accumulated_Blotter()
'Source (EN): http://www.sulprobil.de/sbaccumulatedtradeblotter_en/
'Source (DE): http://www.berndplumhoff.de/sbaccumulatedtradeblotter_de/
'(C) (P) by Bernd Plumhoff 15-Jan-2011 PB V0.1
Dim lRow As Long
Dim lColorIdx As Long
Dim dSign As Double
Dim dSum As Double
Dim vColors As Variant
Dim oCcyPairAcc As Object   'Stores accumulated amounts of ccy pairs
Dim state As SystemState    'Runtime optimisation - see class SystemState

Set state = New SystemState 'Runtime optimisation - see class SystemState
Set oCcyPairAcc = CreateObject("Scripting.Dictionary")

vColors = Array(xlCIRed, xlCIBlue, xlCIBrown, xlCIDarkGreen, _
                xlCIDarkYellow, xlCIOrange, xlCIDarkTeal, xlCIPlum)
lColorIdx = 0
Sheets("CurrencyPairs").Select       'Sheet to work on
lRow = 3                    'Start row
Do While Not IsEmpty(Cells(lRow, ts_ccy))
    If lRow Mod 10 = 0 Then Application.StatusBar = _
        "Calculate_Accumulated_Blotter: Processing row " & lRow & " ..."
    Cells(lRow, ts_output_ccy) = Cells(lRow, ts_ccy)
    If oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text & "|FontColor") = 0# Then
        oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text & "|FontColor") = _
            vColors(lColorIdx)
        lColorIdx = lColorIdx + 1
        If lColorIdx > UBound(vColors) Then
            Call MsgBox("Row " & lRow & ": Not enough colors defined", _
                vbOKOnly, "Error")
            Exit Sub
        End If
    End If
    Select Case Cells(lRow, ts_bought_sold)
    Case "Bought"
        dSign = 1#
    Case "Sold"
        dSign = -1#
    Case Else
        Call MsgBox("Row " & lRow & ": Illegal Bought/Sold Keyword """ & _
            Cells(lRow, ts_bought_sold) & """ in column " & ts_bought_sold, _
            vbOKOnly, "Error")
        Exit Sub             'Stop at first error
    End Select
    dSum = oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text) + _
           dSign * Cells(lRow, ts_amount)
    Select Case Sgn(dSum)
    Case 1#
        If dSign = 1# Then
            Cells(lRow, ts_output_long_short) = "Enter long"
        Else
            Cells(lRow, ts_output_long_short) = "Exit long"
        End If
        oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text & "|EUR") = _
            oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text & "|EUR") + _
            dSign * Cells(lRow, ts_fxrate) * Cells(lRow, ts_traded_value)
    Case 0#
        If dSign = 1# Then
            Cells(lRow, ts_output_long_short) = "Exit short - flat"
        Else
            Cells(lRow, ts_output_long_short) = "Exit long - flat"
        End If
        oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text & "|EUR") = 0#
    Case -1#
        If dSign = 1# Then
            Cells(lRow, ts_output_long_short) = "Exit short"
        Else
            Cells(lRow, ts_output_long_short) = "Enter short"
        End If
        oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text & "|EUR") = _
            oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text & "|EUR") + _
            dSign * Cells(lRow, ts_fxrate) * Cells(lRow, ts_traded_value)
    End Select
    Cells(lRow, ts_output_amount_accumulated) = Abs(dSum)
    oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text) = dSum
    Cells(lRow, ts_output_traded_value_EUR) = Abs(oCcyPairAcc.Item(Cells(lRow, _
                                              ts_ccy).Text & "|EUR"))
    Range(Cells(lRow, ts_output_ccy), Cells(lRow, _
        ts_output_traded_value_EUR)).Font.ColorIndex = _
        oCcyPairAcc.Item(Cells(lRow, ts_ccy).Text & "|FontColor")
    lRow = lRow + 1
Loop
Set oCcyPairAcc = Nothing
End Sub