Musing #58: Mutual Fund (SIP) Portfolio Overlap Analyser



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.

Link: Download from Google Drive

Musing #54: Impact of standard deduction in FY2019



The introduction of standard deduction in FY2019 lieu of transport allowance and medical reimbursement is unanimously portrayed as being beneficial to employees, at least by HR personnel. To a large extent that is true, even though the mention of the additional 1% education cess is conveniently omitted.

However, I had a query from a lesser financially inclined colleague of mine about the extent of benefit  that this change brings about. Considering my past example of helping out colleagues and my interest in economics and taxation, I decided to get cracking on preparing a spreadsheet capturing the difference in taxation in FY2019 over the past financial year based on these specific changes. It is a simplistic model created in a few minutes for the purpose of understanding. It considers the taxable income to be the income post all exemptions apart from the ones that are part of the subject matter.

As can be seen by the graph, benefit is a subjective consideration but it is helps to be an optimist. The crossover happens at ₹12,64,000, so I assume a large proportion of the population would be happy about the change, especially if they couldn't produce the necessary medical bills in the past years for reimbursement. The maximum benefit of ₹1070 is, of course, at a taxable income of ₹5,40,000 which would have been in the 20% bracket in FY2018 compared to the 5% bracket in FY2019.

The spreadsheet, formatted for Google Sheets can be accessed here.

Musing #45: Creating a visual dashboard using Qlik Sense


Lately, I have been in to Qlik Sense and was looking to create a single page dashboard for publicly available information. The most logical place to head to was Kaggle and keeping in line with my previous post concerning US-India, I decided to go with the H1-B Visa Petition data set.

Tutorial #17: A batter(!) understanding of dosa economics


Raghuram Rajan has been featuring a lot in the media recently in promotion of his book 'I Do What I Do'. While I am yet to read it, there has been no escaping it as select excerpts and anecdotes have been making their way to the news every day now. Earlier today it was the turn of 'Dosa Economics' on BBC.

On the face of it, it is a simple concept of understanding the real interest rate as against the nominal one. Most people tend to look at interest rate in absolute terms since it is the most visible one and inflation as the silent killer is rarely understood. It was a noble attempt by Rajan at explaining this concept, though how many pensioners received the message even after the simplification of numbers is debatable.

However, I see no reason for Raghuram Rajan to have a monopoly on dosas in economics. Moreover, one would be hard pressed to find a dosa for ₹50 in a city like Mumbai, let alone 1-year fixed deposits at 8% and real-life consumer inflation at 5.5%. So, now you get to create your own realistic dosa economics, provided you have the appetite for it.

Open in new tab

Tutorial #15: How to get official licenses (Windows/Office) for cheap


There was a time when piracy was considered to be a necessity. The unavailability of the software locally along with dollar pricing made it impossible for anyone to even contemplate purchasing the software. However, things have changed a lot since then. Local availability along with local pricing has made these products far more accessible.

But, and the big but, is affordability. The pricing is certainly competitive from a commercial perspective but personal users would still find the price prohibitive, especially when the usage is limited to writing personal documents and filing income tax returns. Piracy can't be condoned, so what other valid options are available?

By valid, I refer to the ability to download and register the software using official sources. I remember getting a Windows 8 license for less than $10 during its launch due to a Microsoft promotion and I wish they were generally generous in their pricing in developing countries. However, I presume Internet anonymity has made it difficult to separate the wheat from the chaff.

Hence, it becomes necessary to take recourse to other options. One of the most prominent ones is the Microsoft Software Swap Marketplace on Reddit, though there are other forums available. The prices are certainly competitive compared to retail pricing but still on the expensive side for those not dealing with USD as local currency. Hence, the best option is to head to good old ebay.com. The price fluctuates from seller to seller and availability is entirely dependent on timing. However, if you are in luck, then the prices range in low single digits, as far as USD is concerned. Local credit cards might not work directly with ebay.com but PayPal comes to the rescue. Do keep in mind to use your bank conversion since the fees are usually much less than PayPal's, the premium mostly being less than 5% depending on the size of the transaction. The proclamation is that these codes have been salvaged from scrapped machines and hence it is legitimate to resell the same. What I can confirm is that the codes work fine with office.com and are instantly redeemed along with the download links for any Microsoft account. Similarly, Windows activates just fine with the supplied key, if used with a fresh installation.

If you prefer Office 365 instead, then there are Educational subscriptions available that offer multiple year access for about the same price. This one certainly feels a bit dodgier because you are restricted to an academic email address being governed by administrators. However, it offers multiple installations and 1TB of SkyDrive space, though it is difficult to trust an address you are not entirely in control of.

Whatever be the case, there are certainly legitimate options available that if nothing else help protect from options that are untrustworthy and laden with malware, at a significantly affordable price.

Musing #38: The case of the mismatching VP count


While the US President has managed to hog all the limelight just about everyday in office, the Indian President was ushered in a more laid-back affair in the past month which is just as well considering the fact that is largely a ceremonial post. However, it was the ushering in of the 13th Vice-President yesterday, as against the 14th President earlier that got me to delve in to the history of these posts and the resulting mismatch in their count.

