hoppy08520 wrote: ↑Sun Mar 07, 2021 3:22 pm
Sorry for the delay, here it is:
Tickers Share Price Sample | GoogleSheets
See the "Read Me" tab for explanations.
What makes this spreadsheet interesting is that it pulls prices from three different services, and takes what it can get. I find that my sheet almost always works now because at least one of the three services works for every fund that I have. If one service changes how it functions (like alters the HTML structure or renames fields), then you will need to fix it or adapt. It's kind of a moving target.
...
I have shamelessly stolen your ideas here and updated a sheet I had shared a year ago that computes real-time during the trading day Mutual prices synthesized from the current movement of a paired ETF.
https://docs.google.com/spreadsheets/d/ ... sp=sharing (updated to revised link on NOV-21-2021)
It incorporates the KevinM functions for Yahoo and Morningstar (extended to return trade-date/time and current change %) as well as your iferror approach (much cleaner than my previous hacked set of byzantine calculations.)
I already had incorporated KevinM's getVanguardPriceYieldAndAttributes() function that provides loads of fund related data cleanly. Note, for VG investors, yield data, ER, etc are all well recorded and up to date using this function. I extended Kevin's VG function to include ETFs as well as Mutual Funds.
I also created a function getVanguardLastPriceYield() that returns the most recent fund price for a Vanguard Mutual Fund or CIT (in retirement plans) so I can get daily updates of my Vanguard 401(K) funds. Previously I depended upon authoritative answers only by downloading daily from Vanguard's 401K website. This is my favorite part of this sheet.
There are three basic sheets:
-
BestMFTracking - provides a similar service to what hoppy provided previously for solid up-to-date mutual fund prices.
-
EfficientTickers - using the iferror approach, ensures a reliable if not timely price based upon the synthetic pricing model described above.
-
BestTickers - queries that ensure both a reliable and most up to date price based upon the synthetic pricing model described above. Achieved by running all queries and finding that most recent errorless query. This version is the most bullet proof fund pricing sheet I have ever created.
Make a copy and enjoy. the vlookup approach for finding the prices by symbol works well with all the tabs. I used made up names for the Vanguard CITs for that lookup since they don't have symbols. you can change them as necessary if you have a VG 401K.