9
$\begingroup$

I would like to compute market-value weighted stock prices grouped by sector from a Dataset but can't figure out how to apply WeightedData when using GroupBy.

Get prices:

prices = FinancialData[{"AAPL", "GOOG", "JPM", "C"}]
{$205.53, $1171.02, $106.80, $62.36}

Create dataset:

port = Dataset[{
   <|"Sector" -> "Tech", "Ticker" -> "AAPL", "Shares" -> 1000, "Price" -> prices[[1]]|>,
   <|"Sector" -> "Tech", "Ticker" -> "GOOG", "Shares" -> 100, "Price" -> prices[[2]]|>,
   <|"Sector" -> "Fin", "Ticker" -> "JPM", "Shares" -> 400, "Price" -> prices[[3]]|>,
   <|"Sector" -> "Fin", "Ticker" -> "C", "Shares" -> 1000, "Price" -> prices[[4]]|>
   }]

Add market-value to dataset:

port = port[All, Append[#, "MarketValue" -> #Price/100.0 * #Shares] &]

Weight all prices by market-value (goal is to apply this at group level):

wp = WeightedData[Normal[port[All, "Price"]], Normal[port[All, "MarketValue"]]]

Compute weighted mean:

Mean[wp]
$439.13

Compute mean by group:

port[GroupBy["Sector"], Mean, "Price"]

enter image description here

So, how can one compute weighted averages at the group level?

$\endgroup$
  • $\begingroup$ Does everyone with Mathematica have access to FinancialData or is it a separate service? I keep getting "Internet download failed..." $\endgroup$ – alancalvitti Aug 29 '19 at 16:54
  • $\begingroup$ @alancalvitti afaik, yes. I have been using the Home edition of Mathematica since version 10.0 and have always had access to FinancialData. The documentation for version 12.0 indicates it was introduced in version 6.0 (2007). $\endgroup$ – Lee Aug 29 '19 at 20:12
6
$\begingroup$
weightedmean = Mean @* Apply[WeightedData] @* Transpose 

port[GroupBy["Sector"],  weightedmean,  {#Price, #MarketValue}&]

enter image description here

port[weightedmean, {#Price, #MarketValue}&]

Quantity[439.1334211424316, "USDollars"]

Note: You can also define weightedmean using (easier to read?) RightComposition:

weightedmean = Transpose /* Apply[WeightedData] /* Mean
$\endgroup$
  • $\begingroup$ I see, @* is Composition and /* is RightComposition. Initially my preference was for Composition, as I tend to read Dataset operations from right to left, but after some experimentation the RightComposition approach seems easier to read, imo. $\endgroup$ – Lee Aug 30 '19 at 22:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.