While Wikipedia has a well structured list of Presidents and Vice-Presidents, it is difficult to follow the changes for both the posts in sequence. Hence, I decided to chronologise the same and have presented it in the table below.

Interestingly, only twice has the Vice-President been ushered in with the President on the same day, which can be considered to be an oddity of sorts, though perfectly justifiable considering the circumstances and the duration of the posts.

Date of taking office Pres/VP Count President Taking Office VP Taking Office
26-Jan-1950
1/0
Rajendra Prasad
-
13-May-1952
1/1
-
Sarvepalli Radhakrishnan
13-May-1962
2/2
Sarvepalli Radhakrishnan
Zakir Husain
13-May-1967
3/3
Zakir Husain
Varahagiri Venkata Giri
03-May-1969
3/3
Varahagiri Venkata Giri
-
20-Jul-1969
3/3
Mohammad Hidayatullah
-
24-Aug-1969
4/3
Varahagiri Venkata Giri
-
31-Aug-1969
4/4
-
Gopal Swarup Pathak
24-Aug-1974
5/4
Fakhruddin Ali Ahmed
-
31-Aug-1974
5/5
-
Basappa Danappa Jatti
11-Feb-1977
5/5
Basappa Danappa Jatti
-
25-Jul-1977
6/5
Neelam Sanjiva Reddy
-
31-Aug-1979
6/6
-
Mohammad Hidayatullah
25-Jul-1982
7/6
Giani Zail Singh
-
31-Aug-1984
7/7
-
Ramaswamy Venkataraman
25-Jul-1987
8/7
Ramaswamy Venkataraman
-
03-Sep-1987
8/8
-
Shankar Dayal Sharma
25-Jul-1992
9/8
Shankar Dayal Sharma
-
21-Aug-1992
9/9
-
Kocheril Raman Narayanan
25-Jul-1997
10/9
Kocheril Raman Narayanan
-
21-Aug-1997
10/10
-
Krishan Kant
25-Jul-2002
11/10
A. P. J. Abdul Kalam
-
19-Aug-2002
11/11
-
Bhairon Singh Shekhawat
25-Jul-2007
12/11
Pratibha Patil
-
11-Aug-2007
12/12
-
Mohammad Hamid Ansari
25-Jul-2012
13/12
Pranab Mukherjee
-
25-Jul-2017
14/12
Ram Nath Kovind
-
11-Aug-2017
14/13
-
Muppavarapu Venkaiah Naidu

Tutorial #12: Converting calendar (CY) dates to fiscal year (FY) and quarter


I had a recent request from a colleague who wanted to arrange a bunch of dates in to fiscal quarters. So, I went about creating an Excel workbook  that would implement the same with the freedom of selecting the starting month of the fiscal year and decided to post it here along the lines of my previous tutorial. The following steps explain the logic behind my implementation.

Note that the steps below refer to the formula in cell C5. Hence B5 refers to the input date whereas the fiscal start month is captured in the cell C2.

Step 1: Input for FY Start Month

The 'FY Start Month" is the only input to this sheet and enables adaptation to any fiscal year. To prevent errors, I used Data Validation to limit the inputs to whole numbers ranging from 1 to 12.


Step 2: Calculating Fiscal Year

To calculate the fiscal year I used the simple logic wherein if the month of the date is equal to or greater than the fiscal starting month then the fiscal year is incremented by one compared to the calendar year or else it remains the same.
IF(MONTH(B5)-$C$2>=0,YEAR(B5)+1,YEAR(B5))
This works for all scenarios apart from when the fiscal year is same as the calendar year since in that case we have to create an exception where the fiscal year is same as the calendar year. This is done with the help of the additional IF statement.
IF(MONTH(B5)-$C$2>=0,IF($C$2=1,YEAR(B5),YEAR(B5)+1),YEAR(B5))
Step 3: Calculating Fiscal Quarter

To identify the quarter, I decided to go with the CHOOSE function which makes it imperative that the calendar months are rearranged to fiscal months.

The difference in the numerical value between the calendar month and the fiscal month can range from -11 (1 minus 12) to +11 (12 minus 1). Hence the logic below offsets the value such that it lies between 1 to 12. This is done by adding 13 whenever the difference is negative and adding 1 whenever the difference is positive.
CHOOSE(IF(MONTH(B5)-$C$2<0,13+MONTH(B5)-$C$2,1+MONTH(B5)-$C$2),"Q1","Q1","Q1","Q2","Q2","Q2","Q3","Q3","Q3","Q4","Q4","Q4")
Step 4: Combination of fiscal quarter and year

The final step is to join the two formulae with the fiscal quarter leading the fiscal year with suitable spacing.
CHOOSE(IF(MONTH(B5)-$C$2<0,13+MONTH(B5)-$C$2,1+MONTH(B5)-$C$2),"Q1","Q1","Q1","Q2","Q2","Q2","Q3","Q3","Q3","Q4","Q4","Q4")
&" "&
IF(MONTH(B5)-$C$2>=0,IF($C$2=1,YEAR(B5),YEAR(B5)+1),YEAR(B5))
Since Google Sheets supports the same semantics as Microsoft Excel in this case, you can access the same using this link.