Sub CalcE_Ratio()

    Dim strValueToDelete As String

    If Range("O2").Value & "" = "" Then
        
        'The Text to Columns has not been done - do it
        Columns("C:E").Select
        Selection.Insert Shift:=xlToRight
        Columns("B:B").Select
        Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
            :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
            TrailingMinusNumbers:=True
        strValueToDelete = ""
    
    Else
            
        'The Text to Columns has been done: just shift columns around
        Range("C1:N1").Cut Destination:=Range("F1:Q1")
        strValueToDelete = "Market"
         
    End If

    'Add headers
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "length"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "trade length"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "ATR"
    
    'Sort the data
    Columns("A:Q").Select
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("F2") _
        , Order2:=xlAscending, Key3:=Range("G2"), Order3:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
    
    Dim iCol As Long
    iCol = 0
    
    'Remove duplicate header rows
    For i = 1 To 66000
        If Range("C" & i).Value & "" = strValueToDelete Then
            Rows(i & ":" & i).Clear
            If iCol = 0 Then iCol = i - 1 'Record last row of data for later
        End If
        If Range("A" & i + 1).Value & "" = "" Then
            Exit For
        End If
    Next
    
    'Add new headers for calculated values
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "price/ppt"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "MFE"
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "MAE"
    Range("U1").Select
    ActiveCell.FormulaR1C1 = "MFE norm"
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "MAE norm"
    
    'Calculate new values
    Range("R2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-4]<>0,ABS((RC[-5]-RC[-9])/RC[-4]),R[-1]C)"
    Range("S2").Select
    ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-1]"
    Range("T2").Select
    ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-2]"
    Range("U2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-16]"
    Range("V2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-17]"

    'Fill formulas down
    Range("R2:V2").Select
    Selection.AutoFill Destination:=Range("R2:V" & iCol)
    
    
    
    'THE PIVOT TABLE
    Dim strSheetName As String
    strSheetName = ActiveSheet.Name
    
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
         strSheetName & "!R1C1:R" & iCol & "C22").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable" & strSheetName, DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable" & strSheetName).PivotFields("length")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable" & strSheetName).PivotFields("trade length")
        .Orientation = xlRowField
        .Position = 2
    End With
    Range("A4").Select
    ActiveSheet.PivotTables("PivotTable" & strSheetName).AddDataField ActiveSheet.PivotTables( _
        "PivotTable" & strSheetName).PivotFields("MFE norm"), "Sum of MFE norm", xlSum
    ActiveSheet.PivotTables("PivotTable" & strSheetName).AddDataField ActiveSheet.PivotTables( _
        "PivotTable" & strSheetName).PivotFields("MAE norm"), "Sum of MAE norm", xlSum
    ActiveWorkbook.ShowPivotTableFieldList = False
        
    'Calculate the E_RATIO
    
    Range("E3").Value = "e-ratio"
    Range("F3").Value = "Trade Duration"
    
    Range("E4").FormulaR1C1 = _
        "=IF(RC[-3],GETPIVOTDATA(""Sum of MFE norm"",R3C1,""length"",R4C1,""trade length"",RC[-3])/GETPIVOTDATA(""Sum of MAE norm"",R3C1,""length"",R4C1,""trade length"",RC[-3]),"""")"
    Range("E4").Select
    Selection.AutoFill Destination:=Range("E4:E500")
    
    Range("F4").FormulaR1C1 = "=IF(RC[-4],RC[-4],"""")"
    Range("F4").Select
    Selection.AutoFill Destination:=Range("F4:F500")


End Sub