Being from a finance background, I made it a point to invest in SIPs early on. Over the years, while the investment amount has increased steadily, the number of funds being invested in has remained more or less constant. Hence, I need not emphasis how important it is to know where exactly the money is going.
Too often, the choice of a fund is made simply on returns and diversification is achieved by selecting a different fund class. However, it provides no indication of the extent of value creation. I prefer to keep an eye out on what’s happening with my portfolio and it is not only when selecting a new fund but also for keeping tabs on what’s going on with the existing investments.
My search for websites/files providing this information yielded a few options that were quite limited in nature, dispensing basic overlap information between two or three funds. Unable to find the requisite information, I decided to go on my own and create an Excel workbook that provides overlap analysis for up to six funds. The other target I had set for myself was to do so without the use of VBA, so the only permission required is to access the external data source – moneycontrol.com.
The workbook is structured in to distinct sheets for input and detailed analysis. The ‘Input’ sheet is pretty straightforward and is essentially a two-step process requiring the funds and investment amount to be entered along with the selection of the fund that would form the basis of checking the overlap. It would be a good idea to read through the notes prior to using the workbook. The sheet has some safeguards built in to alert the user about inconsistent inputs, like missing investment values/funds and failure to refresh the ‘base fund’ selection. At the same time, it is robust enough to still function immaculately when any of the selected funds are deleted.
Note that although the sheet includes funds with equity holdings from various classes, some of them do not have their holdings listed on moneycontrol.com which may cause an error illustrated above. As such, there is nothing that can be done about it. Also, to state the obvious, the default funds selected in the sheet are for illustration and are not suggestive.
The ‘Analysis’ sheet provides the primary analysis of the portfolio. Besides listing the fund class and the equity holdings of each fund, it provides the percentage overlap of the base fund with all the other funds in the portfolio, both, in terms of the number of stocks and the value invested. The charts in turn provide ‘Top 10’ visualisations for individual stocks as well as the different sectors.
The ‘Detail’ sheet provides the tabular information that form the basis of the analysis and lists all the values as against only the Top 10 in the charts.
The ‘MFx’ sheets list the holdings of each fund, as retrieved from moneycontrol.com and is subsequently used for the overlap calculations.
Finally, the ‘List’ sheet is a list of the funds retrieved from moneycontrol.com and covers the various equity fund classes. It is easy to add any new funds to the list in the specified format and the information can be scraped en masse from the MoneyControl site.
As is often the case, I have created something to primarily fulfil my needs but with the intention of sharing it with other netizens. Consequently, I am open to any suggestions for improvement which you may leave in the comments section.
I'm getting "Initialization of the data source failed"
Can you please guide me how to get rid of this error.
Hey, sorry for the late reply. The error may possibly result from an older version of Excel since it may not support all the Power Query features of Excel 2016.
Alternately, even on Excel 2016, the error occurs when there is an issue with the Microsoft Power Pivot for Excel Add-in. You can remove and re-install this add-in using File > Options > Add-ins and then selecting 'COM Add-ins' in the Manage section at the bottom of the window.