use web service inside Pivot table calculated measure

[dadarara]

I have a data table with Stock trades.
I also have a pivot that shows all currently open stock positions.

I would like to have a calculated field in the pivot that will retrieve the current stock quote from Yahoo and calculate the current value of a position.
I know how to get the stock quotes into excel. I need the data populated in my above pivot table automatically.
I was thinking about creating a "pivot measure" that will include a web service.

Can I do that? or can I call an external VBA function from within the measure ?

a side note: I have the pivot with the open positions working. I have defined the excel cells next to the pivot with the web service formula.
but whenever the pivot is updated (after refresh) the cells next tot he pivot are not updated and keep the old data.
nothing I do resolves the issue.

Thanks for help

 

[Roger Govier]

Hi

You say that the cells external to your PT are producing the required result, but just not updating.
Any cells outside of the Pivot Table will not refresh as part of any update of the Pivot Cache.
Perhaps you could use the Pivot Update event to refresh your external cells

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
' your code to refresh your range of cells


End Sub