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.
Very nice post!ReplyDelete
Hi, this was a great and very informative post. I do have one question: I am probably missing something very obvious, but where does one get the file "chart.html" that you reference in this line of code?ReplyDelete
sankeyPlot$setTemplate(script = "layouts/chart.html")
thanks, honored that you likedReplyDelete
Actually, rCharts has made this even easier now. Install the newest dev branch:ReplyDelete
install_github('rCharts', 'ramnathv', ref = "dev")
Then use the code you see in http://rcharts.io/viewer/?6022406. Let me know if this does not work.
Thanks! That totally worked. Very helpfulReplyDelete
Thank you for the great demo! It has been really helpful. I'm an R user with no experience with D3. My question is: is there a way to make the bands different colors? I'm thinking along the lines of:ReplyDelete
Thanks again for the help and I look forward to hearing from you.