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.

Sundry #8: Around the year in 80 posts

The first anniversary of this website is still a week away and this happens to be the 81st post, but it was too good an opportunity to pass in terms of word play. I am pretty sure that Jules Verne has stopped turning in his grave a long time back. I would have pretty much liked to hit the 3-figure mark in the first year but writing on this blog ends up taking the back seat ever so often, even as ideas continue to flow. I am afraid that even matching this number might be a tall task in the second year, though I hope to make up in quality for what might lack in quantity.

The numeric labelling of each post gives the complete picture in terms of how the posts are divided across the various categories, but here is a visualization of the same. Apparently, I am more of a critic and a philosopher.

 

I have already lamented the fact that I don't write as often as I can and the distribution of the posts over the year indicates how inconsistent I can be in putting my thoughts down. It is pretty much a function of free time.


Lastly, it is time for me to revisit the Word Cloud. Last time it was generated from the RSS feed which limited the extent of posts considered. However, this time I have gone the extra mile in extracting all the text and hence it should provide a better representation. Apparently, I need to be less possessive.


P.S. Around the sun in infinite ways?

Review #28: Private Internet Access (PIA) VPN


Hello 2017! The first full weekend of 2017 gives me an opportunity to put my thoughts to words once again. Considering that security and privacy were a highlight in 2016, I thought it best to start 2017 with a review of my current VPN service provider.

I have been subscribed to VPN services for over half a decade and have been constantly switching service providers every year without fail. The primary reason has always been the immense loss of speed I have been subjected to when using OpenVPN, especially when using servers at a great distance from my country (India). The other "not too unsubstantial" reason is the pricing which is always in USD and comes at a significant cost when your local currency happens to be INR. However, things have improved on both these fronts over the years. Server choices have improved over time and there are great discounts on offer during Black Friday. This means that I am now able to connect to more servers at a cost lower than what I paid 5 years ago.

While my trend of switching providers continues with my current choice, this is the first time I have returned to a service that I have used previously. PIA has always been a popular choice but my experience first time around was not great. The speeds were not that great and there were no local servers available. However, the speed test on their site came back with impressive numbers and the Black Friday offer of $29.95 for a year's subscription seemed too good to pass. The default data encryption offered on all clients is AES-128 in CBC mode, though it can be stepped up to AES-256 if security is more of a priority over speed. Since I value speed a lot more when using the VPN, I have till now stuck with AES-128. But as you will see, the results of my tests have now made me change my mind. PIA is also known to not keep any logs and has a good track record as far as privacy is concerned.

For my testing, I focussed on speeds alone since I have taken the security for granted on the basis of the claims and feedback linked to in the previous paragraph. The testing procedure went as follows:
  1. I used the speedtest.net website or app with the same server (based on geographical proximity to my direct connection) for the speed tests
  2. I used the official PIA apps wherever possible. However, at the time of this writing, the PIA app failed to connect to any servers on iOS 10.2 and hence I used the OpenVPN Connect app as an alternative.
  3. I made it a point to select all the countries listed by PIA when using the official apps. In case of multiple servers in a country (US, UK, Canada), I selected the one which seemed geographically closer. For the router test, I used the configurations already stored on it since switching servers is more time consuming.
The point of running it across multiple devices was not to compare them, but to observe the consistency of the performance, especially as I ran the test on each platform at different times and on different servers, but on the same 10 Mbps fixed line network. As you can very well see, the performance is anything but consistent. It gives an idea of the variabilty in speed that one can expect when using a server. Also, the high pings make it incapable of being used in a time sensitive setting. True to expectations, it is the locally situated server that comes close to providing a good experience, though the French server seems to be a good alternative. On the other end of the spectrum, the server in New Zealand is of no utility at all.


Next, I decided to run the tests on a faster 4G LTE cellular network that usually hangs around the 20 Mbps mark. The higher speed compared to my fixed line was bound to provide greater fidelity in the data. It ended up rejigging the speed order once again but is another indication of the inconsistency and limited server choices available if speed is the main criteria.


By default, all the PIA clients and configuration files are set to operate with AES-128 data encryption in CBC Mode. Apparently, the stronger encryption sacrifices speed for better security. However, this time, contrary to expectations as well as suggestions made by PIA, it was the stronger encryption that yielded better speed results throughout.


