have an excel sheet where data gets added every two minutes alongwith timeline. I have attached sample file of data and pivot table.
And here is the pivot table generated out of it
I am trying to refresh pivot table which gives me price difference for a stock between last and second last time when data is updated (Both these values will be dynamic), so that I can track progress and trade according to the direction of market.
However, I am unable to add this dynamic field to my pivot table. Following is the code I am trying to modify to suit my need.
' '*************** Perfectly working example if I calculate manually
' ActiveSheet.PivotTables("PivotTable2").PivotFields("Lupdate").CalculatedItems. _
' Add "Diff", "='12:13:00' -'11:25:00'", True
'
' ActiveSheet.PivotTables("PivotTable2").PivotFields("Lupdate").CalculatedItems. _
' Add "Diff %", "=IFERROR(('12:13:00' -'11:25:00' )/'12:13:00',0)", True
' '******************* Upto this line
Complete subroutine is as follows:
>>> use code - tags <<<
Any help will be much appreciated. I am using office 2019.
And here is the pivot table generated out of it
| F&O | * | ||||
| Sum of LTP | Lupdate | ||||
| Name | 10:35:00 | 10:52:00 | 11:15:00 | 11:25:00 | 12:13:00 |
| ACC | 1,883.95 | 1,864.95 | 1,863.40 | 1,862.00 | 1,857.00 |
| Adani Enterprises | 922.80 | ||||
| Ambuja Cements | 295.60 | 293.55 | |||
| Axis Bank | 739.50 | 737.20 | |||
| Adani Ports & Special Economic Zone | 753.60 |
I am trying to refresh pivot table which gives me price difference for a stock between last and second last time when data is updated (Both these values will be dynamic), so that I can track progress and trade according to the direction of market.
However, I am unable to add this dynamic field to my pivot table. Following is the code I am trying to modify to suit my need.
' '*************** Perfectly working example if I calculate manually
' ActiveSheet.PivotTables("PivotTable2").PivotFields("Lupdate").CalculatedItems. _
' Add "Diff", "='12:13:00' -'11:25:00'", True
'
' ActiveSheet.PivotTables("PivotTable2").PivotFields("Lupdate").CalculatedItems. _
' Add "Diff %", "=IFERROR(('12:13:00' -'11:25:00' )/'12:13:00',0)", True
' '******************* Upto this line
Complete subroutine is as follows:
>>> use code - tags <<<
Code:
Sub Macro1()
'
' Macro1 Macro
'
' Add calculated items (not fields)
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Gainer Pivot")
'Store last and second last updated time for calculation
last = Application.WorksheetFunction.Large(ws.Range("4:4"), 1)
secondlast = Application.WorksheetFunction.Large(ws.Range("4:4"), 2)
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
'MsgBox last & " " & secondlast
ActiveSheet.PivotTables("PivotTable2").PivotFields("Lupdate").CalculatedItems. _
Add "Diff", "='" & last & "' -'" & secondlast & "'", True
End Sub
Last edited by a moderator: