Add Dynamic calculated item to a pivot table

rbnaik · Mar 8, 2021

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&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.

mart37 · Mar 9, 2021

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")

mart37 · Mar 9, 2021

Sorry, wrong answer.

Declare your variables:
Dim last As Date
Dim secondlast As Date

rbnaik · Mar 9, 2021

Thanks mart37, but it is still giving run-time error 1004

rbnaik · Mar 10, 2021

I did not get what you mean to say..

mart37 · Mar 10, 2021

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.

rbnaik · Mar 10, 2021

mart37 said:
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 ?

mart37 said:
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.

mart37 · Mar 11, 2021

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)

rbnaik · Mar 11, 2021

mart37 said:
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.

mart37 · Mar 11, 2021

VBA Code:

    ActiveSheet.PivotTables("Draaitabel1").PivotFields("Lupdate").CalculatedItems. _
    Add "Diff", "='" & Format(last, "h:mm:ss") & "'-'" & Format(secondlast, "h:mm:ss") & "'", True

)

Add Dynamic calculated item to a pivot table

rbnaik

New Member

Excel Facts

mart37

Well-known Member

mart37

Well-known Member

rbnaik

New Member

rbnaik

New Member

mart37

Well-known Member

rbnaik

New Member

mart37

Well-known Member

rbnaik

New Member

Attachments

mart37

Well-known Member

Similar threads

Forum statistics

Share this page

Add Dynamic calculated item to a pivot table

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Attachments

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock