Screen scraping function for return YTD?

[sengsational]

Ironic how it takes a lot of effort to automate things to save effort.

It's the nature of the work, though. Manually slogging through 200 clicks on 8 web sites that takes 1/2 hour? I would probably decide it wasn't that important and quit. Tweaking the script to get it working again, that's a puzzle that, even if it takes longer, is something I'll keep doing "for the fun of it"

 

[sengsational]

Another solution that would be logical and easy for businesses would be a uniform data format that all institutions could make available. So if every company that managed your money (investment, bank, credit card, etc) signed on to a standard format (I'm thinking XML), then it would be simple for a program to do any analysis you need. It also would be good to have a read-only sign-on to financial sites. That would be the only way I'd let some 3rd party pull my financials together. It would save the scraping if I could put in a date range and get all transactions and balances in a universal format. They probably would not so it without legislation, though.

 

[donheff]

I don't know about Google Finance functions. CloseYest, which returns the previous day's closing price seems fine. But now I noticed that return52, which is supposed to return total returns over the past 52 weeks is way off. It was returning numbers that are way too high. Using the same Morningstar XML import statement as for YTD total return you can get the figure for 1 year, 3 years, 5 years, just by changing the cell data number (TD).

I wonder of Google has stopped supporting some of these functions.
I posted a question on Google Forums to see if I can sort this and the ytd issue out.

 

[target2019]

Ironic how it takes a lot of effort to automate things to save effort. I wonder if, at least for stock quotes, there is some server somewhere which just gives raw text or CSV or XML data so people don't have to screen scrape. If so, it's probably not free to the public.

I doubt we will ever find an open source for that historical data. It is huuuuge.
Even though it takes a bit of research, writing, and testing, it is worth the effort to build new functions to fetch data. Your first attempt may take days, but technical knowledge builds (html, json, xml, etc.). Successive attempts to solve other problems grow shorter. It is very satisfying to conquer one of these challenges. Keeps the brain involved.

It's the nature of the work, though. Manually slogging through 200 clicks on 8 web sites that takes 1/2 hour? I would probably decide it wasn't that important and quit. Tweaking the script to get it working again, that's a puzzle that, even if it takes longer, is something I'll keep doing "for the fun of it"

Some have a collection of traits that makes this possible. Investigate the problem, understand discrete parts, and most importantly, persist through the many failures. For whatever reason, when you see #REF, it is a personal challenge. Most users feel the failure and give up.

 

[marko]

It's the nature of the work, though. Manually slogging through 200 clicks on 8 web sites that takes 1/2 hour? I would probably decide it wasn't that important and quit. Tweaking the script to get it working again, that's a puzzle that, even if it takes longer, is something I'll keep doing "for the fun of it"

Your first attempt may take days, but technical knowledge builds (html, json, xml, etc.). Successive attempts to solve other problems grow shorter. It is very satisfying to conquer one of these challenges. Keeps the brain involved.


Some have a collection of traits that makes this possible. Investigate the problem, understand discrete parts, and most importantly, persist through the many failures. For whatever reason, when you see #REF, it is a personal challenge. Most users feel the failure and give up.

Some people do crossword puzzles, some people do jigsaw puzzles or other brain-work.

I find twisting Excel (Google Sheets) to it's limit a nice, challenging and fun brain exercise. Plus at the end I have a useful tool.

Long live =Vlookup, =Hlookup, =Countif, =Sumif, =Importdata and =INDEX(K:K,MAX(ROW(K:K)*(K:K<>""))) !!

 

[marko]

I don't know about Google Finance functions. CloseYest, which returns the previous day's closing price seems fine. But now I noticed that return52, which is supposed to return total returns over the past 52 weeks is way off. It was returning numbers that are way too high. Using the same Morningstar XML import statement as for YTD total return you can get the figure for 1 year, 3 years, 5 years, just by changing the cell data number (TD).

I wonder of Google has stopped supporting some of these functions.
I posted a question on Google Forums to see if I can sort this and the ytd issue out.

I've also been searching for answers and have come to the conclusion that Google only gives little more than day-end quotes. Sad.

What number do you use for 1, 3, 5 years? 52? 156? etc.

Now, if I could find how to import M* dividend TTM yield numbers....I'm almost at the point of just going to M* and do this all manually at month's end...not the daily updates I was used to but maybe they're doing me a favor (?)

 

[target2019]

During a trading day, this does update continuously:
=GoogleFinance(C6, "price")
where C6 would be the stock symbol.

 

[donheff]

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.

I've also been searching for answers and have come to the conclusion that Google only gives little more than day-end quotes. Sad.

What number do you use for 1, 3, 5 years? 52? 156? etc.

Now, if I could find how to import M* dividend TTM yield numbers....I'm almost at the point of just going to M* and do this all manually at month's end...not the daily updates I was used to but maybe they're doing me a favor (?)

Marko. I use the same code as Cathy modified above where B1 is the relative reference to the cell with the ticker symbol. As written it returns Return YTD but the same Morningstar daily trailing returns table displays the 1,3, and 5 year returns. To get them you just change the td[5] entry to td[6], td[7], and td[8] respectively.

 

[target2019]

Just a reminder-
If you enter your portfolio at M* (free account), you can easily bring up screens which show your specific symbols and all of the data of interest. Export to Excel is available.

 

[SecondCor521]

Another solution that would be logical and easy for businesses would be a uniform data format that all institutions could make available. So if every company that managed your money (investment, bank, credit card, etc) signed on to a standard format (I'm thinking XML), then it would be simple for a program to do any analysis you need. It also would be good to have a read-only sign-on to financial sites. That would be the only way I'd let some 3rd party pull my financials together. It would save the scraping if I could put in a date range and get all transactions and balances in a universal format. They probably would not so it without legislation, though.

This pretty well describes how Quicken works, except (a) they use their own format; it's not XML but it's probably similar in design, and (b) they charge a fee for their software.

Personally I use Quicken 2018 one step update that pulls almost all of my account balances and transactions. When I feel like it, I semi-manually copy some of that data into my big Excel spreadsheet - basically account balances and my last six month's spending. I haven't timed myself, but doing everything takes about 5 minutes.

I could automate the export to Excel but I don't feel the need. Maybe I should return my engineer badge.

 

[donheff]

If anyone is using the import xml from Morningstar and wants to get ETF data, they use a different table. This worked for VTI:

=IMPORTXML("http://performance.morningstar.com/perform/Performance/cef/trailing-total-returns.action?&t=ARCX:VTI&region=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&ndec=2&ep=true&align=d&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=", "//table/tbody[1]/tr[1]/td[5]")


With the concatenate addition to pull the ticker from a cell:

=IMPORTXML(Concatenate("http://performance.morningstar.com/perform/Performance/cef/trailing-total-returns.action?&t=ARCX:",K28,"&region=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&ndec=2&ep=true&align=d&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype="), "//table/tbody[1]/tr[1]/td[5]")

 

[sengsational]

I could automate the export to Excel but I don't feel the need. Maybe I should return my engineer badge.

Such drastic steps needn't be be undertaken!

While I see that often various sites will offer exports in various formats, that's kind of the problem: each institution defines whatever format they feel like, gives the user more or less choice in what's in the document (specific date ranges come to mind), more or less specific data, and buries the download function in various places in their web site.

In the world where I was king, this would be resolved through a single consistent format, and access methodology. Each institution would define the location of it's reporting portal. Forcing upon them an authentication standard would be tricky, so that might be left alone at first, but once authenticated, there would be a single API that every financial institution would implement. All institutions would implement the same format, which captured every aspect of every kind of transaction, balance, transaction ID and also the API would accept date ranges.

We already have laws on the books that require institutions to include certain data in their monthly statements, be let's face it, monthly statements were the best we could do when systems were what they used to be. We've moved on, and we haven't got to the next stage yet in reporting. It could be that there will be a defacto standard, and in fact that's probably where this will go, since those that purport to represent us are busy collecting money for their next campaign, and the chances of me becoming king are rather slim.

 

[donheff]

I've also been searching for answers and have come to the conclusion that Google only gives little more than day-end quotes. Sad.

What number do you use for 1, 3, 5 years? 52? 156? etc.

Now, if I could find how to import M* dividend TTM yield numbers....I'm almost at the point of just going to M* and do this all manually at month's end...not the daily updates I was used to but maybe they're doing me a favor (?)

Marko. I have been messing around with this and have gotten pretty good at using Chrome development tools to identify the table data URLs M* feeds into its pages. I learned that various ETFs use different exchanges so the import xml function has to be tailored to the ETF. I am not sure what you are looking for on TTM is it from this page.

 

[marko]

Marko. I have been messing around with this and have gotten pretty good at using Chrome development tools to identify the table data URLs M* feeds into its pages. I learned that various ETFs use different exchanges so the import xml function has to be tailored to the ETF. I am not sure what you are looking for on TTM is it from this page.

Thanks Don. An Happy Thanksgiving!

The only thing I'm now missing is 'trailing twelve months' (TTM) of dividend yield. GF used to support it ("yieldpct") but it doesn't look like they do anymore...very outdated data.

I can manage through life without it as I enter my own funds manually in dollars each month but I could use it for a comparison tool I built.

 

[donheff]

Thanks Don. An Happy Thanksgiving!

The only thing I'm now missing is 'trailing twelve months' (TTM) of dividend yield. GF used to support it ("yieldpct") but it doesn't look like they do anymore...very outdated data.

I can manage through life without it as I enter my own funds manually in dollars each month but I could use it for a comparison tool I built.

Well, if you find anyplace that publishes the data you want in a table let me know and I will see if I can figure out how to grab the data.

 

[marko]

Well, if you find anyplace that publishes the data you want in a table let me know and I will see if I can figure out how to grab the data.

What I'm trying to find is "% Dividend yield ttm" that M* publishes in portfolio view

 

[donheff]

What I'm trying to find is "% Dividend yield ttm" that M* publishes in portfolio view

That one is beyond me. I couldn't find the source.

 

[marko]

That one is beyond me. I couldn't find the source.

No prob!! Thanks for the effort.
Hope you had a good holiday and didn't spend the day looking for this.

 

[target2019]

What I'm trying to find is "% Dividend yield ttm" that M* publishes in portfolio view

That one is beyond me. I couldn't find the source.

What you see in your portfolio view is not available to others.
I see TTM yield % on the Dividends tab after looking up stock symbol.
For mutual fund, it is on the quote tab.
Today there is a new notice about fund page changing, and looks like TTM will no longer be listed on M*.

 

[marko]

What you see in your portfolio view is not available to others.
I see TTM yield % on the Dividends tab after looking up stock symbol.
For mutual fund, it is on the quote tab.
Today there is a new notice about fund page changing, and looks like TTM will no longer be listed on M*.

Dividend yield TTM is a key component of fund/stock data. Can you direct me to this? I find this difficult to understand why they'd discontinue list a fund/stocks TTM yield.

 

[target2019]

Dividend yield TTM is a key component of fund/stock data. Can you direct me to this? I find this difficult to understand why they'd discontinue list a fund/stocks TTM yield.

They're just not gonna give it for free on the public page for mutual funds, or at least that's what I guess.
I looked up VTSAX, and it was in the upper left of first table.
Just above is a notice to click for preview of new fund page. I don't see TTM anywhere on the new public page(s). Maybe I just could not find it.

 

[marko]

They're just not gonna give it for free on the public page for mutual funds, or at least that's what I guess.
I looked up VTSAX, and it was in the upper left of first table.
Just above is a notice to click for preview of new fund page. I don't see TTM anywhere on the new public page(s). Maybe I just could not find it.

Ok. I'm not seeing that because I'm a premium (paying) member.

 

[donheff]

What you see in your portfolio view is not available to others.
I see TTM yield % on the Dividends tab after looking up stock symbol.
For mutual fund, it is on the quote tab.
Today there is a new notice about fund page changing, and looks like TTM will no longer be listed on M*.

OK. Looking at VTSAX on the quote tab I can see that it is loading TTM yield % in a table with this url. Constructing an =importxml query for Googlesheets I can get the data but I don't know enoough about the syntax to get only the data. I get three cells, one showing text "TTM Yield," the one next to it returning the % from the table, and the one below it showing the text "Front Load."

I tried using Cathy's modification to narrow it down but I still get the extra data. Cathy63 if you are still following, can you narrow this down?

=IMPORTXML("https://quotes.morningstar.com/fundq/c-header?&t=XNAS:VTSAX&region=usa&culture=en-US&version=RET&cur=&test=QuoteiFrame", "//table/tbody[1]/tr[1]/td[1]")

Whether I use /tr or /tr[1] I still get extraneous data.

 

[target2019]

Ok. I'm not seeing that because I'm a premium (paying) member.

I have a portfolio I can get to through "My Portfolio" on the first M* page when I log in. I created a custom view last week to add "% Yield Dividend TTM" to a view.

If you log out, then what you see will be different on M*.

I do see TTM on this page at yahoo:
https://finance.yahoo.com/quote/VZ/key-statistics?p=VZ

but could not find for mutual fund.

 

[donheff]

I narrowed it down a little by adding /span to the query. Now it returns the % in the cell with the formula but still returns the text "Front Load" in the cell below.

=IMPORTXML("https://quotes.morningstar.com/fundq/c-header?&t=XNAS:VTSAX&region=usa&culture=en-US&version=RET&cur=&test=QuoteiFrame", "//table/tbody[1]/tr[1]/td[1]/span")

It works for VTI also but it has a different table structure and so far I can only get it to return some goobledygook with the yield in the cell below:

=IMPORTXML("https://etfs.morningstar.com/etfq/quote-banner?&t=ARCX:VTI&region=usa&culture=en-US&version=RET&cur=&test=QuoteiFrame&e=eyJlbmMiOiJBMTI4R0NNIiwiYWxnIjoiUlNBLU9BRVAifQ.P4uPJX5PajdOYbzhWxMOJrjFXS4aNrDDONDV9_utUuEQy1ki5DGw62tS5wgj56ELPTBEAzRWWJ9StootAmhSB5QtjpDW6A08N7QaZPcJ4eJ-LsgOOnhsOs0hC6TEDE8F2EcxJwlDt3H-aydYN2KmEKK7MyFe6QyvO4A4Kla5CPE.1rFZvFBp7pG9SYjZ.6lTz5KXYpz0EdOsQ9PxTy3gCYcdap1wx9tLP6t7AVnLs7H5_M0hMsEnn98snA85K6W8h7Jz4gIqg0gH4PivqRDKQ0ZSye4J6Q6AIIIvORV4FptwDY5ysw6dTkuKTK9Qe6MHQzL2YiTQN6436rep2pZqFrHlRCTRs_rmbtGaW0QGQGIv5lMoc6JS8YAHPxv5_TY390HeoRnTLnlldzJhFQgVyDmvW0_g5K832EdE.v3u2HDkDsQLIxias-8R_fQ&_=1542989580222", "//table/tbody[1]/tr[1]/td[7]/span")

As with the earlier queries, mutual funds can be replicated by pasting the appropriate ticker in the expression. For ETFs each URL is different.