I have been using the Asus AC68U router for well over a year and it is simply faultless with the AsusWRT Merlin firmware. PIA also has a dedicated guide for it which makes it a breeze to set it up. The setup is for AES-128 in CBC mode and while there doesn't seem to be a separate guide for setting the router up with AES-256 in CBC Mode, it wasn't too difficult. Based on the settings mentioned on their site, I was able to get the stronger encryption working by changing the 'Encryption cipher' value to 'AES-256-CBC', adding the 4096-bit Root CA and including the following in 'Custom Configuration' from the .opvn file.

persist-key
persist-tun
auth sha256
comp-lzo
verb 1
reneg-sec 0
disable-occ
However, router hardware is more limited and running OpenVPN on it is taxing to say the least, especially at higher encryption levels. At the same time, it is essential as it allows dumber devices like Smart TVs and Chromecasts to bypass geographical restrictions and keeps the device count in check. I also have L2TP configurations saved for the same locations in case OpenVPN proves a bridge too far. The results yet again indicate that the strongest encryption mode is also the fastest. This comes at a higher processing cost for the router but it is nothing that will choke it up.


To sum it up, the performance with PIA is literally a mixed bag. If privacy is the driving factor, then using the closest geographical server 24x7 seems to be a good bet as the speed difference is not too significant, though the latency is higher by a factor of 10. Bypassing geographical restrictions for browsing shouldn't be much of a problem as France, Germany, Finland, Sweden offer good speeds. Video streaming from US is not much of an option, though it is possible to stream just fine from UK. On a 10 Mbps line, it is just about possible to watch HD streams with infrequent buffering. But PIA servers are already blocked across geographies as far as Netflix and Amazon are concerned. iPlayer still seems to work but tends to throw out errors every now and then.

On the positive side, it is easy to setup, works well on all the platforms that I use (Linux included) and the speed is good. I have interacted with their customer service and they are well informed while also being quick to reply. However, for me the biggest draw is the value for money. Since I am paying in USD as a foreign currency and don’t use VPN unless I am on a public network or connecting to unknown websites/IP addresses, spending anything above $30 would certainly be an overkill. My recommendation for PIA goes out only on the basis of price as I believe only more expensive VPNs will be capable of providing the speeds and options that one may need on a regular basis.

Musing #22: F1 2016 (prior to season finale) in graphs

My opinion of Formula 1's 2016 season remains pretty much the same 9 months later and has in fact been reinforced by the on-track action. 2016 has again been a two-horse race or rather "two jockeys on one horse" race as the other "horse" in the race has miserably failed to prance. Even as the driver's title stands on a cliffhanger, the inevitably of it falling in Mercedes' hands is not enticing to say the least. The title fight going to the last race does not justify the largely boring on-track action throughout the season. Races like Brazil don’t happen too often and even then Pirelli’s rubbish wet weather tyres nearly managed to spoil the show. Having grown up in awe of Senna’s and Schumacher’s driving prowess and personalities, I think Verstappen’s drive has given me a reason to get behind someone since the vacuum left behind by Schumacher. Few great personalities have arisen in F1 since then and I have filled the void by devoting greater allegiance to Rossi as MotoGP has become the motorsport to beat.

Coming back to F1, this time I have decided to present the 2016 season in graphs based on public available statistics from f1.com and f1fanatic.co.uk, both of which are great resources for any F1 fan. The statistics can be said to mostly favour Rosberg because all things considered, he has been the consistent one. Keeping Hamilton’s engine gremlins aside, he still qualified outside the front row on few instances, something that Rosberg hasn’t done all season. Also, the fluffing of the starts can’t be put down to the car alone, so Hamilton must take his fair share of the blame even as his race pace has been better on most occasions. Having said so, even Rosberg has been untouchable once he’s in the race lead, so one can draw one’s own conclusions. Perhaps, having a new champion might be a welcome change going in to a new F1 era in 2017. I have always been on the “more power, mechanical grip and less downforce” camp, so the 2017 rules are not far reaching as far as I am concerned. But I guess anything will do to disrupt the status quo.

While all the other websites will judiciously update the stats following this Sunday's race, I am going to let this one be as it stands to put forth the perspective going in to the season finale, irrespective of who wins it all.

Qualification: (Click on the images for an enlarged view)



Race: (Click on the images for an enlarged view)



                                 

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.

Links:
1. Excel Karaoke Player     
2. Sample Lyrics File

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.