Add Dynamic calculated item to a pivot table

rbnaik

New Member
Joined
Nov 20, 2010
Messages
27
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.
F&OTickerNameLTPChange% ChangeVol '000Total Traded ValueLupdate
ABFRLPP1Aditya Birla Fashion Partly paid I
178​
2.65​
1.51​
6.62​
0.12​
10:35 AM​
FINEORGFine Organics
2388.4​
22.75​
0.96​
5.59​
1.34​
10:35 AM​
AAVASAAVAS
2313.8​
3.2​
0.14​
5.72​
1.32​
10:35 AM​
ABBABB India Ltd
1518.8​
6​
0.4​
14.1​
2.14​
10:35 AM​
ABBOTINDIAAbbott India
14808​
118.65​
0.81​
2.6​
3.85​
10:35 AM​
*ACCACC
1883.95​
30.25​
1.63​
1139.18​
213.63​
10:35 AM​
*ADANIENTAdani Enterprises
922.8​
4.4​
0.48​
2236.37​
206.91​
10:35 AM​
*ADANIPORTSAdani Ports & Special Economic Zone
753.6​
1.15​
0.15​
5475.78​
413.97​
10:35 AM​
ADANIPOWERAdani Power
74.9​
3.55​
4.98​
8672.66​
64.26​
10:35 AM​
ADANITRANSAdani Transmissions
839.5​
5.25​
0.63​
204.59​
17.24​
10:35 AM​
AFFLEAffle (India)
6245​
240.95​
4.01​
51​
31.76​
10:35 AM​
AIAENGAIA Engineering
1911.25​
12.35​
0.65​
25.27​
4.84​
10:35 AM​
ALKYLAMINEAlkyl Amines Chemicals
5188.4​
37.25​
0.72​
11.95​
6.17​
10:35 AM​
ALOKINDSAlok Industries
22.1​
0.15​
0.68​
4392.3​
9.75​
10:35 AM​
AMBERAmber Enterprises
3605.05​
126.45​
3.64​
134.36​
48.11​
10:35 AM​
*AMBUJACEMAmbuja Cements
295.6​
3.6​
1.23​
7133.12​
210.8​
10:35 AM​
APLAPOLLOAPL Apollo Tubes
1320.3​
6.45​
0.49​
263.72​
34.82​
10:35 AM​
APLLTDAlembic Pharmaceuticals
923.9​
1.2​
0.13​
42.52​
3.93​
10:35 AM​
ASAHIINDIAAsahi India Glass
324.2​
4.2​
1.31​
28.27​
0.91​
10:35 AM​
ASTERDMASTDMHEACAR
142.8​
0.45​
0.32​
26.54​
0.38​
10:35 AM​
ATGLAdani Total Gas
717​
28.25​
4.1​
1208.91​
86.07​
10:35 AM​
AUBANKAU Small Finance Bank
1273​
5​
0.39​
429.57​
54.71​
10:35 AM​
*AXISBANKAxis Bank
739.5​
2.7​
0.37​
6103.88​
447.88​
10:35 AM​
F&OTickerNameLTPChange% ChangeVol '000Total Traded ValueLupdate
AAVASAAVAS
2316.8​
6.2​
0.27​
6.62​
1.53​
10:52 AM​
ABBABB India Ltd
1514.5​
1.7​
0.11​
17.52​
2.66​
10:52 AM​
ABBOTINDIAAbbott India
14800​
110.65​
0.75​
2.93​
4.34​
10:52 AM​
ABFRLPP1Aditya Birla Fashion Partly paid I
177.9​
2.55​
1.45​
7.27​
0.13​
10:52 AM​
*ACCACC
1864.95​
11.25​
0.61​
1285.73​
241.03​
10:52 AM​
ADANIPOWERAdani Power
74.9​
3.55​
4.98​
8766.53​
64.96​
10:52 AM​
ADANITRANSAdani Transmissions
839​
4.75​
0.57​
217.49​
18.32​
10:52 AM​
AFFLEAffle (India)
6165.25​
161.2​
2.68​
59.79​
37.19​
10:52 AM​
AIAENGAIA Engineering
1918.7​
19.8​
1.04​
27.7​
5.31​
10:52 AM​
ALKYLAMINEAlkyl Amines Chemicals
5187.05​
35.9​
0.7​
12.9​
6.66​
10:52 AM​
AMBERAmber Enterprises
3578​
99.4​
2.86​
139.89​
50.1​
10:52 AM​
*AMBUJACEMAmbuja Cements
293.55​
1.55​
0.53​
8100.15​
239.21​
10:52 AM​
APLAPOLLOAPL Apollo Tubes
1320.25​
6.4​
0.49​
278.25​
36.74​
10:52 AM​
ASAHIINDIAAsahi India Glass
323.5​
3.5​
1.09​
29.36​
0.94​
10:52 AM​
ASTERDMASTDMHEACAR
142.85​
0.5​
0.35​
28.43​
0.41​
10:52 AM​
ATGLAdani Total Gas
712.55​
23.8​
3.46​
1278.64​
91.04​
10:52 AM​
AUBANKAU Small Finance Bank
1276.2​
8.2​
0.65​
465.51​
59.29​
10:52 AM​
F&OTickerNameLTPChange% ChangeVol '000Total Traded ValueLupdate
ABBOTINDIAAbbott India
14789​
99.65​
0.68​
3.19​
4.72​
11:15 AM​
ABFRLPP1Aditya Birla Fashion Partly paid I
177​
1.65​
0.94​
10.03​
0.18​
11:15 AM​
*ACCACC
1863.4​
9.7​
0.52​
1463.72​
274.11​
11:15 AM​
ADANIPOWERAdani Power
74.9​
3.55​
4.98​
8962.64​
66.42​
11:15 AM​
AFFLEAffle (India)
6099.9​
95.85​
1.6​
74.22​
46.01​
11:15 AM​
AIAENGAIA Engineering
1914​
15.1​
0.8​
30.61​
5.87​
11:15 AM​
ALKYLAMINEAlkyl Amines Chemicals
5159.95​
8.8​
0.17​
14.06​
7.26​
11:15 AM​
AMBERAmber Enterprises
3577.9​
99.3​
2.85​
148.42​
53.14​
11:15 AM​
APLAPOLLOAPL Apollo Tubes
1320​
6.15​
0.47​
290.84​
38.4​
11:15 AM​
ASAHIINDIAAsahi India Glass
321​
1​
0.31​
34.76​
1.12​
11:15 AM​
ASTERDMASTDMHEACAR
142.55​
0.2​
0.14​
29.35​
0.42​
11:15 AM​
ATGLAdani Total Gas
707.9​
19.15​
2.78​
1566.59​
111.3​
11:15 AM​
AUBANKAU Small Finance Bank
1272.65​
4.65​
0.37​
503.54​
64.12​
11:15 AM​
F&OTickerNameLTPChange% ChangeVol '000Total Traded ValueLupdate
ABBOTINDIAAbbott India
14791​
101.6​
0.69​
3.25​
4.8​
11:25 AM​
ABFRLPP1Aditya Birla Fashion Partly paid I
177​
1.65​
0.94​
11.24​
0.2​
11:25 AM​
*ACCACC
1862​
8.3​
0.45​
1516.02​
283.87​
11:25 AM​
ADANIPOWERAdani Power
74.9​
3.55​
4.98​
8996.11​
66.68​
11:25 AM​
AIAENGAIA Engineering
1907​
8.1​
0.43​
32.71​
6.27​
11:25 AM​
AMBERAmber Enterprises
3558.3​
79.7​
2.29​
152.09​
54.45​
11:25 AM​
APLAPOLLOAPL Apollo Tubes
1320​
6.15​
0.47​
294.45​
38.88​
11:25 AM​
ASTERDMASTDMHEACAR
142.65​
0.3​
0.21​
35.02​
0.5​
11:25 AM​
ATGLAdani Total Gas
702.5​
13.75​
2​
1611.21​
114.44​
11:25 AM​
AUBANKAU Small Finance Bank
1272.1​
4.1​
0.32​
517.87​
65.95​
11:25 AM​
AVANTIFEEDAvanti Feeds
493.15​
0.75​
0.15​
68.53​
3.39​
11:25 AM​
F&OTickerNameLTPChange% ChangeVol '000Total Traded ValueLupdate
AAVASAAVAS
2311.95​
1.35​
0.06​
9.89​
2.28​
12:13 PM​
ABBOTINDIAAbbott India
14800​
110.65​
0.75​
5.76​
8.52​
12:13 PM​
ABFRLPP1Aditya Birla Fashion Partly paid I
178​
2.65​
1.51​
14.88​
0.26​
12:13 PM​
*ACCACC
1857​
3.3​
0.18​
1636.66​
306.36​
12:13 PM​
ADANIPOWERAdani Power
74.9​
3.55​
4.98​
9192.24​
68.14​
12:13 PM​
AIAENGAIA Engineering
1914.95​
16.05​
0.85​
39.28​
7.53​
12:13 PM​
ALKYLAMINEAlkyl Amines Chemicals
5153.6​
2.45​
0.05​
15.03​
7.76​
12:13 PM​
AMBERAmber Enterprises
3575.35​
96.75​
2.78​
193.02​
69.05​
12:13 PM​
APLAPOLLOAPL Apollo Tubes
1320​
6.15​
0.47​
319.91​
42.24​
12:13 PM​
ASAHIINDIAAsahi India Glass
321.5​
1.5​
0.47​
40.32​
1.3​
12:13 PM​
ASTERDMASTDMHEACAR
142.5​
0.15​
0.11​
45.47​
0.65​
12:13 PM​
ATGLAdani Total Gas
695​
6.25​
0.91​
1830.17​
129.74​
12:13 PM​
AUBANKAU Small Finance Bank
1275.95​
7.95​
0.63​
619.8​
78.95​
12:13 PM​
*AXISBANKAxis 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 LTPLupdate
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
convert the times to text:
last = Format(DateAdd("s", last * 60 * 24 * 60, "00:00:00"), "HH:mm:ss")
secondlast = Format(DateAdd("s", secondlast * 60 * 24 * 60, "00:00:00"), "HH:mm:ss")
 
Upvote 0
Sorry, wrong answer.

Declare your variables:
Dim last As Date
Dim secondlast As Date
 
Upvote 0
Thanks mart37, but it is still giving run-time error 1004
1615349258921.png
 
Upvote 0
It works by me fine. You have to delete the calculated item before you run the macro again. When not you get also error 1004: application or object error

The right answer is: The last date or second last date searched on Gainer Pivot doesn't exists in your pivot table on the activesheet.
 
Upvote 0
Hi Mart37,
I tried to run the macro after rebooting of computer, but still getting the same error.. Maybe something else needs to be changed ?
It works by me fine. You have to delete the calculated item before you run the macro again. When not you get also error 1004: application or object error

The right answer is: The last date or second last date searched on Gainer Pivot doesn't exists in your pivot table on the activesheet.
 
Upvote 0
The last date or second last date searched on Gainer Pivot doesn't exists in your pivot table on the activesheet.
Did you check this?
Check of last and secondlast is an item in your pivot table.
I think you can beter search the last and secondlast dates in your pivot table on the active sheet.
VBA Code:
last = Application.WorksheetFunction.Large(ActiveSheet.Range("4:4"), 1)
secondlast = Application.WorksheetFunction.Large(ActiveSheet.Range("4:4"), 2)
 
Upvote 0
The last date or second last date searched on Gainer Pivot doesn't exists in your pivot table on the activesheet.
Did you check this?
Check of last and secondlast is an item in your pivot table.
I think you can beter search the last and secondlast dates in your pivot table on the active sheet.
VBA Code:
last = Application.WorksheetFunction.Large(ActiveSheet.Range("4:4"), 1)
secondlast = Application.WorksheetFunction.Large(ActiveSheet.Range("4:4"), 2)
Yes, the line no 4 contains the column name. Please find attached screenshot. I debugged the last and secondlast and they are fetching values.
 

Attachments

  • Pivot1.jpg
    Pivot1.jpg
    63.8 KB · Views: 10
  • l&sl.png
    l&sl.png
    5.2 KB · Views: 12
Upvote 0
VBA Code:
    ActiveSheet.PivotTables("Draaitabel1").PivotFields("Lupdate").CalculatedItems. _
    Add "Diff", "='" & Format(last, "h:mm:ss") & "'-'" & Format(secondlast, "h:mm:ss") & "'", True

)
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top