The PIMCO All Asset funds are managed by Research Affiliates Robert Arnott and invest only in other PIMCO mutual funds rather than individual securities, so in many ways the fund’s success is dependent on the quality and breadth of other PIMCO funds. Since it sounds slightly incestuous, I thought it would be a good subject for my new tools—sankey diagrams and basic network analysis.
Excel to Edgelist with Old-school VBASince PIMCO (and nobody else for that matter) doesn’t provide holdings in an igraph readable edgelist, I decided to revive some fond memories and use VBA to convert the spreadsheet into a 3 column edgelist (source, target, value). Even though I am embarrassed by my VBA code, I will show it below.
Sub getedgelist() Dim clInfo As Range, clWrite As Range, tempcl As Range Dim offsetamt As Integer 'this will be used throughout to indicate columns to offset for desired date 'watch out for hidden rows offsetamt = 15 'clInfo will be the cell with our reference information Set clInfo = ThisWorkbook.ActiveSheet.Range("a9") 'clWrite will be the cell where we write the info that we gather Set clWrite = ThisWorkbook.ActiveSheet.Range("u9") Do Until clInfo = "Total Gross Asset Allocation" 'looking at formatting is probably the easiest way to determine if group or fund 'I chose indent to determine if group or fund 'IndentLevel = 0 for groups and 1 for funds If clInfo.IndentLevel = 0 Then 'if we are on a group write in clWrite the group and each fund within the group 'with group as source and fund as target 'we will loop until we are at the next group Set tempcl = clInfo 'write mutual fund as source, group as target, and weight clWrite.Value = ThisWorkbook.ActiveSheet.Range("e6").Value clWrite.Offset(0, 1).Value = clInfo.Value clWrite.Offset(0, 2).Value = clInfo.Offset(0, offsetamt).Value Set clWrite = clWrite.Offset(1, 0) Do Until tempcl.Offset(1, 0).IndentLevel = 0 'now loop through each fund in group 'write group as source, held fund as target, and weight clWrite.Value = clInfo.Value clWrite.Offset(0, 1).Value = tempcl.Offset(1, 0).Value clWrite.Offset(0, 2).Value = tempcl.Offset(1, offsetamt).Value 'next cell down Set tempcl = tempcl.Offset(1, 0) Set clWrite = clWrite.Offset(1, 0) Loop Set clInfo = clInfo.Offset(1, 0) Else End If 'if we are on a group write in clWrite the group and each fund within the group 'with group as source and fund as target Debug.Print (clInfo) Set clInfo = clInfo.Offset(1, 0) Loop End Sub
Now we have everything we need to do the sankey diagram in R.
#sankey of PIMCO All Asset All Authority holdings #data source http://investments.pimco.com/ShareholderCommunications/External%20Documents/PIMCO%20Bond%20Stats.xls require(rCharts) #originally read the data from clipboard of Excel copy #for those interested here is how to do it #read.delim(file = "clipboard") holdings = read.delim("http://timelyportfolio.github.io/rCharts_d3_sankey/holdings.txt", skip = 3, header = FALSE, stringsAsFactors = FALSE) colnames(holdings) <- c("source","target","value") #get rid of holdings with 0 weight or since copy/paste from Excel - holdings <- holdings[-which(holdings$value == "-"),] holdings$value <- as.numeric(holdings$value) #now we finally have the data in the form we need sankeyPlot <- rCharts$new() sankeyPlot$setLib('http://timelyportfolio.github.io/rCharts_d3_sankey') sankeyPlot$set( data = holdings, nodeWidth = 15, nodePadding = 10, layout = 32, width = 750, height = 500, labelFormat = ".1%" ) sankeyPlot
Blogger makes it hard to incorporate d3 directly into this post, so click here or on the screenshot below, to engage and interact with the sankey.
Just for good measure, here is the default plot from igraph.
Of course we could also plot the data a little more traditionally with a ggplot2 bar chart.
Any way we look at it, we can see that PIMCO now has way more than just bonds and the fund All Asset All Authority uses almost everything.