If anyone's interested, I've added the following code in polarzbearz IRR Excel sheet to obtain the latest NAV prices from FSM with just one click (I attached the macro to a button).



As this code relies on FSM's price list pages, if there's any changes to that in the future, this code will break. The only modifications done on the table was changing Column C into the Fund Codes used at FundSuperMart (You can get these from the URL: http://www.fundsupermart.com.my/main/fundi...do?sedolnumber=MYKNGGF) and adding the value "*exit*" into the final row of Column C. Like this:


Also, the formula for the Units calculation in Column M will sometimes result in an amount that doesn't reflect the value shown on FSM's website as the rounding order is different.



This post has been edited by idyllrain: Jul 12 2015, 05:45 PM

Here's my modified copy (with some slight differences): https://www.dropbox.com/s/r9vv8oib6blxia3/P...bearz.xlsm?dl=0

Modifications I've made:
Added macro to obtain fund prices (Options -> Customize Ribbon -> Developer tab)
If you are not comfortable with code, I highly suggest that you don't use this file and let polarzbearz look at the code, verify that it doesn't do anything malicious then include it into his version. As this file contains a macro, it will trigger security warnings and you will need to enable macros in order to use it. You have to do this only once.


The macro code in this file is slightly different than the one I posted above. The differences in this file is just that I added a message indicator saying what is being updated so that if something fails you'll know which fund is causing the problem:

Code here for auditing:

Changed "Investment Details" table into range (Table Design tab -> Convert to Range)
Since I just want to insert rows by "Select rows -> right click -> insert copied cells" *and* I don't really want to have tons of placeholder rows, I converted it to a normal range. The only downside to this, is that Excel will not autofill the formulas for you. However, it is really really simple to do that manually:
- Shift + spacebar (This selects the row if its not already selected)
- Ctrl + D (This takes the formula from the row above and fills it into the current row)

I removed the dummy values for the IRR calculations
Since I don't have any retired funds... I understand they're there to fix a summary bug

Changed unit amount rounding order



This post has been edited by idyllrain: Jul 12 2015, 05:46 PM

Awesome! Time for me to lurk again~

Thats because when I bolded the "code" part of the URL, the link got broken.

Broken: http://www.fundsupermart.com.my/main/fundi...do?sedolnumber=MYKNGGF (the bold part cannot be clicked)
Same Link without Bold: http://www.fundsupermart.com.my/main/fundi...lnumber=MYKNGGF
Price list that the excel macro gets: http://www.fundsupermart.com.my/main/fundi....tpl?id=MYKNGGF

This post has been edited by idyllrain: Jul 12 2015, 05:30 PM

In that case then yeah, I agree its best to use your version. I'll update my posts to reflect your findings.

polarzbearz I just noticed that the msxml2.xmlhttp object we've used in our code appears to cache responses from the FSM server . If you have run the code once, and you don't exit Excel, and FundSuperMart updates their prices, and you run the code again, none of the prices in the Excel sheet will update to the latest (for non-techies, it means that Excel is storing old data and reusing it). (This caching is the reason why subsequent executions of the macro code are faster.)

To get past this, I've swapped to using the winhttp.winhttprequest.5.1 object that does not cache. These are the lines that need to be changed (I changed the variable name as well):

p/s: While waiting for him to update his file, you can force an update by just saving, exiting Excel, open the file again, and run the code. This issue is just a minor annoyance.

This post has been edited by idyllrain: Jul 13 2015, 02:04 PM

Just a nickname coz the short term historical returns have been very good.

That's basically ROI after averaging out the cost/unit.

XIRR and IRR are both Excel functions that calculate IRR. The main difference is that XIRR can deal with random dates, while IRR assumes the values you give it to be periodic. The XIRR function gives you an annualized rate though, so if your data is less than year it'll give you big numbers:


Here are some examples of the different return rate formulas used:


Note that ROI and CAGR (a.k.a Annualized return) calculated the same values for all scenarios, these two measures are not time-sensitive: ROI measures the absolute return over cost of investment irrespective of how long it will take to get that rate, CAGR gives you the average return per year that your investment is compounding at.

IRR on the other hand takes into account the time-value of money, i.e. RM10 today is worth more than RM10 next year. To understand IRR more, you'll need to learn about NPV (Net Present Value, i.e what's the current value of a particular sum of money that you will get in the future).

This post has been edited by idyllrain: Sep 26 2015, 06:15 AM

In our case (as in Polarzbearz's file), we are using the "lump sum returns" example but instead of the anticipated date being a time in the future, we just move it back to today. Like this:


Nah, both XIRR and IRR Excel functions will give you the same result if your top-ups/dividends are regular (see the second example "Periodic Returns"). They are just different "methods" of calculating the Internal Rate of Return (IRR). You can think the difference between the two methods like this: XIRR tries to be as accurate as possible by asking you when you received dividends and when you invested, IRR assumes you always get dividends/perform investments at the same day every week/month/year.

You don't need to worry about the file, it is only used for tracking your investments in greater detail because FSM's website has the information scattered in different places after you've logged in. It is also not live linked to FSM; the "Update Current Fund Price" button is just a convenience feature that will go to FSM's website and get the latest NAVs of the funds you put in the Excel file.

Highlighted in yellow is "A Row of Fund", marked with blue is "A Group of Fund"


First, determine how much risk you're willing to take in the short term. Once you have this answered you can then either use FSM's recommended portfolios or build your own. An aggressive portfolio will consist mostly equity funds, balanced portfolios are less heavy on equity funds and have more fixed income funds, while conservative portfolios lean toward fixed income funds. Diversification (i.e. don't put all your eggs in one basket) is a key concept in when building a portfolio. We try to build a portfolio that satisfies our appetite for risk and is diversified across asset types and geographic regions.

An example balanced portfolio (starting with RM 10k) will be something like this:
RM1500 - 15% Equities Fund investing in Malaysia
RM1500 - 15% Equities Fund investing in Asia (excluding Japan)
RM1000 - 10% Equities Fund investing with a Global scope
RM1000 - 10% Balanced Fund investing in Emerging markets (Brazil, Russia, etc)
RM1000 - 10% Balanced Fund investing in Developed markets (US, UK, etc)
RM2000 - 20% Fixed Income Bond Fund investing in Malaysia
RM2000 - 20% Fixed Income Bond Fund investing in Asia (ex. Japan)

As to what specific funds you should invest in, you can start by looking at FSM's recommended funds. Remember though, doing great in the past doesn't mean it'll continue doing well in the future (because the people managing it may change, market conditions may change, the fund's investment aims may change, etc).

Other than the things I've mentioned (geographic region, equity/bond mix), you should be looking at the fund's expense ratio. If you are inclined, these are some of the things that you can use to select a fund:
- Fund manager (is the fund manager experienced? How long has she managed the fund? How strong is her team?)
- The fund's investment objectives (short term? Can they shift to different investment mix when the prospects are not good?)
- Parent fund details (if the fund is a feeder fund)
- The fund's holdings by sector/country/company (Is it heavy in China? India? Another country? Is it investing mainly in technology/etc companies?)

You don't have to know all these things in #5 to begin investing in UTs; selecting funds from FSM's recommended list is a pretty safe move. If you need more specific answers about XYZ fund, there are many members here with encyclopaedic knowledge of the funds offered by FSM, so just ask

This post has been edited by idyllrain: Oct 1 2015, 08:57 AM