Hi,
This forum has been very helpful to me from the beginning. Thanks all for all your support.
I have an excel sheet where data gets added every two minutes alongwith timeline. Following is the sample shot of data.
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:
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
Any help will be much appriciated. I am using office 2019.
This forum has been very helpful to me from the beginning. Thanks all for all your support.
I have an excel sheet where data gets added every two minutes alongwith timeline. Following is the sample shot of data.
| F&O | Ticker | Name | LTP | Change | % Change | Vol '000 | Total Traded Value | Lupdate |
| ABFRLPP1 | Aditya Birla Fashion Partly paid I | 178 | 2.65 | 1.51 | 6.62 | 0.12 | 10:35 AM | |
| FINEORG | Fine Organics | 2388.4 | 22.75 | 0.96 | 5.59 | 1.34 | 10:35 AM | |
| AAVAS | AAVAS | 2313.8 | 3.2 | 0.14 | 5.72 | 1.32 | 10:35 AM | |
| ABB | ABB India Ltd | 1518.8 | 6 | 0.4 | 14.1 | 2.14 | 10:35 AM | |
| ABBOTINDIA | Abbott India | 14808 | 118.65 | 0.81 | 2.6 | 3.85 | 10:35 AM | |
| * | ACC | ACC | 1883.95 | 30.25 | 1.63 | 1139.18 | 213.63 | 10:35 AM |
| * | ADANIENT | Adani Enterprises | 922.8 | 4.4 | 0.48 | 2236.37 | 206.91 | 10:35 AM |
| * | ADANIPORTS | Adani Ports & Special Economic Zone | 753.6 | 1.15 | 0.15 | 5475.78 | 413.97 | 10:35 AM |
| ADANIPOWER | Adani Power | 74.9 | 3.55 | 4.98 | 8672.66 | 64.26 | 10:35 AM | |
| ADANITRANS | Adani Transmissions | 839.5 | 5.25 | 0.63 | 204.59 | 17.24 | 10:35 AM | |
| AFFLE | Affle (India) | 6245 | 240.95 | 4.01 | 51 | 31.76 | 10:35 AM | |
| AIAENG | AIA Engineering | 1911.25 | 12.35 | 0.65 | 25.27 | 4.84 | 10:35 AM | |
| ALKYLAMINE | Alkyl Amines Chemicals | 5188.4 | 37.25 | 0.72 | 11.95 | 6.17 | 10:35 AM | |
| ALOKINDS | Alok Industries | 22.1 | 0.15 | 0.68 | 4392.3 | 9.75 | 10:35 AM | |
| AMBER | Amber Enterprises | 3605.05 | 126.45 | 3.64 | 134.36 | 48.11 | 10:35 AM | |
| * | AMBUJACEM | Ambuja Cements | 295.6 | 3.6 | 1.23 | 7133.12 | 210.8 | 10:35 AM |
| APLAPOLLO | APL Apollo Tubes | 1320.3 | 6.45 | 0.49 | 263.72 | 34.82 | 10:35 AM | |
| APLLTD | Alembic Pharmaceuticals | 923.9 | 1.2 | 0.13 | 42.52 | 3.93 | 10:35 AM | |
| ASAHIINDIA | Asahi India Glass | 324.2 | 4.2 | 1.31 | 28.27 | 0.91 | 10:35 AM | |
| ASTERDM | ASTDMHEACAR | 142.8 | 0.45 | 0.32 | 26.54 | 0.38 | 10:35 AM | |
| ATGL | Adani Total Gas | 717 | 28.25 | 4.1 | 1208.91 | 86.07 | 10:35 AM | |
| AUBANK | AU Small Finance Bank | 1273 | 5 | 0.39 | 429.57 | 54.71 | 10:35 AM | |
| * | AXISBANK | Axis Bank | 739.5 | 2.7 | 0.37 | 6103.88 | 447.88 | 10:35 AM |
| F&O | Ticker | Name | LTP | Change | % Change | Vol '000 | Total Traded Value | Lupdate |
| AAVAS | AAVAS | 2316.8 | 6.2 | 0.27 | 6.62 | 1.53 | 10:52 AM | |
| ABB | ABB India Ltd | 1514.5 | 1.7 | 0.11 | 17.52 | 2.66 | 10:52 AM | |
| ABBOTINDIA | Abbott India | 14800 | 110.65 | 0.75 | 2.93 | 4.34 | 10:52 AM | |
| ABFRLPP1 | Aditya Birla Fashion Partly paid I | 177.9 | 2.55 | 1.45 | 7.27 | 0.13 | 10:52 AM | |
| * | ACC | ACC | 1864.95 | 11.25 | 0.61 | 1285.73 | 241.03 | 10:52 AM |
| ADANIPOWER | Adani Power | 74.9 | 3.55 | 4.98 | 8766.53 | 64.96 | 10:52 AM | |
| ADANITRANS | Adani Transmissions | 839 | 4.75 | 0.57 | 217.49 | 18.32 | 10:52 AM | |
| AFFLE | Affle (India) | 6165.25 | 161.2 | 2.68 | 59.79 | 37.19 | 10:52 AM | |
| AIAENG | AIA Engineering | 1918.7 | 19.8 | 1.04 | 27.7 | 5.31 | 10:52 AM | |
| ALKYLAMINE | Alkyl Amines Chemicals | 5187.05 | 35.9 | 0.7 | 12.9 | 6.66 | 10:52 AM | |
| AMBER | Amber Enterprises | 3578 | 99.4 | 2.86 | 139.89 | 50.1 | 10:52 AM | |
| * | AMBUJACEM | Ambuja Cements | 293.55 | 1.55 | 0.53 | 8100.15 | 239.21 | 10:52 AM |
| APLAPOLLO | APL Apollo Tubes | 1320.25 | 6.4 | 0.49 | 278.25 | 36.74 | 10:52 AM | |
| ASAHIINDIA | Asahi India Glass | 323.5 | 3.5 | 1.09 | 29.36 | 0.94 | 10:52 AM | |
| ASTERDM | ASTDMHEACAR | 142.85 | 0.5 | 0.35 | 28.43 | 0.41 | 10:52 AM | |
| ATGL | Adani Total Gas | 712.55 | 23.8 | 3.46 | 1278.64 | 91.04 | 10:52 AM | |
| AUBANK | AU Small Finance Bank | 1276.2 | 8.2 | 0.65 | 465.51 | 59.29 | 10:52 AM | |
| F&O | Ticker | Name | LTP | Change | % Change | Vol '000 | Total Traded Value | Lupdate |
| ABBOTINDIA | Abbott India | 14789 | 99.65 | 0.68 | 3.19 | 4.72 | 11:15 AM | |
| ABFRLPP1 | Aditya Birla Fashion Partly paid I | 177 | 1.65 | 0.94 | 10.03 | 0.18 | 11:15 AM | |
| * | ACC | ACC | 1863.4 | 9.7 | 0.52 | 1463.72 | 274.11 | 11:15 AM |
| ADANIPOWER | Adani Power | 74.9 | 3.55 | 4.98 | 8962.64 | 66.42 | 11:15 AM | |
| AFFLE | Affle (India) | 6099.9 | 95.85 | 1.6 | 74.22 | 46.01 | 11:15 AM | |
| AIAENG | AIA Engineering | 1914 | 15.1 | 0.8 | 30.61 | 5.87 | 11:15 AM | |
| ALKYLAMINE | Alkyl Amines Chemicals | 5159.95 | 8.8 | 0.17 | 14.06 | 7.26 | 11:15 AM | |
| AMBER | Amber Enterprises | 3577.9 | 99.3 | 2.85 | 148.42 | 53.14 | 11:15 AM | |
| APLAPOLLO | APL Apollo Tubes | 1320 | 6.15 | 0.47 | 290.84 | 38.4 | 11:15 AM | |
| ASAHIINDIA | Asahi India Glass | 321 | 1 | 0.31 | 34.76 | 1.12 | 11:15 AM | |
| ASTERDM | ASTDMHEACAR | 142.55 | 0.2 | 0.14 | 29.35 | 0.42 | 11:15 AM | |
| ATGL | Adani Total Gas | 707.9 | 19.15 | 2.78 | 1566.59 | 111.3 | 11:15 AM | |
| AUBANK | AU Small Finance Bank | 1272.65 | 4.65 | 0.37 | 503.54 | 64.12 | 11:15 AM | |
| F&O | Ticker | Name | LTP | Change | % Change | Vol '000 | Total Traded Value | Lupdate |
| ABBOTINDIA | Abbott India | 14791 | 101.6 | 0.69 | 3.25 | 4.8 | 11:25 AM | |
| ABFRLPP1 | Aditya Birla Fashion Partly paid I | 177 | 1.65 | 0.94 | 11.24 | 0.2 | 11:25 AM | |
| * | ACC | ACC | 1862 | 8.3 | 0.45 | 1516.02 | 283.87 | 11:25 AM |
| ADANIPOWER | Adani Power | 74.9 | 3.55 | 4.98 | 8996.11 | 66.68 | 11:25 AM | |
| AIAENG | AIA Engineering | 1907 | 8.1 | 0.43 | 32.71 | 6.27 | 11:25 AM | |
| AMBER | Amber Enterprises | 3558.3 | 79.7 | 2.29 | 152.09 | 54.45 | 11:25 AM | |
| APLAPOLLO | APL Apollo Tubes | 1320 | 6.15 | 0.47 | 294.45 | 38.88 | 11:25 AM | |
| ASTERDM | ASTDMHEACAR | 142.65 | 0.3 | 0.21 | 35.02 | 0.5 | 11:25 AM | |
| ATGL | Adani Total Gas | 702.5 | 13.75 | 2 | 1611.21 | 114.44 | 11:25 AM | |
| AUBANK | AU Small Finance Bank | 1272.1 | 4.1 | 0.32 | 517.87 | 65.95 | 11:25 AM | |
| AVANTIFEED | Avanti Feeds | 493.15 | 0.75 | 0.15 | 68.53 | 3.39 | 11:25 AM | |
| F&O | Ticker | Name | LTP | Change | % Change | Vol '000 | Total Traded Value | Lupdate |
| AAVAS | AAVAS | 2311.95 | 1.35 | 0.06 | 9.89 | 2.28 | 12:13 PM | |
| ABBOTINDIA | Abbott India | 14800 | 110.65 | 0.75 | 5.76 | 8.52 | 12:13 PM | |
| ABFRLPP1 | Aditya Birla Fashion Partly paid I | 178 | 2.65 | 1.51 | 14.88 | 0.26 | 12:13 PM | |
| * | ACC | ACC | 1857 | 3.3 | 0.18 | 1636.66 | 306.36 | 12:13 PM |
| ADANIPOWER | Adani Power | 74.9 | 3.55 | 4.98 | 9192.24 | 68.14 | 12:13 PM | |
| AIAENG | AIA Engineering | 1914.95 | 16.05 | 0.85 | 39.28 | 7.53 | 12:13 PM | |
| ALKYLAMINE | Alkyl Amines Chemicals | 5153.6 | 2.45 | 0.05 | 15.03 | 7.76 | 12:13 PM | |
| AMBER | Amber Enterprises | 3575.35 | 96.75 | 2.78 | 193.02 | 69.05 | 12:13 PM | |
| APLAPOLLO | APL Apollo Tubes | 1320 | 6.15 | 0.47 | 319.91 | 42.24 | 12:13 PM | |
| ASAHIINDIA | Asahi India Glass | 321.5 | 1.5 | 0.47 | 40.32 | 1.3 | 12:13 PM | |
| ASTERDM | ASTDMHEACAR | 142.5 | 0.15 | 0.11 | 45.47 | 0.65 | 12:13 PM | |
| ATGL | Adani Total Gas | 695 | 6.25 | 0.91 | 1830.17 | 129.74 | 12:13 PM | |
| AUBANK | AU Small Finance Bank | 1275.95 | 7.95 | 0.63 | 619.8 | 78.95 | 12:13 PM | |
| * | AXISBANK | Axis Bank | 737.2 | 0.4 | 0.05 | 8915.73 | 654.82 | 12:13 PM |
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:
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
Any help will be much appriciated. I am using office 2019.
