joseballester / mufunds Goto Github PK
View Code? Open in Web Editor NEWµFunds lets you import your asset data from Morningstar and other sources to your Google Sheets spreadsheet by using a simple formula.
Home Page: https://mufunds.com
µFunds lets you import your asset data from Morningstar and other sources to your Google Sheets spreadsheet by using a simple formula.
Home Page: https://mufunds.com
The Morningstar fund id is GB00B7QK1Y37. (iShares North American Eq Idx (UK) D Acc (Blackrock))
I'm using =muFunds("nav", "GB00B7QK1Y37", "morningstar") but getting an error message :
"Error: NAV is not available for this asset and source. Please try another data source (line 80)."
When getting data from Morningstar Italy, the returned expenses ratio is wrong (maximum entry fee is returned instead). This is a bug in HTML parsing that should be solved in the next version.
Hello José Ballester and muFunds
Thanks for a nice add-on (most of the time).
I am using muFunds to track three Danish investments through morningstar:
Sparindex INDEX DJSI World KL (ticker: SPIDKJWKL)
Sparindex INDEX Europa Growth KL (ticker: SPIEUGKL)
Sparindex INDEX Emerging Markets KL (ticker: SPIEMIKL)
The weird thing is that somestimes one of them works and the two others don't. Then at other times, a different one works and the two others don't. So it differs from time to time which one or more that don't seem to work. If it makes an error, it goes:
Exception: Request failed for https://www.morningstar.dk returned code 403. Truncated server response: <META HTTP-EQUIV="Content-Type" ... (use muteHttpExceptions option to examine full response) (linje 70).
For each ticker, I use this code:
SPIDJWKL: =E14muFunds("nav";"DK0010297464";"morningstar-dk")
SPIEUGKL: =E15muFunds("nav";"DK0010297548";"morningstar-dk")
SPIEMIKL: =E16*muFunds("nav";"DK0060300762";"morningstar-dk")
Sometimes it's my E14 that works fine and E15 and E16 gives me an error message and other days, E15 works fine and E14 and E16 gives me an error message without I touched anything. I don't understand this.
Can you help me?
Thanks.
I have this strange fund pension fund:
https://www.morningstar.co.uk/uk/snapshot/snapshot.aspx?id=VAUSA0KKID&investmentType=SA
If I call muFunds("nav","VAUSA0KKID","morningstar-uk")
I get a TypeError: No se puede llamar al método "getDescendants" de undefined. (line 33).
error. Digging through the code it looks like muFund does not handle queries with that require a investmentType
parameter, which is the case here. I also see that this fund is shown as GBX when really it is trading GBP.
Did you come across such strange fund pages? I am happy to add some custom parsing code to my sheet and not go through muFunds, but thought I'd ask if this could be fully supported.
And thanks for building this!
This has been working great for me for a long time so thanks so much!
However I found today that when I use morningstar-uk as the source I now return an error. It says:
Error on line 222: The prefix "xml" cannot be bound to any namespace other than its usual namespace; neither can the namespace for "xml" be bound to any prefix other than "xml". (line 106).
Any ideas how to fix this?
Trying to import iShares Global Property Securities Equity Index Fund (UK) D Acc using =muFunds("nav", "F00000OJRZ", "morningstar-gb")
Getting error TypeError: No se puede llamar al método "getDescendants" de undefined. (line 33).
I installed code as my own app script and found that Xml.parse is no longer supported under V8 runtime so had to move it back to Rhino. I ran a test using XmlService.parse but hit a badly formed XML error.
Hi! Thank you for creating muFunds! Any plans of adding an option to retrieve the expected dividend payout of a stock?
Hi,
It's been over a week that I notice that the "change" numbers given by muFunds do not correspond with the morningstar info.
Some examples with the latest numbers, September 5th:
ES0112611001, muFunds is quoting 2,57%, and morningstar 2,13%
ES0124037021, muFunds is quoting -0.28%, and morningstar 0,13%
ES0159201013, muFunds is quoting -2.05%, and morningstar -1,65%
ES0116567035, muFunds is quoting -0.66%, and morningstar -0,25%
etc. I could go on, but all seem to be wrong.
Any tips on this? Thanks!
I Can't find correct wording to get Google sheet to extract AutoCallable Notes : https://www.bmonotes.com/fr/Note/JHN14584.
anyone has idea how to proceed?
Thank you
This is an error:
SyntaxError: Unexpected token '<', "<?xml vers" ... is not valid JSON (line 179)
When calling muFunds
for certain funds (usually Morningstar country pages), the following error is obtained:
Argument too large: value (line 132)
This is due to the fact that µFunds attempts to put a string larger than 100 KB in the Google Apps Script cache (source).
HTML parsing for data from the Australian Morningstar page has stopped working. For example, when asking for OnePath OA FR IP-T. Rowe Price Global Eq fund's NAV with the function call =muFunds("nav","MMF1563AU", "morningstar-au")
, an HTML parsing error is obtained:
TypeError: No se puede llamar al método "getValue" de undefined. (línea 145).
Hello, it would be useful to be able to retrieve "high52" and "low52" data as is possible with the GOOGLEFINANCE function
An example request URL would be: https://markets.ft.com/data/equities/ajax/get-historical-prices?startDate=2020%2F03%2F26&endDate=2020%2F03%2F26&symbol=101272178
(for 2020-03-26 and the fund Cobas Selección FI).
Hola josebaseller.
Instalo el complemento, lo activo y cuando introduzco la fórmula en cualquier celda siempre aparece ese error.
He probado con dos parámetros, con tres, con referencia al ISIN, poniendo el ISIN como cadena...
Nada, no hay manera de obtener el precio del fondo ni siquiera con el ejemplo.
Por cierto los ejemplos tienen una pinta estupenda. Gracias, me parece un trabajo genial.
I have been having problems with this two codes retrieving data from Morningstar . The first one, LU0198728585 corresponding to THREADNEEDLE GL FOCUS AEH, is returning 2012 data and date when you can see that morningstar has the correct data. With the second one, LU0406802339 corresponding to BNP PARIBAS CLIMATE IMPACT, the date seems to be correct but not the VL. Thanks in advance.
On using function =VALUE(muFunds("nav", C2,"morningstar-uk")) or =VALUE(muFunds("nav", C2,"morningstar-gb"))
getting an error TypeError: No se puede llamar al método "toXmlString" de undefined. (line 103).
The fund can be tracked on the website. https://www.morningstar.co.uk/uk/funds/snapshot/snapshot.aspx?id=F00000M0M8
sorry an update, I corrected an error in syntax, but still getting mismatched change values on CIBC Global Monthly Income Fund F = 0P0001K9S3
For Nov 22, =muFunds("change", 0P0001K9S3) gave a value of $0.0034, but the actual change according to morningstar and globe and mail web sites was $0.0118.
The NAV value is correct
Any ideas? Help?
Hello
I just noticed that the countries supported by mufunds do not include Canada. Do you have any plan to add Canada to your support list?
Google finance does not provide price for Canadian mutual fund, so it's become a big headache. If you can solve it, it will be great
Hi Jose. I do not get the formula working. I tried to find the answers on reddit but did not succeed. I hope you can help me.
I got the the add-on working in the sheet.
The settings are Dutch (decimal ,)
I type e.g. the formula =muFunds("value","NL0013689110"); the variables are in green font so that is ok.
The result is an error in the cell with the message "Parser error in formula".
I have an Apple MacBook. I get the same results in Firefox and Chrome.
What is incomprehensible for me that the example file on your website shows the formula =WAARDE(muFunds("nav", $B$2)). And it works. But when I try it my worksheet I still have this parser problem. =WAARDE(muFunds("nav","LU0690375182"))
https://docs.google.com/spreadsheets/d/1CKog0J7FvuMGyh-XzF9yffor8-kqMLazBOg0PAedzUM/edit#gid=0
Thank you.
Peter
While following the muFund guide to save history of mutual funds, I had not got values for muFund formulas when the script was triggered automatically. I finally got it solved by adding to suggested code flush function at script initiation, which reclaculates all values within the Spreadsheet: SpreadsheetApp.flush()
.
I just recommend to include this in the guide: https://mufunds.com/history.html
Hi,
Cant pull nordic stocks from Morningstar. E.g.:
=muFunds("nav";"NO0003054108";"morningstar-no")
, throws the error message:
I am getting the error message: TypeError: No se puede llamar al método "getDescendants" de undefined. (linje 33).
Following;
=muFunds("nav";"NO0011088593";"morningstar")
, throws the error message:
Error: NAV is not available for this asset and source. Please try another data source (linje 80).
I have tried different IDs for various nordics stocks, and none seems to work - except for funds.
Hope you have some time to look into this!
Hola,
En google finance es posible coger cotizaciones de Stocks y ETFs del pasado. Con mufunds no veo esa opción. Por ejemplo con googlefince puedo poner asi: GOOGLEFINANCE(Ticker;"price";FechaIni;FechaFin;"DAILY")
y con ello cojo un rango de fechas pasados o una fecha pasada concreta.
¿Es posible con mufunds hacer algo parecido?
Os dejo mi hoja googlesheets donde tengo todo hecho, precios presentes y pasados, de stocks, ETFs, y divisas, pero me falta el precio pasado de fondos.
https://docs.google.com/spreadsheets/d/1pSs2mC38gaKbqUYBkmMv7s1sbFWaVJmVJS7GvdXpyDk/edit?usp=sharing
Si pudieseis incorporarlo sería estupendo
Muchas gracias
Antonio
It seems the entire fetching from Morningstar has stopped working in the last week.
My google sheet now shows "Error Loading ..." for all invocations of the formula muFunds()
To verify I did not accidently break something in my google sheets, I made a new copy of one of the examples from the muFunds website (https://mufunds.com/usage.html - Examples: Single mutual fund).
The same problem happens in this google sheet document as well.
As an example, this formula now displays "Loading ..." forever:
=VALUE(muFunds("nav", "LU0690375182"))
So I fear Morningstar has changed their HTML format?
Hello. I get this :
Error: Expenses ratio is not available for this asset and source. Please try another data source (línea 104).
I tried with all the different sources, but the problem remains... The ISIN is this: LU0389812347 , is a very popular fund for european bogleheads.
Thanks for your amazing work!
How do you get muFunds to run without changing any of the cells in use?
Hi team, @joseballester
Thank you for your work, and appreciate the tool.
I'll suggest adding to the backlog, if you think that makes sense, the possibility to get the info of the last 15-30 days to determine a trend. I think Google finance F() allows this.
Best!
I've got most other funds working in google sheets, but this one:
Legal & General International Index Trust Class C - Accumulation
Just doesn't seem to propagate and errors all the time. I've tried both ISIN and morningstar URL id..
Any ideas...
Hi, trying to pull two Vanguard UK funds into Google sheets but for both get the error message
"Error: you have selected an invalid option. (line 16)."
The entries are on lines 17 and 18, but the error for each references 16, which is a different stock and is correct.
I am using nav,morningstar ID, morningstar-uk (or - gb), neither work.
When running
=muFunds("nav", "US0231351067" , "morningstar-es")
I'm getting error:
Error: NAV is not available for this asset and source. Please try another data source. (line 18).
Even though I can see data for that ISIN in morningstar.es: https://tools.morningstar.es/es/stockreport/default.aspx?SecurityToken=0P000000B7]3]0]E0WWE$$ALL
Who does one need to suck to get this fixed?
The permissions required to the install the plugin are scary from a security perspective, for example : "See, edit, create and delete all your Google Sheets spreadsheets". They seem to be far in excess of what is required.
Can the set of requested permissions be restricted to the minimum required for the operation of the add-on?
Hi,
I followed this guide to create an automated data storing for my funds https://mufunds.com/history.html. Manual operations works fine, but the automation (time-based trigger) does not always work. Sometimes I get just a "#NAME?" in the value field, while the date and fund name get printed correctly.
One of the funds I track is 'FI4000223565', which I use "morningstar-FI" for in a cell call (=muFunds("nav"; "FI4000223565";"morningstar-fi")).
Does the trigger require the computer on which the script has been built to be running when the trigger launches?
Rgds,
Björn
PS. Disclosure: I modified the script a bit and added three instances of it for one page, to collect three different funds with one Sheet. As I remember, the results above were the same before modifying, and running each of these manually produces the expected results, so the modifications seem OK.
PPS. It would be awesome to have an example of a script taking into account fetching several funds :).
Hola Jose,
Al cargar el código de MuFunds en una hoja de google spreadsheet me da ese error en las celdas en las que utilizo las funciones. Es como si el "Xml.parse(xmlstr, true)" no estuviese funcionando. Sin embargo, si utilizo el complemento de MuFunds, si que me funciona.
Te agradezco la ayuda,
Un saludo,
When trying to access the expenses
option through a Morningstar country snapshot (morningstar-*
), the following error is returned:
Error on line 162: The prefix "xml" cannot be bound to any namespace other than its usual namespace; neither can the namespace for "xml" be bound to any prefix other than "xml". (line 121).
Hi there,
perhaps this is an issue or maybe I am doing something wrong. I got several worksheets in Google spreadsheets that are working fine with muFunds. But just now when I am opening a new sheet the plugin behaves differently:
It seems as if the spreadsheet doesn't know the muFunds function where it is working in a different sheet without any problems. The add-on is allowed to be executed with any sheet:
So where's my mistake? Or is it a bug?
Best regards,
Chris
Hi,
After installing and authorising the uFund add-in, I am unable to use this add-in, receive error message "NAME?", and when I hover over this error message "Unknown function 'mufunds' is displayed. It appears to be not recognising the add-in. I have tried uninstalling, then re-installing but I still get the same error. I have checked under Managed Add-Ins and it is successfully installed. I am using google sheets on a Windows 10 Professional PC. Can you please advise how to fix this problem.
Fund name APIR ISIN Participat. Purch. price Purch. amount Cur. price Cur. amount Date Gains ± relative ± last TER (%) Learn https://mufunds.com/usage.html
Fundsmith Equity Fund Feeder T EUR Acc LU0690375182 82715.786323 3.772 312000.00 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
Mo matter what combination of option, id, or source that I use on Google Sheets, I just get error messages.
For example, trying to get the nav for Magellan Global:
=muFunds("nav","AU60MGE00068","morningstar") using the ISIN doesn't work - and I have tried "morningstar-au" as the source as well.
=muFunds("nav","MGE006AU","morningstar-au") using the APIR doesn't work - also changing the source.
The instructions also usggest using the Morningstar ID, but even as a daily user of morningstar, I can't find the secure ID - the AU website appears to be using the ASX number system for funds (eg Magellan Global is 15699.
Can someone give me a single example of the cell formula that works with common Australian funs?
My Australian Fund data being tracked on muFunds appears to be showing data that is weeks old - that is, muFunds has stopped updating.
Is there a way to check or force an update?
Hi Jose,
great work on your tool and thanks first of all.
Since this morning I've been getting a "Dirección no disponible: http://quotes.morningstar.com/fund/c-header?t=IE00BFZXGZ54 (riga 123)."
Any idea if this is morningstar changing links or any other possible issue?
thanks!
On a regular base, I experience the fact that I receive a 403 HTTP error response back when the following formula is being executed: =muFunds("date";LU1864953499;"morningstar-be")
The issue does not occur always, but quite regularly I would say...
More details about the exception below:
Exception: Request failed for https://www.morningstar.be returned code 403. Truncated server response: <META HTTP-EQUIV="Content-Type" ... (use muteHttpExceptions option to examine full response) (regel 70).
I get this error message for every use of muFunds.
Error: You have selected an invalid option (line 16)
Examples:
=muFunds(NAV, GB00BDSFHH38, morningstar-uk)
=muFunds(currency, F00000YBT6, morningstar-uk)
For Legal & General International Index Trust C Class Accumulation, it's been showing error for quite sometime...
=muFunds("nav", "GB00BG0QP604", "morningstar-uk")
Trying to use morningstar-gb as a source for one of my mutual funds (GB00B6QQ9X96) and it returns an error on line 103 of I think morningstar-country.js. Also for some reason the error message is in Spanish: ‘TypeError: No se puede llamar al método “toXmlString” de undefined. (line 103).
I understand that this project is targeted at funds, but is there any plans to support the closing price of a stock/ETF? Google Finance does not have the same coverage as Morningstar; ETFs such as IE00B4L5Y983 don't exist in Google Finance, but they do not have a NAV either.
Would it be very difficult to support this? From what I understood this plugin is based on HTML scraping, so wouldn't it suffice to add a new function for extracting a different attribute?
Tras usar la formula =VALUE(muFunds("nav";A2)) me coge el valor pero en lugar de ser por ejemplo 170,84 aparece 17084,00.
En mi caso A2 es (LU0389811885)
¿Hay que dar algun formato especial a la celda?
I guess this would be niche, but Google now defaults new accounts to Advanced Protection, and has automatically upgraded millions of accounts that have had attacks against them in the past. Error message when trying to install:
Authorisation Error
Error 400: policy_enforced
Advanced Protection prevented your Google Account from being signed in. This security feature stops most non-Google apps and services from accessing your data to keep your account protected.
Learn more
Request Details
The content in this section has been provided by the app developer. This content has not been reviewed or verified by Google.
If you’re the app developer, make sure that these request details comply with Google policies.
access_type: offline
login_hint: [my email]
hl: en_GB
response_type: none gsession
redirect_uri: https://script.google.com/oauthcallback
state: ACjPJvEzp_CtBeHcRUzgVbvpkjnWSt3-gl7gHvGJewLXw2PR3vLjAuBi9Raqn9cyJ7Kynd2Pl2U
app_script_properties: AIXpZZnTuB83IgZGkag41bFKKUNHIiJIa0XvI4WUpow0Qbed2JHjpPbGsLi0V2tnBI1kAAe-o7zYG4kMFYLZcqGZpJuAIb5nmuHE3eRqEhgTKKdNWv5hgmmE6hh4H87JCEr8KIN2uLQx3p0OxD64NIE-4Vu-_OUIgIGy39i3KPSTx4UiaIliEZudVnqo0vr66sB3HuugwqPA
client_id: 274036499952-4aq47fh7m3e0d3t55mfbt5hrmjjauau4.apps.googleusercontent.com
prompt: consent
scope: https://www.googleapis.com/auth/script.external_request https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/script.container.ui https://www.googleapis.com/auth/userinfo.email https://www.googleapis.com/auth/userinfo.profile openid
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).
It seems that muFunds do not support China funds (morningstar.cn).
Is there any plan for supporting it?
I'm really appreciated that you present this excellent&easy formula. But I have a problem that may be you can help me know what's problem
1.) I try this formula with this =muFunds("nav,LU1997245508,https://www.quefondos.com/es/fondos/ficha/index.html?isin=LU1997245508"). But the result show this context "#ERROR! You have selected an invalid option. Do I have to install any code first to make it work?
2.) Is this formula gonna work with Bloomberg too?
Thank you
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.