Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Screen scraping function for return YTD?
Old 11-15-2018, 08:53 AM   #1
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
donheff's Avatar
 
Join Date: Feb 2006
Location: Washington, DC
Posts: 9,067
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?
__________________

__________________
Every man is, or hopes to be, an Idler. -- Samuel Johnson
donheff is offline   Reply With Quote
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!

Old 11-15-2018, 03:41 PM   #2
Thinks s/he gets paid by the post
SecondCor521's Avatar
 
Join Date: Jun 2006
Location: Boise
Posts: 2,970
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.
SecondCor521 is offline   Reply With Quote
Old 11-15-2018, 04:09 PM   #3
Thinks s/he gets paid by the post
target2019's Avatar
 
Join Date: Dec 2008
Posts: 4,360
https://stackoverflow.com/questions/...with-importxml
That page describes an approach to solving the problem.
target2019 is online now   Reply With Quote
Old 11-15-2018, 04:43 PM   #4
Thinks s/he gets paid by the post
 
Join Date: Aug 2013
Location: North
Posts: 1,494
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.
__________________
AA (Stock/Bond/Cash ): 99/0/1% MIX (Small/Mid/Large): 50/25/25% BLEND(US/Foreign): 100/0%, REIT (Real Estate Equity): 50% of Assets

FIRE in 2031 @ 50yrs old (+/- 2yrs) w/ a hypothetical $2.5mil portfolio, 3 appreciated homes worth $1.0mil and rental income to fund my gap years until RMD. Assets will go to an inherited IRA where I plan on watching the investments grow until I die or the trust gets executed.
kgtest is offline   Reply With Quote
Old 11-15-2018, 05:10 PM   #5
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
donheff's Avatar
 
Join Date: Feb 2006
Location: Washington, DC
Posts: 9,067
Quote:
Originally Posted by kgtest View Post
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.
donheff is offline   Reply With Quote
Old 11-16-2018, 08:12 AM   #6
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
donheff's Avatar
 
Join Date: Feb 2006
Location: Washington, DC
Posts: 9,067
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):
Attached Images
File Type: jpg VTIAX.JPG (10.5 KB, 211 views)
__________________
Every man is, or hopes to be, an Idler. -- Samuel Johnson
donheff is offline   Reply With Quote
Old 11-16-2018, 08:26 AM   #7
Thinks s/he gets paid by the post
 
Join Date: Mar 2011
Posts: 4,978
Quote:
Originally Posted by donheff View Post
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
marko is offline   Reply With Quote
Old 11-16-2018, 08:33 AM   #8
Thinks s/he gets paid by the post
 
Join Date: Mar 2011
Posts: 4,978
Quote:
Originally Posted by donheff View Post
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&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
marko is offline   Reply With Quote
Old 11-16-2018, 08:35 AM   #9
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
donheff's Avatar
 
Join Date: Feb 2006
Location: Washington, DC
Posts: 9,067
Quote:
Originally Posted by marko View Post
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.
__________________
Every man is, or hopes to be, an Idler. -- Samuel Johnson
donheff is offline   Reply With Quote
Old 11-16-2018, 08:42 AM   #10
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
donheff's Avatar
 
Join Date: Feb 2006
Location: Washington, DC
Posts: 9,067
Quote:
Originally Posted by marko View Post
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
__________________
Every man is, or hopes to be, an Idler. -- Samuel Johnson
donheff is offline   Reply With Quote
Old 11-16-2018, 09:10 AM   #11
Thinks s/he gets paid by the post
 
Join Date: Mar 2011
Posts: 4,978
Quote:
Originally Posted by donheff View Post
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
marko is offline   Reply With Quote
Old 11-16-2018, 09:32 AM   #12
Full time employment: Posting here.
 
Join Date: Mar 2013
Location: Coronado
Posts: 898
Quote:
Originally Posted by donheff View Post
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]"
cathy63 is offline   Reply With Quote
Old 11-16-2018, 09:38 AM   #13
Thinks s/he gets paid by the post
 
Join Date: Mar 2011
Posts: 4,978
Quote:
Originally Posted by cathy63 View Post
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
marko is offline   Reply With Quote
Old 11-16-2018, 09:39 AM   #14
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
donheff's Avatar
 
Join Date: Feb 2006
Location: Washington, DC
Posts: 9,067
Quote:
Originally Posted by cathy63 View Post
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.
__________________
Every man is, or hopes to be, an Idler. -- Samuel Johnson
donheff is offline   Reply With Quote
Old 11-16-2018, 09:43 AM   #15
Full time employment: Posting here.
 
Join Date: Mar 2013
Location: Coronado
Posts: 898
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.
cathy63 is offline   Reply With Quote
Old 11-16-2018, 09:43 AM   #16
Thinks s/he gets paid by the post
 
Join Date: Mar 2011
Posts: 4,978
Quote:
Originally Posted by donheff View Post
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
marko is offline   Reply With Quote
Old 11-16-2018, 09:45 AM   #17
Thinks s/he gets paid by the post
 
Join Date: Mar 2011
Posts: 4,978
Quote:
Originally Posted by cathy63 View Post
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
marko is offline   Reply With Quote
Old 11-16-2018, 09:52 AM   #18
Thinks s/he gets paid by the post
sengsational's Avatar
 
Join Date: Oct 2010
Posts: 4,657
Probably something like


...action?&t=XNAS:"&b1&"&Region...
sengsational is offline   Reply With Quote
Old 11-16-2018, 09:55 AM   #19
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
donheff's Avatar
 
Join Date: Feb 2006
Location: Washington, DC
Posts: 9,067
Quote:
Originally Posted by marko View Post
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
__________________
Every man is, or hopes to be, an Idler. -- Samuel Johnson
donheff is offline   Reply With Quote
Old 11-16-2018, 09:57 AM   #20
Thinks s/he gets paid by the post
 
Join Date: Mar 2011
Posts: 4,978
Quote:
Originally Posted by donheff View Post
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
Now I can sleep at night!!

Thanks Donheff for getting the ball rolling.
__________________

__________________
Living well is the best revenge!
Retired @ 52 in 2005
marko is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Scraping By on $250K/Year Amethyst FIRE and Money 105 03-23-2011 08:03 PM
Scraping off the dependents cute fuzzy bunny Health and Early Retirement 5 07-13-2008 03:11 PM
Scraping by on $150,000 a year Milton FIRE and Money 11 10-13-2007 03:43 PM
Scraping Wallpaper - - Oh, Joy? :) W2R Other topics 41 09-24-2007 11:38 AM
What's Your YTD Return? Tommy_Dolitte Young Dreamers 53 12-02-2004 07:10 AM

» Quick Links

 
All times are GMT -6. The time now is 04:29 AM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2018, vBulletin Solutions, Inc.