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 -

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 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 and is subsequently used for the overlap calculations.

Finally, the 'List' sheet is a list of the funds retrieved from 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

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 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 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 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.

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.
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.
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.
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.
&" "&
Since Google Sheets supports the same semantics as Microsoft Excel in this case, you can access the same using this link.

Sundry #4: Excel Karaoke Player

My previous post on creating karaoke tracks got me thinking of creating a karaoke player and what better tool to use than the most interesting member of the Office family. I have used it mostly for boring purposes and a quite a few interesting ones which I shall unfortunately never be able to share.

Hence, this one is a fully open, quick and dirty take on fulfilling an idea. I usually like to focus on design and error handling when creating anything new, but I have given those a miss here, thereby following an engineer's ethos of "if it's working, it's done". It worked fine for me with the Groove Music player on Windows 10 along with a lyrics file (*.lrc) that I found on the web. I have posted this lyrics file (with some clean-up and synchronization) along with the Excel file, so you can feel free to try it out with the same track (not supplied, har har har) or indeed with any other.

The logic it follows is quite simple.
1. Locally store the MP3 path
2. Copy the contents of the lyrics file to the 'LRC' sheet
3. Extract the timing and lyrics from the contents (using only minutes and seconds here, so the player is not millisecond accurate)
4. On clicking of 'Start', open the MP3 file and start a counter using VBA that tracks the progress
5. Highlight the lyrics using conditional formatting by comparing the track progress with the timing of that specific line

If you like it or indeed re-use it, then just leave me a High Five.

1. Excel Karaoke Player     
2. Sample Lyrics File

Tutorial #6: PowerPoint as an image editor (and meme generator!)

Photoshop may be the de-facto tool and term when referring to image editing, but not everyone has access to it or can even use it. There are plenty of free tools and even online editors available that can do a basic job of editing the image. However, as is the case with the phone camera, the best tool is the one that you have on hand. To that end, PowerPoint is an ubiquitous tool that is present on most office PCs as well as home ones with the advent of Office 365. Hence, this is a primer on utilizing PowerPoint as an on-hand image editor to isolate parts of an image .

Step 1: Click on the image and select 'Remove Background' from the Format tab.

Step 2: Select the area which you want to isolate from the rest of the picture. The pink region depicts the part of the image that will be discarded while the slide preview indicates how the image will look like.

Step 3: Use the 'Mark Areas to Keep' option to draw lines across the areas which  you wish to retain.

Step 4: Similarly, use the 'Mark Areas to Remove' option to select the areas which you wish to    remove.

Step 5: With the combination of the 'Keep' and 'Remove' options you can get the outline of the part of the image that you want. Thereafter, you can use the Corrections, Picture Styles, Picture Effects as required to obtain a desired look. At least in my case, I was able to generate a fairly representative picture of the web from a random image I found on it (just kidding!).

 Finally, what's PowerPoint without pointless animations.

Poetry #6: The Big Leap

Curse the day that leaps at me
For it makes me work for free

It may just be the earth's quirk
But got to hate the boss' smirk

One in four it may be
Why does it even bother me?

Isn't this just another Monday?
Or is it special by some way?

Time alone doesn't make something noteworthy
Unless it is really praiseworthy

Opportunity rarely knocks on the door
If all one is doing is rolling on the floor

So for what remains of the day
It's carpe diem all the way

Bruised and battered I may be
But never the battlefield shall I flee

For it's time to really dig deep
And nonchalantly take the big leap
Where ought this leap lead to in 2020?

P.S.: Also written as my first Sway

Tutorial #2: Replace a specific string in Word using wildcards

A recent task I had to perform was to remove a list of item numbers from a document while keeping the item name intact. The syntax was <ITMxxx – Item Name> which was required to be changed to only <Item Name>. The task seemed straight forward up front but the tricky part was that the spacing around the hyphen was inconsistent and so was the usage of a dash instead of a hyphen. The various forms taken by this element were:

<ITM123-Item Name>
<ITM345 -Item Name>
<ITM546- Item Name>
<ITM729 - Item Name>
<ITM189 – Item Name>

The idea was to replace all of them at one go while ensuring that nothing else was inadvertently replaced. Thus, the use of only the common wildcard character ‘?’ wouldn't have sufficed. Fortunately, Word provides a very powerful set of wildcards detailed at the following link:

To illustrate my case, the following strings in ‘Find What’ and ‘Replace With’ did the trick:
Find what: (ITM[0-9]{3}*[-–]*)(<*>)
Replace with: \2

[0-9] captures the presence of any number while {3} ensures that the search is limited to 3 occurrences of these digits thereby capturing the ‘ITMxxx’ part.

The part *[-–]* captures the presence of space preceding or following the hyphen or dash.

The round brackets () represent a complete search expression. Thus, in this case the search is split in to two expressions, the first being the entire text leading up to the ‘Item Name’ and the second being the first word of the item name. The Replace With string ensures that these two expressions are replaced by the second expression alone which happens to be the first word of the item name. The rest of the text is kept as-is thereby producing the requisite result.

Tutorial #1: Dealing with duplicates in Excel

A common occurrence in Excel is the presence of duplicate values within a column which makes it difficult to further utilise the data. Depending on the need, following are some of the quick ways to deal with duplicate values.

A. Using conditional formatting

1. Highlight duplicate values
  • Select the range of cells within which you wish to identify the duplicates
  • From the Home tab, choose Conditional Formatting > Highlight Cells Rules > Duplicate Values. Click OK within the Duplicate Values dialog box
The duplicate values in the list will now be highlighted in red.

2. List duplicate values

After the above step, select right click on a cell within the range and choose Filter > Filter by Selected Cell's Color. This will give the list of unique values which can then be further copy-pasted in another sheet.

Note: Make sure you have a column header in place as the filter will be applied on the first row. So, if the first row happens to be a unique value, the filter will be applied on it assuming it to be the header. This may throw you off guard if you are not totally aware of it.

3. List non-repeating values

Just reverse the selection in Step 2 when applying the filter i.e. select a 'No Fill' cell. This will list the values that are not repeated at all.

Note: This doesn't list unique values as the ones which are repeated are omitted.

B. Using built-in Excel functionality

1. List unique values

The simplest method to list unique values is to use the 'Remove Duplicates' option under the 'Data Tools' group of the Data tab. Make sure you properly utilise the 'My data has headers' selection before clicking on OK in the Remove Duplicates dialog box.

C. Using an array formula

A scalable way of doing this with fewer clicks (or none at all) is to use an array formula. Array formulae are a lot more convoluted to understand, so I may try to do a break-up of the formula along with an illustration in the future, but for now copy-paste of the below should suffice. Note that array formulae are entered using Ctrl + Shift + Enter and hence are also called 'CSE' formulae.

Here, I will assume that the values with duplicates are present in Column A and labelled as 'List1' (Ctrl + F3). Also, the formula will be entered in the cell B2.

1. List duplicate values
{=IFERROR(INDEX(List1, MATCH(0, COUNTIF($B$1:$B1,List1)+IF(COUNTIF(List1,List1)>1, 0, 1), 0)),"")}

2. List unique values 
{=IFERROR(INDEX(List1, MATCH(0, COUNTIF($B$1:$B1,List1)+IF(COUNTIF(List1,List1)>=1, 0, 1), 0)),"")}
Note: The only change here is to use ">=" instead of ">" within the IF statement condition check.

All my Excel tips are based on the latest version (Excel 2016 at the time of writing) as I am an Office 365 subscriber. The tips can be extended to the immediate preceding versions like Excel 2013 and 2010 but may differ in earlier versions.