|
|
Screen scraping function for return YTD?
11-15-2018, 07:53 AM
|
#1
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Feb 2006
Location: Washington, DC
Posts: 11,313
|
Screen scraping function for return YTD?
I have been using the Google function "returnytd" to keep an eye on the return on my mutual funds (mostly Vanguard). I recently noticed that it returns the the total return year to date as of the end of the last quarter. That is pretty useless data. I would like to get a fund total return year to date as of the last day of trading. I get my TSP daily prices from a screen scraping function someone concocted. Is anyone aware of a screen scraping function that will pull current total return YTD from a site that tracks the data up to date? I notice that Morningstar provides the data in its performance tab > trailing total returns > daily tab in a chart that has several columns including a YTD column that is the data I want. Has anyone concocted a screen scraping function to pull that?
__________________
Idleness is fatal only to the mediocre -- Albert Camus
|
|
|
|
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!
Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!
You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!
|
11-15-2018, 02:41 PM
|
#2
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Jun 2006
Location: Boise
Posts: 7,862
|
I don't know, but I believe that there are Excel macros out there that do a similar sort of thing.
__________________
"At times the world can seem an unfriendly and sinister place, but believe us when we say there is much more good in it than bad. All you have to do is look hard enough, and what might seem to be a series of unfortunate events, may in fact be the first steps of a journey." Violet Baudelaire.
|
|
|
11-15-2018, 03:09 PM
|
#3
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Dec 2008
Location: On a hill in the Pine Barrens
Posts: 9,669
|
https://stackoverflow.com/questions/...with-importxml
That page describes an approach to solving the problem.
|
|
|
11-15-2018, 03:43 PM
|
#4
|
Thinks s/he gets paid by the post
Join Date: Aug 2013
Location: North
Posts: 4,023
|
Yes you need to use google sheets =Finance function to get the close price for Dec31 of prior year...then a function to get todays close price...then a third cell to 'do the math' to figure out the difference between the two numbers. Then divide into the Jan31 price to get a percentage.
__________________
Time > $$$ ~ 100% equities ~ FIRE @2031
|
|
|
11-15-2018, 04:10 PM
|
#5
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Feb 2006
Location: Washington, DC
Posts: 11,313
|
Quote:
Originally Posted by kgtest
Yes you need to use google sheets =Finance function to get the close price for Dec31 of prior year...then a function to get todays close price...then a third cell to 'do the math' to figure out the difference between the two numbers. Then divide into the Jan31 price to get a percentage.
|
unfortunately, that doesn’t give you total return with dividends which is what I want.
|
|
|
11-16-2018, 07:12 AM
|
#6
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Feb 2006
Location: Washington, DC
Posts: 11,313
|
Well, this stuff is way over my head since all I know about web stuff is basic HTML circa 1994. I looked at the Stack Overflow discussion and started fooling around with Chrome Dev tools. I found a URL that contains the data I want (this is for VTIAX). I convereted that to an Import XML function for Google sheets and added some table elements from the STack Overflow discussion and am able to get the data I want. Unfortunately the code returns the data I want in the cell I paste it into but adds two cells below with data I don't want. Here is the function if anyone understands this stuff:
=IMPORTXML("http://performance.morningstar.com/perform/Performance/fund/trailing-total-returns.action?&t=XNAS:VTIAX&Region=usa&culture=en-US&ops=clear&cur=&s=0P00001MJB&ndec=2&ep=true&ali g n=d&annlz=true&comparisonRemove=false&loccat=&taxa dj=&benchmarkSecId=&benchmarktype=", "//table/tbody/tr[1]/td[5]")
This is the portion that parses the table data from the URL:
", "//table/tbody/tr[1]/td[5]")
Below is a shot of the return it provides (the function is in the cell below VTIAX):
__________________
Idleness is fatal only to the mediocre -- Albert Camus
|
|
|
11-16-2018, 07:26 AM
|
#7
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Mar 2011
Posts: 8,332
|
Quote:
Originally Posted by donheff
I have been using the Google function "returnytd" to keep an eye on the return on my mutual funds (mostly Vanguard). I recently noticed that it returns the the total return year to date as of the end of the last quarter. That is pretty useless data.
|
I've noticed (and reported here) the same thing. Also the dividend date hasn't updated for two months. I'm beginning to suspect that G isn't supporting those items anymore.
The fund prices do update each night and other info as well but the YTD and dividend date seem to be not updating.
I've been forced back to M* to get my most recent YTD update
__________________
Living well is the best revenge!
Retired @ 52 in 2005
|
|
|
11-16-2018, 07:33 AM
|
#8
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Mar 2011
Posts: 8,332
|
Quote:
Originally Posted by donheff
Well, this stuff is way over my head since all I know about web stuff is basic HTML circa 1994. I looked at the Stack Overflow discussion and started fooling around with Chrome Dev tools. I found a URL that contains the data I want (this is for VTIAX). I convereted that to an Import XML function for Google sheets and added some table elements from the STack Overflow discussion and am able to get the data I want. Unfortunately the code returns the data I want in the cell I paste it into but adds two cells below with data I don't want. Here is the function if anyone understands this stuff:
=IMPORTXML("http://performance.morningstar.com/perform/Performance/fund/trailing-total-returns.action?&t=XNAS:VTIAX®ion=usa&culture=en-US&ops=clear&cur=&s=0P00001MJB&ndec=2&ep=true&alig n=d&annlz=true&comparisonRemove=false&loccat=&taxa dj=&benchmarkSecId=&benchmarktype=", "//table/tbody/tr[1]/td[5]")
This is the portion that parses the table data from the URL:
", "//table/tbody/tr[1]/td[5]")
Below is a shot of the return it provides (the function is in the cell below VTIAX):
|
I cut and pasted your formula and got nothing but error messages. Tried a few variations but like you, this is all Greek to me.
__________________
Living well is the best revenge!
Retired @ 52 in 2005
|
|
|
11-16-2018, 07:35 AM
|
#9
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Feb 2006
Location: Washington, DC
Posts: 11,313
|
Quote:
Originally Posted by marko
I've noticed (and reported here) the same thing. Also the dividend date hasn't updated for two months. I'm beginning to suspect that G isn't supporting those items anymore.
The fund prices do update each night and other info as well but the YTD and dividend date seem to be not updating.
I've been forced back to M* to get my most recent YTD update
|
You can paste my code (above) into a cell with whatever your ticker is replacing VTIAX and it will return the YTD as long as you don't mind also getting some MCSI data in the two cells below YTD. I will have to watch this function to see if it works over time.
__________________
Idleness is fatal only to the mediocre -- Albert Camus
|
|
|
11-16-2018, 07:42 AM
|
#10
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Feb 2006
Location: Washington, DC
Posts: 11,313
|
Quote:
Originally Posted by marko
I cut and pasted your formula and got nothing but error messages. Tried a few variations but like you, this is all Greek to me.
|
Somewhere in the cut and paste effort I screwed up. After VTIAX the code should be &Region try this:
=IMPORTXML("http://performance.morningstar.com/perform/Performance/fund/trailing-total-returns.action?&t=XNAS:VTIAX&Region=usa&culture=en-US&ops=clear&cur=&s=0P00001MJB&ndec=2&ep=true&ali g n=d&annlz=true&comparisonRemove=false&loccat=&taxa dj=&benchmarkSecId=&benchmarktype=", "//table/tbody/tr[1]/td[5]")
I will edit the original post
__________________
Idleness is fatal only to the mediocre -- Albert Camus
|
|
|
11-16-2018, 08:10 AM
|
#11
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Mar 2011
Posts: 8,332
|
Quote:
Originally Posted by donheff
Somewhere in the cut and paste effort I screwed up. After VTIAX the code should be &Region try this:
=IMPORTXML("http://performance.morningstar.com/perform/Performance/fund/trailing-total-returns.action?&t=XNAS:VTIAX&Region=usa&culture=en-US&ops=clear&cur=&s=0P00001MJB&ndec=2&ep=true&ali g n=d&annlz=true&comparisonRemove=false&loccat=&taxa dj=&benchmarkSecId=&benchmarktype=", "//table/tbody/tr[1]/td[5]")
I will edit the original post
|
Ok that works! I have to manually go in and change VTIAX to another fund vs "b1" (name of fund) but I can make it work by sending all that info to another sheet and then just pick the individual cell.
Thanks Don!! Been trying to find a workaround for a few weeks now
__________________
Living well is the best revenge!
Retired @ 52 in 2005
|
|
|
11-16-2018, 08:32 AM
|
#12
|
Thinks s/he gets paid by the post
Join Date: Mar 2013
Location: Coronado
Posts: 3,655
|
Quote:
Originally Posted by donheff
Unfortunately the code returns the data I want in the cell I paste it into but adds two cells below with data I don't want. Here is the function if anyone understands this stuff:
=IMPORTXML("http://performance.morningstar.com/perform/Performance/fund/trailing-total-returns.action?&t=XNAS:VTIAX&Region=usa&culture=en-US&ops=clear&cur=&s=0P00001MJB&ndec=2&ep=true&ali g n=d&annlz=true&comparisonRemove=false&loccat=&taxa dj=&benchmarkSecId=&benchmarktype=", "//table/tbody/tr[1]/td[5]")
This is the portion that parses the table data from the URL:
", "//table/tbody/tr[1]/td[5]")
|
There are three "tbody" tags in the data, and your xpath query doesn't specify which one to extract. That's why your output includes 3 rows. Since you only want the data from the first one, you can just add an index on the tbody in your query like so:
"//table/tbody [1]/tr[1]/td[5]"
|
|
|
11-16-2018, 08:38 AM
|
#13
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Mar 2011
Posts: 8,332
|
Quote:
Originally Posted by cathy63
There are three "tbody" tags in the data, and your xpath query doesn't specify which one to extract. That's why your output includes 3 rows. Since you only want the data from the first one, you can just add an index on the tbody in your query like so:
"//table/tbody[1]/tr[1]/td[5]"
|
Fantastic! Now.....do you know how I might be able to use "b1" (name of fund in cell b1) instead of having to manually add the fund name in each query?
(this resources of site never ceases to amaze me)
__________________
Living well is the best revenge!
Retired @ 52 in 2005
|
|
|
11-16-2018, 08:39 AM
|
#14
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Feb 2006
Location: Washington, DC
Posts: 11,313
|
Quote:
Originally Posted by cathy63
There are three "tbody" tags in the data, and your xpath query doesn't specify which one to extract. That's why your output includes 3 rows. Since you only want the data from the first one, you can just add an index on the tbody in your query like so:
"//table/tbody[1]/tr[1]/td[5]"
|
Thank you. It helps to have people who know what they are doing. Marko - we can skip the extra sheets and cell references.
__________________
Idleness is fatal only to the mediocre -- Albert Camus
|
|
|
11-16-2018, 08:43 AM
|
#15
|
Thinks s/he gets paid by the post
Join Date: Mar 2013
Location: Coronado
Posts: 3,655
|
Try this:
=IMPORTXML(concatenate("http://performance.morningstar.com/perform/Performance/fund/trailing-total-returns.action?&t=XNAS:",b1,"&Region=usa&culture=en-US&ops=clear&cur=&s=0P00001MJB&ndec=2&ep=true&ali g n=d&annlz=true&comparisonRemove=false&loccat=&taxa dj=&benchmarkSecId=&benchmarktype="), "//table/tbody[1]/tr[1]/td[5]")
I have not tested it, but I bolded the changes, so if I misplaced a parenthesis or something, you can probably find and fix it easily.
|
|
|
11-16-2018, 08:43 AM
|
#16
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Mar 2011
Posts: 8,332
|
Quote:
Originally Posted by donheff
Thank you. It helps to have people who know what they are doing. Marko - we can skip the extra sheets and cell references.
|
Right...hoping for an answer to
"Now.....do you know how I might be able to use "b1" (name of fund in cell b1) instead of having to manually add the fund name in each query?" before making that change.
__________________
Living well is the best revenge!
Retired @ 52 in 2005
|
|
|
11-16-2018, 08:45 AM
|
#17
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Mar 2011
Posts: 8,332
|
Quote:
Originally Posted by cathy63
Try this:
=IMPORTXML(concatenate("http://performance.morningstar.com/perform/Performance/fund/trailing-total-returns.action?&t=XNAS:",b1,"&Region=usa&culture=en-US&ops=clear&cur=&s=0P00001MJB&ndec=2&ep=true&ali g n=d&annlz=true&comparisonRemove=false&loccat=&taxa dj=&benchmarkSecId=&benchmarktype="), "//table/tbody[1]/tr[1]/td[5]")
I have not tested it, but I bolded the changes, so if I misplaced a parenthesis or something, you can probably find and fix it easily.
|
It works!!! Thank you so much! I just cut and pasted this and did a trial. Perfect.
__________________
Living well is the best revenge!
Retired @ 52 in 2005
|
|
|
11-16-2018, 08:52 AM
|
#18
|
Moderator
Join Date: Oct 2010
Posts: 10,622
|
Probably something like
...action?&t=XNAS:"&b1&"&Region...
|
|
|
11-16-2018, 08:55 AM
|
#19
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Feb 2006
Location: Washington, DC
Posts: 11,313
|
Quote:
Originally Posted by marko
It works!!! Thank you so much! I just cut and pasted this and did a trial. Perfect.
|
Yes it does. Perfect. Now Morningstar will change the table like the TSP did recently. I figured the TSP out only to have them fix it back the way it used to be
__________________
Idleness is fatal only to the mediocre -- Albert Camus
|
|
|
11-16-2018, 08:57 AM
|
#20
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Mar 2011
Posts: 8,332
|
Now I can sleep at night!!
Thanks Donheff for getting the ball rolling.
__________________
Living well is the best revenge!
Retired @ 52 in 2005
|
|
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
Thread Tools |
|
Display Modes |
Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
» Recent Threads
|
|
|
|
|
|
|
|
|
|
|
|
|
» Quick Links
|
|
|