Calculated Field in a Pivot table not working

[jojojo123]

Hello,

Please see my code below:


I'm trying to divide my traded volume by the market volume. I think the below code should do this, but some of the answers it produces are incorrect. Has anyone got any idea why this might be please?

Thanks
Jo

    ' Define Calculated Fields
    PT.CalculatedFields.Add Name:="% of market vol", Formula:="=Executed Quantity/volume"

    ' Set up the data fields
    With PT.PivotFields("Executed Quantity")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
        .Name = "Aspect Executed Volume"
        .NumberFormat = "#,##0_ ;[Red]-#,##0 "
    End With
    With PT.PivotFields("volume")
        .Orientation = xlDataField
        .Function = xlAverage
        .Position = 2
        .Name = "Market Volume"
        .NumberFormat = "#,##0_ ;[Red]-#,##0 "
    End With
    With PT.PivotFields("% of market vol")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 3
        .NumberFormat = "0.00%"
        .Name = "Aspect % of Market Vol"

 

[RoryA]

but some of the answers it produces are incorrect.

Can you elaborate on this?

At a guess, your problem is the way calculated fields work. What that formula calculates is:
=SUM(Executed Quantity)/SUM(volume)

and not:
=SUM(Executed Quantity/volume)

 

[jojojo123]

You're absolutely right, Rory, thank you. I had just found the answer on another post (apologies for duping.)

Thanks
Jo

 

[jojojo123]

Ok, so i changed the formula to

=SUM(Executed Quantity)/SUM(volume)

and it worked yesterday afternon. It worked again this afternoon but the spreadsheet crashed a couple of times (i think due to me overloading it with dragging data in from Bloomberg). But it has now stopped working.

I've tried:
=SUM(Executed Quantity/volume)

aswell - nothing works though.

I can't understand why sometimes it wokrs and sometimes not. I found another post about something similar - ther theory there was that using 'count' was causing rounding errors - mine doesn't do that though.

ANy ideas much appreciated - it's extremely frustrating!

Thanks
Jo

 

[RoryA]

You don't need to put SUM in - a calculated field will do that anyway. You do need to be more clear as to what exactly "not working" means.

 

[jojojo123]

Sorry.


If I could upload a snip it'd be much easier to explain.





My pivot table generates a table that has in:


column 1: Contract;


column 2, Executed Volume;


column 3: Market Volume;


column 4: % of Market Volume





In column 4 the calculation (in the code) is Executed Volume/Market Volume, shown as a percentage.





The example I have is:


Executed Volume: 4


Market Volume: 3814


% of market volume: 0.05% (when (4/3814)*100)=0.10%

 

[RoryA]

Looking at your code, your Volume field is an average in the pivot table, but the calculation won't use average - it will sum all items in that field.

 

[jojojo123]

So even though my executed volume field (4) and market volume (3814) are showing the correct values, you're saying that the calculation that's happening is: (4/(3814+3814)*100)?

The underlying data is shown across two lines with 2 lots in executed volume in each line and 3814 as market volume in each line (as pulled in from Bloomberg). So that would fit with your explanation.

Any ideas how to get around that and force the calculation not to use SUM?

Thanks
Jo

 

[RoryA]

That's correct. You can't alter the way calculated fields behave unfortunately. If you can use PowerPivot, you can create three simple measures to achieve the result you want but otherwise you need to do a calculation in the source data to divide the volume by the number of relevant rows. Such a calculation unfortunately has to be specific to the grouping you have in your pivot table.

 

[jojojo123]

Thanks very much. You've clarified a lot and saved me much time googling how to fix the calculated field! I have done as you suggest and it works

Many thanks again,

Jo