Lately, I've become increasingly disappointed with Google Finance's slow updates for daily mutual fund NAV quotes in my Google Docs portfolio tracker.
Rather than do workarounds with ETF quotes, which are actually up-to-date, I've replaced my Google Finance functions and now pull all fund quotes from marketwatch.com.
The529guy wrote: ↑Fri Feb 02, 2018 6:09 pm
Lately, I've become increasingly disappointed with Google Finance's slow updates for daily mutual fund NAV quotes in my Google Docs portfolio tracker.
Rather than do workarounds with ETF quotes, which are actually up-to-date, I've replaced my Google Finance functions and now pull all fund quotes from marketwatch.com.
Yes, real-time in the sense that -- right now at 7:15pm ET -- marketwatch.com returns today's closing NAV and finance.google.com still returns yesterday's closing NAV.
And, even though finance.google.com's quote tool returns the NAV from yesterday (Feb 1), its timestamp for the quote says "Jan 31, 7:00PM EST".
The529guy wrote: ↑Fri Feb 02, 2018 6:15 pm
Yes, real-time in the sense that -- right now at 7:15pm ET -- marketwatch.com returns today's closing NAV and finance.google.com still returns yesterday's closing NAV.
And, even though finance.google.com's quote tool returns the NAV from yesterday (Feb 1), its timestamp for the quote says "Jan 31, 7:00PM EST".
This is useful. Thanks.
I don't suppose there's a way to tweak it to return the YTD performance instead of the share price? (I couldn't find a YTD quote listed on the marketwatch site.) I'm tracking a few mock mutual fund portfolios, and it's easier to use the YTD info so I don't have to worry about adjusting for dividends.
The529guy wrote: ↑Fri Feb 02, 2018 6:09 pm
Lately, I've become increasingly disappointed with Google Finance's slow updates for daily mutual fund NAV quotes in my Google Docs portfolio tracker.
Rather than do workarounds with ETF quotes, which are actually up-to-date, I've replaced my Google Finance functions and now pull all fund quotes from marketwatch.com.
Thank you! This is much better than the Vanguard only function I have been using:
Nice. I use Apple numbers for my stocks and quotes but have been very disappointed lately. Getting stock quotes originally debuted a while ago and I got real time quotes or close to it. An “update” later and now I get closenof market quotes from YESTERDAY! Really?! Can I at least get close if market quotes from today? Sigh...
venkman wrote: ↑Fri Feb 02, 2018 8:46 pm
I don't suppose there's a way to tweak it to return the YTD performance instead of the share price? (I couldn't find a YTD quote listed on the marketwatch site.) I'm tracking a few mock mutual fund portfolios, and it's easier to use the YTD info so I don't have to worry about adjusting for dividends.
Rather than find a source for an updated YTD number, I would create a cell that compares the current NAV with a hardcoded value for the fund's initial NAV for the year, and calculate the percent change.
Edit: If you want to automate pulling the 2017 year-end NAV, you could use the methodology below to import from MarketWatch's Historical Quotes tool. For VTSAX (Dec 29, 2017):
The529guy wrote: ↑Fri Feb 02, 2018 6:09 pm
Lately, I've become increasingly disappointed with Google Finance's slow updates for daily mutual fund NAV quotes in my Google Docs portfolio tracker.
Rather than do workarounds with ETF quotes, which are actually up-to-date, I've replaced my Google Finance functions and now pull all fund quotes from marketwatch.com.
venkman wrote: ↑Fri Feb 02, 2018 8:46 pm
I don't suppose there's a way to tweak it to return the YTD performance instead of the share price? (I couldn't find a YTD quote listed on the marketwatch site.) I'm tracking a few mock mutual fund portfolios, and it's easier to use the YTD info so I don't have to worry about adjusting for dividends.
Rather than find a source for an updated YTD number, I would create a cell that compares the current NAV with a hardcoded value for the fund's initial NAV for the year, and calculate the percent change.
The problem with that is the NAV will drop when the fund makes a distribution, and the dividends won't get counted toward the YTD total return.
venkman wrote: ↑Fri Feb 02, 2018 8:46 pm
I don't suppose there's a way to tweak it to return the YTD performance instead of the share price? (I couldn't find a YTD quote listed on the marketwatch site.) I'm tracking a few mock mutual fund portfolios, and it's easier to use the YTD info so I don't have to worry about adjusting for dividends.
Rather than find a source for an updated YTD number, I would create a cell that compares the current NAV with a hardcoded value for the fund's initial NAV for the year, and calculate the percent change.
The problem with that is the NAV will drop when the fund makes a distribution, and the dividends won't get counted toward the YTD total return.
Good point. I'm so used to updating my share numbers after dividend payouts, but I guess that might not make sense for mock portfolios.
Dale_G wrote: ↑Sat Feb 03, 2018 4:14 pm
Thank you for the heads up 529. I would have had no prayer of figuring this out by myself.
For some reason I get an error if the price of the fund is less than $10.00, so in that case I use BH13's technique for Vanguard funds.
Dale
Dale, great catch! Try adjusting the final number in the function from 7 to 6. I was able to get sub-$10 prices to work with this change.
To see why, keep the 7 and delete "VALUE". With an X.XX price, the last 7 characters in the cell include the colon after "Last" (see RIGHT function), which the VALUE function doesn't like.
venkman wrote: ↑Fri Feb 02, 2018 8:46 pm
I don't suppose there's a way to tweak it to return the YTD performance instead of the share price? (I couldn't find a YTD quote listed on the marketwatch site.) I'm tracking a few mock mutual fund portfolios, and it's easier to use the YTD info so I don't have to worry about adjusting for dividends.
Rather than find a source for an updated YTD number, I would create a cell that compares the current NAV with a hardcoded value for the fund's initial NAV for the year, and calculate the percent change.
Edit: If you want to automate pulling the 2017 year-end NAV, you could use the methodology below to import from MarketWatch's Historical Quotes tool. For VTSAX (Dec 29, 2017):
The529guy wrote: ↑Sat Feb 03, 2018 11:43 pm
Update: Try the WSJ's quote tool. Here's the result for VTSAX. I'm able to access the Total Returns table like this:
The529guy wrote: ↑Fri Feb 02, 2018 6:09 pm
Lately, I've become increasingly disappointed with Google Finance's slow updates for daily mutual fund NAV quotes in my Google Docs portfolio tracker.
Rather than do workarounds with ETF quotes, which are actually up-to-date, I've replaced my Google Finance functions and now pull all fund quotes from marketwatch.com.
The "Last" NAV quote (68.81) is located in the 3rd row of the 1st column. ("VTSAX" in the 1st row, "hide quote" in the 2nd row.)
If you scroll over to the end of the code above, you'll see 3,1 to import the value from that table cell.
The daily change values are in the 3rd and 4th rows of the 2nd column. You'd use 3,2 for "Change" (-1.46) and 4,2 for "Percent Change" (-2.08%).
For reference, it's the INDEX function that returns a value based on a cell's row and column.
I really appreciate all the tips in this thread.
After reading this, I put together a Google Sheets and ran into this problem today with the daily change values, with both $ and %. Today VBTLX had zero price change, and the change value and percent came back as #value!. I am guessing this is because of the zero change. Any way to fix this?
Not sure why, but this suddenly started giving me completely incorrect values for VTI only. I noticed my US allocation was suddenly 10% below target and thought that something horrible must have happened in the markets. I then calculated it myself and realized it was returning an invalid result. I couldn't figure out why, but it was doing it across all my spreadsheets. I only tested it with a few ETFs, but it worked for all but VTI.
While I liked that this method was updated faster, I have reverted to the googlefinance function given that it was very reliable even if delayed.
“There may be times when we are powerless to prevent injustice, but there must never be a time when we fail to protest.” - Elie Wiesel
Thanks, this is extremely useful and I'll eventually move my spread sheet to use this.
BTW the right function will break down for a few cases depending on the number of digits that are returned. I found that in some cases the right 7 were not enough (and too many as pointed out earlier).
VTI seems to be returning 8 characters today. 4 digits past the decimal point on the right. I'll have to ponder a solution for this.
132.6197
When set to 7 you get this:
32.6197
As a side, how often do you find yourself tweaking this when marketwatch changes the table since row/column are hard-coded?
maineminder wrote: ↑Fri Feb 09, 2018 11:05 am
VTI seems to be returning 8 characters today. 4 digits past the decimal point on the right. I'll have to ponder a solution for this.
That makes sense. I didn't spend too much time debugging it.
I am amazed there is not a simple API available that returns plain text ETF/stock/mutual fund prices!
“There may be times when we are powerless to prevent injustice, but there must never be a time when we fail to protest.” - Elie Wiesel
The "Last" NAV quote (68.81) is located in the 3rd row of the 1st column. ("VTSAX" in the 1st row, "hide quote" in the 2nd row.)
If you scroll over to the end of the code above, you'll see 3,1 to import the value from that table cell.
The daily change values are in the 3rd and 4th rows of the 2nd column. You'd use 3,2 for "Change" (-1.46) and 4,2 for "Percent Change" (-2.08%).
For reference, it's the INDEX function that returns a value based on a cell's row and column.
I really appreciate all the tips in this thread.
After reading this, I put together a Google Sheets and ran into this problem today with the daily change values, with both $ and %. Today VBTLX had zero price change, and the change value and percent came back as #value!. I am guessing this is because of the zero change. Any way to fix this?
No one responded to my question, but I figured it out myself. If you are importing price change and it is zero, you get an error. You can use the Google Sheets IFERROR formula to have the value changed to $0.00 when you receive an error for price change. Works great.
From my experience programming and in quality control I would say these are the type of things that really should be encapsulated in a visual basic function (or google app srcript).
That way the main spreadsheet contains many lines like MYGETQUOTE( "VTI" ) and you can more easily change the web scraping as needed by editing the MYGETQUOTE() function. That allows you to avoid excessive one liners and add some fine comments.
The529guy wrote: ↑Sat Feb 03, 2018 11:43 pm
Update: Try the WSJ's quote tool. Here's the result for VTSAX. I'm able to access the Total Returns table like this:
Using the INDEX function described in this thread, you can extract the YTD, 1YR, 3YR, 5YR, and 10YR total returns for the fund.
I would like to use this but instead of embedding the ticker symbol in the formula, I would like to have it pass the ticker from a cell, say A3. Anyone know how to do that?
Epsilon Delta wrote: ↑Sun Feb 11, 2018 12:46 pm
From my experience programming and in quality control I would say these are the type of things that really should be encapsulated in a visual basic function (or google app srcript).
maineminder wrote: ↑Mon Feb 12, 2018 8:41 am
munemaker The original post actually has the solution. Use the CONCAT function to build the http:... string in your importxml function.
Somehow I overlooked this until you pointed it out. I was able get get it to work.
Thanks
NEW QUESTION: I have approximately 60 IMPORTHTML statements in my spreadsheet. The imports load (i.e. update values) very, very slowly. What is the most important factor contributing to the slow update speed? Is it the internet speed or the processor speed. I am traveling and only have my lower powered Chromebook with me, which only has a Celeron processor. I wonder if this is why the slow-load? The website I am pulling the data from cannot be very busy as it is after midnight EST
maineminder wrote: ↑Mon Feb 12, 2018 8:41 am
munemaker The original post actually has the solution. Use the CONCAT function to build the http:... string in your importxml function.
Somehow I overlooked this until you pointed it out. I was able get get it to work.
Thanks
NEW QUESTION: I have approximately 60 IMPORTHTML statements in my spreadsheet. The imports load (i.e. update values) very, very slowly. What is the most important factor contributing to the slow update speed? Is it the internet speed or the processor speed. I am traveling and only have my lower powered Chromebook with me, which only has a Celeron processor. I wonder if this is why the slow-load? The website I am pulling the data from cannot be very busy as it is after midnight EST
I don’t think its internet or processor speed.
It seems related to google’s whim or ability. Sheets with many imports drag from time to time.
If you use multiple values from the same imported table, one way to reduce the web calls might be to import the table to a separate page and pick the values you want from the individual table cells on that page.
maineminder wrote: ↑Mon Feb 12, 2018 8:41 am
munemaker The original post actually has the solution. Use the CONCAT function to build the http:... string in your importxml function.
Somehow I overlooked this until you pointed it out. I was able get get it to work.
Thanks
NEW QUESTION: I have approximately 60 IMPORTHTML statements in my spreadsheet. The imports load (i.e. update values) very, very slowly. What is the most important factor contributing to the slow update speed? Is it the internet speed or the processor speed. I am traveling and only have my lower powered Chromebook with me, which only has a Celeron processor. I wonder if this is why the slow-load? The website I am pulling the data from cannot be very busy as it is after midnight EST
You are pulling in 60 webpages to render your one sheet. For each page you pull, you are just using a single number and tossing the rest. The slowness is from all this downloading and is a really inefficient use of resources, including the website. They don't like this and often have (legally binding) Terms of Service clauses that prohibit bulk crawling/scraping of their services.
Maybe you can just use the scraping for some of your fields and use GOOGLEFINANCE() calls for the rest, i.e. the ones that aren't as time sensitive (like past performance), or update quickly (like regular stocks). API help is here https://support.google.com/docs/answer/3093281?hl=en
Thanks for your comments. I am new to this and didn't even know what "scraping" was two weeks ago. I have my SS working now, and next I will work to minimize the data loads on the servers. Interestingly, there are multiple descriptions online of how the "importhtml" instruction updates. One place referred to "Spreadsheet Settings/Calculations" for update settings, which turns out not to be true. Another place said "importhtml" does not update automatically and you need to write a script and set a trigger, which I did. Then I found out "importhtml" was automatically updating every hour, and that's how it works by design, so I can delete the script. Not only that, but the SS updates even if the file is closed which is pretty cool. Now that my SS is complete, the update time does not seem so long or inconvenient. I don't really need an hourly update...just once a day after the mutual fund prices are reported would be fine. Doesn't seem like a way to turn off the hourly updates though.
I am learning a lot with this. I used to think Sheets was a lightweight program but am finding it has a lot of capability. Going to look for some online training for the more advanced features.
Thank you The529Guy - the delayed quotes were a huge frustration for me, and your formula works great. I was even able to play with the numbers at the end of the string to get the day's percent change into another column so I can track the daily fluctuation of my portfolio. Finally may leave Morningstar!
The529guy wrote: ↑Fri Feb 02, 2018 6:09 pm
Lately, I've become increasingly disappointed with Google Finance's slow updates for daily mutual fund NAV quotes in my Google Docs portfolio tracker.
Rather than do workarounds with ETF quotes, which are actually up-to-date, I've replaced my Google Finance functions and now pull all fund quotes from marketwatch.com.