Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Error when fetching mutual fund quotes from Morningstar #47

Open
vickychijwani opened this issue Jun 23, 2022 · 54 comments
Open

Error when fetching mutual fund quotes from Morningstar #47

vickychijwani opened this issue Jun 23, 2022 · 54 comments

Comments

@vickychijwani
Copy link

vickychijwani commented Jun 23, 2022 •
edited
Loading

Hello, I encountered the 404 error below when using muFunds to display mutual fund NAV from Morningstar. I believe it only stopped working a few days ago, so it's probably a recent change in Morningstar.

Here's a sample Morningstar id that can be used to replicate this - f00000pdmk. It's an Indian mutual fund, if that matters. Let me know if I can provide any other info to help debug this - thanks!

No se pudo completar la solicitud para el código devuelto 404 de http://quotes.morningstar.<?> Respuesta del servidor truncada: The report is no longer supported (usar la opción muteHttpExceptions para examinar toda la respuesta). (line 123).
@damonbrodie
Copy link

I'm getting it for Canadian mutual funds too - same error.

muFunds("nav","FID1682")

@joseballester
Copy link
Owner

Hi @vickychijwani and @damonbrodie! Thanks for reporting this.

It seems that Morningstar has stopped supporting their generic reports available at quotes.morningstar.com (e.g. this one, where the message "The report is no longer supported" can be read). This means that the Morningstar generic mode, as explained here and which was the default behavior if no source option was given, will not work anymore.

This means that, for making muFunds work again, a user should now explicitly define a Morningstar country-specific source as the third argument of the muFunds function, as explained in the Usage section. This is valid for mutual funds that are commercialised in any of these countries: Australia (au), Austria (at), Belgium (be), Denmark (dk), Finland (fi), France (fr), Germany (de), Iceland (is), Ireland (ie), Italy (it), Norway (no), Netherlands (nl), Portugal (pt), South Africa (za), Spain (es), Switzerland (ch), United Kingdom (uk/gb).

Basically, we should now use muFunds("nav", "(identifier)", "morningstar-**"), where ** is one of the countries above, in which the fund is commercialised.

If we don't find any way to replace these generic Morningstar reports we were using, we should either:

a) Make the source option (third argument of the muFunds function) mandatory, so the user is forced to define the Morningstar country site that should be used to retrieve the data (which basically would be their country), or

b) Within the muFunds function, check all Morningstar countries until we find one in which the data is available for the given fund. This would be more time consuming, but the country could be cached for future calls.

@mattjgalloway, what do you think?

@damonbrodie: As you may see in the previous list, muFunds is currently not compatible with the Canadian version of Morningstar right now, but I'll try to fix that in the next few days.

@wroliveira1
Copy link

It used to work for brazilian funds too. Very neat. Hope you can find a fix.

@alexpatez
Copy link

I also often use it to track Brazilian funds. I hope the problem will be fixed.

@mattjgalloway
Copy link
Contributor

mattjgalloway commented Jun 24, 2022 •
edited
Loading

@joseballester should we just release an update with the fixes we made a while back? That would make the country based one’s work right?

I agree with your idea of making the source mandatory.

@Garate77
Copy link

The proposed solution is working but for the pension plan option. The following example returns an error:

=mufunds("nav", "F00000XL5T", "morningstar-es")

@vickychijwani
Copy link
Author

Thanks for the quick reply @joseballester! What would it take to support India as a compatible source country? (morningstar.in)
I can help if the fix is straightforward :)

@alechima92
Copy link

Thanks @joseballester! It works for me now!

@woxxa-Wiz
Copy link

Thanks, works. As a note; ISIN no longer works with country specific, need to use morningstar ID

@WillemWijnans
Copy link

This means that, for making muFunds work again, a user should now explicitly define a Morningstar country-specific source as the third argument of the muFunds function, as explained in the Usage section.

Awesome, thx for the fix

@alexpatez
Copy link

The problem remains for Brazilian funds... It is impossible to find the morningstar ID for Brazilian funds. :(

@Jvhowube
Copy link

Is there any fix for US funds? The US is not in that country code list. Thanks.

@joseballester joseballester mentioned this issue Jun 24, 2022
Merged
@ofaofaofa
Copy link

hi, @joseballester , the solution only works with the listed countries? i tried Luxembourg and still ERROR
thanks for the help!

@sanksara
Copy link

sanksara commented Jun 25, 2022 •
edited
Loading

@joseballester
Please help add singapore morningstar.
my many of mutual funds are listed in morningstar singapore. Previously working but now stopped
Please help

@shaneyamkowy
Copy link

shaneyamkowy commented Jun 25, 2022 •
edited
Loading

I can not get Cdn mutual funds to work either, "morningstar-ca" does not work? Is there a country code for Canada?

As a possible mutual fund source, globe and mail might work?

@Sebsebzen
Copy link

Also would need morningstar singapore. Thanks

@joseballester
Copy link
Owner

I'm trying to update the addon published in the Google Workspace Marketplace to let users know that the source argument will now be required. I was also planning to update it to make it compatible with some of the countries that have appeared in this discussion.

However, I'm having issues to deploy a new version to the marketplace because of the nature of the Google Cloud Platform project associated to my Google Apps Script project. I'm trying to solve this, but for now it seems that the only solution is creating a new GCP project (user-managed/standard instead of the Apps Script-managed/default one, more here). The bad news is that this would make users uninstall the current muFunds addon and install a new one, but without me having a way to tell them to do so.

@Jvhowube: I believe the Google Finance built-in function works for US mutual funds. You can try with =GOOGLEFINANCE("MUTF:(ticker)"). More info here (search for United States) and here.

@Garate77: It is true that morningstar-es does not work with Spanish pension plans right now, since the page layout is slightly different. You can use the quefondos source instead, but it only returns nav, date, currency and category (change and expenses do not work).

@woxxa-Wiz: Searching by ISIN should still work. When using a ISIN, the Morningstar ID is searched before actually querying the fund data. Could you please let me know the ISIN of the fund you're having trouble with?

@ofaofaofa: Could you please let me know the ISIN of the fund for which morningstar-lu doesn't work? You should choose a country from the list where the fund is available for sale.

@vickychijwani, @damonbrodie, @wroliveira1, @alexpatez, @sanksara, @shaneyamkowy and @Sebsebzen: I am willing to update the addon to make it compatible with Canada, Brazil and Singapore, respectively, but I am having the issue with the Google Marketplace. I will update you soon, thanks for your patience.

@ofaofaofa
Copy link

LU1353950568

@joseballester
Copy link
Owner

@ofaofaofa Sorry, I misunderstood your question before. Yes, the solution only works for the listed countries, which I'll be extending in the future, and Luxembourg is not in that list. Anyway, for the fund with ISIN LU1353950568, even if morningstar-lu is not available, you can try using another country where this fund is available for sale, e.g. Spain (morningstar-es).

Note: If you try it right now, it may fail since it seems Morningstar is not working at the moment (checking this fund in Morningstar Spain returns an error), but hopefully the issue will be solved soon.

@chickyyip
Copy link

I cannot find a source can retrieve ISIN: HK0000055761 "JPMorgan Pacific Technology"

but it can be retrieved on "morningstar" before

I find that the ISIN code start with HK cannot be retrieved on every provided source, please recommend.

@ofaofaofa
Copy link

@joseballester Crees que van a solucionar el error? o la solucion final es usar morningstar-xx
muchas gracias!

@wgking
Copy link

wgking commented Jun 28, 2022

My Canadian funds are reporting ERROR like those above. I tried adding 3rd "morningstar-ca" field without any change. Is the function fixed yet, and if so do I have to reinstall it to get the fix?

Thank you for this very useful add-on.

@mrbatch5
Copy link

mrbatch5 commented Jun 28, 2022 •
edited
Loading

Hi,

EDIT 28/6 Working again after un/reinstall

Funds stopped working today for Morningstar-UK ones. I use ISIN but also the direct Morningstar codes which i recheck are right by using them on the Morningstar search page. eg F00000SRPM

I uninstalled mufunds but it will not re-install. Get a box up saying ready to install, press continue , then box disappears but still says it is not installed ie "Install" option still available.

Great app I use every day in spreadsheet with Google finance etf funds - whole s/s stops without the related funds.

Hope you can advise workaround or a fix.
Best Lawrence

@carlotavaldes
Copy link

carlotavaldes commented Jun 28, 2022 •
edited
Loading

It's not working for me either. I am sure I am doing something wrong, but I cannot figure out what.
Example of what I am doing:
=muFunds("nav", "(ES0112611001)", "morningstar-es")
Edited: the error is the following: "This asset is not compatible with this Morningstar country. Try another country or data source (line 112)," which is strange as this is a Spanish fund.

@camzz
Copy link

camzz commented Jun 30, 2022

I ran across the issue from this thread and have tried supply the 3rd option to fix, but am getting an error.

I am trying:
=muFunds("nav", "IE00B76VTN11", "morningstar-gb")

and I get the error:
TypeError: No se puede llamar al metodo "toXmlString" de undefined. (line 103).

I tried using uk and gb, both had the issue.
I tried "ie" also since I guessed some of my the assets I am tracking would be available there and it seemed to work for around 50% of them.
Therefore my guess is there might be some error in the handling of the uk specific codes? Since these methods do seem to work, just not when I use a uk/gb code.

@mrbatch5
Copy link

@camzz
this works
morningstar fund code is F00000N9WG put this in cell a1 ( ghet this number by using the isin number you gave to goto fund on morningstar then in https url see the morningstar ref ID ie F00xxxx

in cell a2 put exactly including the =value

=value(muFunds("nav",a1,"morningstar-uk"))

obviously make sure mufunds is installed in your excel or google sheet instance

@CarlosVadinia
Copy link

It's not working for me either. I am sure I am doing something wrong, but I cannot figure out what.
Example of what I am doing:
=muFunds("nav", "(ES0112611001)", "morningstar-es")
Edited: the error is the following: "This asset is not compatible with this Morningstar country. Try another country or data source (line 112)," which is strange as this is a Spanish fund.

Worked for me just by replacing your , for ; and taking off the ()

@dvx76
Copy link

dvx76 commented Jul 4, 2022

Hi @joseballester ,

I'm trying use https://tools.morningstar.be/be/snapshotpdf/default.aspx?Site=be&id=F0GBR04IT6&currencyid=EUR (used to work before this issue, using the ISIN).

Tried these variations:

# Using the ISIN
=muFunds("nav"; "BE0026535543"; "morningstar-be")

# Using the code from the morningstar URL above
=muFunds("nav"; "F0GBR04IT6"; "morningstar-be")

Getting the same This asset is not compatible with this Morningstar country as others.

@shaneyamkowy
Copy link

just checking in, any idea when Canadian Mutual fund data will work again?

@carlotavaldes
Copy link

I figured it out, but thanks for your help anyway.
I changed it for this, which also works fine:
=value(muFunds("nav",B4,"morningstar-es"))
B4 being cell ES0112611001

It's not working for me either. I am sure I am doing something wrong, but I cannot figure out what.
Example of what I am doing:
=muFunds("nav", "(ES0112611001)", "morningstar-es")
Edited: the error is the following: "This asset is not compatible with this Morningstar country. Try another country or data source (line 112)," which is strange as this is a Spanish fund.

Worked for me just by replacing your , for ; and taking off the ()

@lewandor
Copy link

lewandor commented Jul 7, 2022

@joseballester I believe the Google Finance built-in function works for US mutual funds. You can try with =GOOGLEFINANCE("MUTF:(ticker)").

As far as I can tell, there are some Vanguard fund classes that don't have tickers. For example, Vanguard Target Retirement 2065 Trust Select,, which is different than Vanguard Target Retirement 2065 Fund (VLXVX). I used to get the NAV for the Trust Select version of the fund from muFunds by looking it up via the CUSIP in Morningstar, but with this latest country change, that's not working.

Any suggestions, as I can't find this fund in any of the country specific morningstar sources, and there is no ticker for GOOGLEFINANCE to look up? Once upon a time I wrote a scraper with a web API to get data like that from the relevant Vanguard page, but it was pretty hacky and unreliable due to my rudimentary knowledge of the technology. I stopped messing with it when I found muFunds because it worked waaay better.

@sanksara
Copy link

sanksara commented Jul 7, 2022

@joseballester .. it has been quite sometime now.. till now no workable solution...
It was amazing to use mufunds..
Is there any way of reviving the addon?
Thanks for your great help..

@nearlycossack
Copy link

I'm a UK user and am still having problems with mufunds and am still getting the error : Error
TypeError: No se puede llamar al método "toXmlString" de undefined. (line 103).

A few ISIN examples "GB00B7QK1Y37" and "GB00B41YBW71".

I am using "morningstar-uk" as the third argument in my function call. I have uninstalled and reinstalled mufunds but still get the error. Thankyou.

@mrbatch5
Copy link

@nearlycossack
UK user - this works

Use a morningstar fund code ie F00000N9WG put this in cell a1 ( get this number by using the isin number you gave to goto fund on morningstar then in https url see the morningstar ref ID ie F00xxxx

in cell a2 put exactly including the =value

=value(muFunds("nav",a1,"morningstar-uk"))

obviously make sure mufunds is installed in your excel or google sheet instance

@sanksara
Copy link

Instead of waiting for this addon to get updated, the simple workaround is to use "importxml" function... And get this current nav data from either FT or Bloomberg... For me both are working... Can get data from morningstar too...

@nearlycossack
Copy link

@nearlycossack UK user - this works

Use a morningstar fund code ie F00000N9WG put this in cell a1 ( get this number by using the isin number you gave to goto fund on morningstar then in https url see the morningstar ref ID ie F00xxxx

in cell a2 put exactly including the =value

=value(muFunds("nav",a1,"morningstar-uk"))

obviously make sure mufunds is installed in your excel or google sheet instance

Thanks. It looks like I was using the ISIN # to look up morningstar-uk which no longer works. Using the other reference does work.

@electriclight2022
Copy link

@sanksara Could you please provide an example of how you use "importxml" in Google sheets? I'm trying to get price info on a Canadian mutual fund (TDB900), but I'm a noob and can't figure it out. I keep getting either #ERROR or #N/A.

@joseballester Thank you so much for creating and maintaining muFunds. I'm a longtime user, and it's been hugely helpful for me. I'd be doubly grateful if you could fix this latest problem and get it working again, especially for Canadian funds. Thanks!

@JanuaryKing
Copy link

To clarify (slightly) - for UK at least, do as @nearlycossack says - the key is to look up the fund or whatever on morningstar and on the resulting page go into the URL at the top of your browser. At the end there is the code that will will work. Did for nine out of nine of my problem ones. Thank you again mufunds!

@alcorhythm
Copy link

While muFunds is not supporting Canadian morningstar, you can get Canadian mutual funds (e.g. RBF 460: RBC Select Balanced) from Globe and Mail:
=IMPORTXML("https://www.theglobeandmail.com/investing/markets/funds/RBF460.CF/", "//barchart-field[@name='lastPrice']/@value")

To get Canadian ETFs and stocks (e.g. HBAL: Horizons CDN Balanced Portfolio ETF):
=IMPORTXML("https://www.theglobeandmail.com/investing/markets/stocks/HBAL-T/", "//barchart-field[@name='lastPrice']/@value")

@electriclight2022
Copy link

@alcorhythm Thank you! This is fantastic, and exactly what I was looking for. Thanks for taking the time to lay it out so clearly.

@damonbrodie
Copy link

@alcorhythm Thanks very much for posting the importXML. Works great.

I'm sad that muFunds seems to be dead now. Hopefully somebody picks up the mantle and is able to support it!

@joseballester
Copy link
Owner

joseballester commented Jul 16, 2022 •
edited
Loading

Hi all! After having some issues when publishing to the Google Workspace Marketplace, I've finally managed to deploy the latest updates, so muFunds should now properly (except expenses for Denmark). Adding other countries such as Canada, Brazil and Singapore will happen very soon.

Those that were having issues until now, and are working within one of the compatible countries, could you please confirm that everything works for you now?

@carlotavaldes
Copy link

Thanks Jose,
it's working well in general. Some assets however keep giving me error:
ES0116567035
LU1670724704

@5minsf
Copy link

5minsf commented Aug 2, 2022

Thanks Jose,
everything seem to work fine without country-specific.

@alexpatez
Copy link

alexpatez commented Aug 2, 2022 via email

@damonbrodie
Copy link

Hi @joseballester

Any progress on supporting Canada again? Is there a barrier to getting it working again or just the work to scrape the HTML?

@joseballester
Copy link
Owner

Hi all! Now the generic Morningstar mode is working again, so if you leave the third argument empty or use morningstar, it should work as well.

@joseballester
Copy link
Owner

Thanks Jose, it's working well in general. Some assets however keep giving me error: ES0116567035 LU1670724704

@carlotavaldes: Could you please check now? These funds should now work for both morningstar and morningstar-es sources.

@joseballester
Copy link
Owner

In the case of Brazilian funds, the pension funds (BRADA6CTF006, BRMPF0CTF006, BRMPF8CTF009 and others) are not working. Months ago it worked correctly.

@alexpatez: For some reason it seems that these funds are not supported right now. To understand it better myself, what is the difference between the ones you're referring to and this one: BRMPFMCTF005, for example? Are the first ones pension funds and these ones regular mutual funds?

@joseballester
Copy link
Owner

Hi @joseballester

Any progress on supporting Canada again? Is there a barrier to getting it working again or just the work to scrape the HTML?

@damonbrodie: Canadian funds should be supported now via generic mode (morningstar source option). What fund identifier were you using in the past? I've tried and at least it works when using Morningstar ID (e.g. ABC American-Value with Morningstar ID 0P000070JR works correctly with morningstar data source, although it does not return the 1-day change or the expenses ratio).

@sanksara
Copy link

sanksara commented Aug 7, 2022 •
edited
Loading

Hi Jose,

SG9999004311, SG9999003826, SG9999015176, LU1410565573 these funds doesnot fit in any country.. please advise

Capture

@damonbrodie
Copy link

damonbrodie commented Aug 7, 2022 via email

@alexpatez
Copy link

alexpatez commented Aug 8, 2022 via email

@plocie
Copy link

plocie commented Aug 11, 2022 •
edited
Loading

@damonbrodie
muFunds - version 28-July is working for Canadian Mutual Funds - using the generic morningstar format.

The key is - use the morningstar ID - not your fund code.

How do iI find morningstar ID ?

  1. GOOGLE search your Mutual Fund Name ie) IA Clarington Global Allocation Fund Series A

  2. Select item containing morningstar.ca ie)
    https://www.morningstar.ca › report › fund › performance
    IA Clarington Loomis Global Allc A, Fund, performance

  3. check Fund Name - to ensure Name is exact (there are many variations of the Fund Name)

  4. view url > it has morningstar ID included ie. https://www.morningstar.ca/ca/report/fund/performance.aspx?t=0P0000KS0Z

  5. use generic formula =muFunds("nav", "0P0000KS0Z","morningstar")

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests