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 VBA
Since 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 SubNow 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%"
)
sankeyPlotBlogger 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.
