Yahoo format changed again, affecting spreadsheet import

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
User avatar
dave.d
Posts: 935
Joined: Mon Mar 19, 2007 10:30 pm
Location: Richmond, VA

Yahoo format changed again, affecting spreadsheet import

Post by dave.d » Fri Mar 25, 2011 10:21 am

Yahoo Finance has changed the format of their pages showing multiple stock or mutual fund quotes. This is causing my investment spreadsheet, which imports data from such a page, not to work.

It's stacking two price quotes right next to each other, e.g. I got an S&P 500 quote of "1312.5699461,312.57". Has anyone figured out how to deal with this? Or know anything about what they are doing or why?

A few weeks ago they had changed the format for a day or two and then switched back to the old way.
Value-based allocation: recently 23% stocks @PE10=27 and real bond yields approx. zero

hsv_climber
Posts: 3969
Joined: Tue Sep 22, 2009 7:56 pm

Post by hsv_climber » Fri Mar 25, 2011 10:31 am

Last year I've switched to Geico... I mean google -> finance and never looked back.

matt
Posts: 2305
Joined: Sun Mar 04, 2007 3:47 pm

Post by matt » Fri Mar 25, 2011 10:33 am

Back in 2003 or so, I had an import spreadsheet set up to pull data from Yahoo Finance. It never went more than 2 or 3 weeks before something wasn't working and I had to re-enter some formulas and re-save the file. I didn't know if it was an Excel problem or a Yahoo problem, but it was really annoying. Sounds like similar problems remain, so my guess is whatever has always been will always be.

Tramper Al
Posts: 3628
Joined: Thu Oct 18, 2007 11:42 am

Post by Tramper Al » Fri Mar 25, 2011 10:34 am

xxx
Last edited by Tramper Al on Tue Apr 19, 2011 7:36 pm, edited 1 time in total.

User avatar
Ted Valentine
Posts: 1556
Joined: Tue Jul 10, 2007 10:28 am
Location: Music City USA

Post by Ted Valentine » Fri Mar 25, 2011 10:45 am

I have an excel spreadsheet I found on the internet and have since modified that pulls from yahoo. It still works ok.

The spreadsheet uses the following source code to grab daily values:

download.finance.yahoo.com/d/quotes.csv?s=VTSAX+VTIAX+VIPSX+VISVX+_&f=nl1vc1d1t1jk

Add http:// in front of that above to make it work

Edit: I'm trying to get the entire link code above to show up but the forum auto converts it to a hyperlink
Last edited by Ted Valentine on Fri Mar 25, 2011 10:52 am, edited 4 times in total.
Although our intellect always longs for clarity and certainty, our nature often finds uncertainty fascinating.

User avatar
dave.d
Posts: 935
Joined: Mon Mar 19, 2007 10:30 pm
Location: Richmond, VA

Post by dave.d » Fri Mar 25, 2011 10:45 am

I'm using OpenOffice Spreadsheet, and I've had few problems over the past 3 or so years... until these last couple of format changes. I may have to try using Google Finance instead.

I expect I could use GetQuote(ticker) (which I believe is an OpenOffice thing, not available in Excel although Excel has a similar add-on) but I think that retrieves quotes individually. That is noticably slower than linking to a page that gets multiple quotes at once.
Value-based allocation: recently 23% stocks @PE10=27 and real bond yields approx. zero

User avatar
CyberBob
Posts: 3184
Joined: Tue Feb 20, 2007 2:53 pm

Post by CyberBob » Fri Mar 25, 2011 11:21 am

dave.d wrote:I expect I could use GetQuote(ticker) (which I believe is an OpenOffice thing, not available in Excel although Excel has a similar add-on) but I think that retrieves quotes individually. That is noticably slower than linking to a page that gets multiple quotes at once.
The GetQuote extension is what I was going to suggest. You are correct that if you have a large number of quotes on a spreadsheet that it can be slower getting them individually when you first open the spreadsheet, but on the plus side, since the extension is used just like any other formula, it's very simple to use.

Bob

User avatar
Dan Moroboshi
Posts: 866
Joined: Sat Jul 07, 2007 7:10 pm

Post by Dan Moroboshi » Fri Mar 25, 2011 11:57 am

Ted Valentine wrote:I have an excel spreadsheet I found on the internet and have since modified that pulls from yahoo. It still works ok.

The spreadsheet uses the following source code to grab daily values:

download.finance.yahoo.com/d/quotes.csv?s=VTSAX+VTIAX+VIPSX+VISVX+_&f=nl1vc1d1t1jk

Add http:// in front of that above to make it work

Edit: I'm trying to get the entire link code above to show up but the forum auto converts it to a hyperlink
Use the "Code" tag. When you write the post like this:

Code: Select all

[code]http://download.finance.yahoo.com/d/quotes.csv?s=VTSAX+VTIAX+VIPSX+VISVX+_&f=nl1vc1d1t1jk
[/code]

It will be displayed like this:

Code: Select all

http://download.finance.yahoo.com/d/quotes.csv?s=VTSAX+VTIAX+VIPSX+VISVX+_&f=nl1vc1d1t1jk

User avatar
dratkinson
Posts: 4180
Joined: Thu Jul 26, 2007 6:23 pm
Location: Centennial CO

Post by dratkinson » Fri Mar 25, 2011 12:36 pm

I use this to get fund data from Yahoo.

Gummy's Yahoo download macro:
http://www.gummy-stuff.org/Yahoo-data.h ... r_TolQxhiA

It is a good idea to include the "s" tag to ensure the returned data "symbol" is the same as requested. (An invalid data request returns nothing, and causes mis-aligned data rows. With the "s" tag and returned "symbol", you will notice this error.)

Above documentation suggests making the "s" tag the last in the request string because some returned symbols include commas... which causes problems in a *.csv file. By putting the "s" tag last in the request string, any returned symbols containing commas are placed last on the returned data row and so are not a problem.
Last edited by dratkinson on Fri Mar 25, 2011 12:47 pm, edited 1 time in total.

earlyout
Posts: 1349
Joined: Tue Feb 20, 2007 5:24 pm

Post by earlyout » Fri Mar 25, 2011 12:45 pm

Ted Valentine wrote:I have an excel spreadsheet I found on the internet and have since modified that pulls from yahoo. It still works ok.

The spreadsheet uses the following source code to grab daily values:

download.finance.yahoo.com/d/quotes.csv?s=VTSAX+VTIAX+VIPSX+VISVX+_&f=nl1vc1d1t1jk

Add http:// in front of that above to make it work

Edit: I'm trying to get the entire link code above to show up but the forum auto converts it to a hyperlink
I first saw this code for Excel from Gummy 5 or 6 years ago. The "download." before the finance.yahoo is not necessary. It works well today just as it has worked well over the past several years.

EO

Khuzud
Posts: 45
Joined: Wed Dec 29, 2010 11:16 am

Post by Khuzud » Fri Mar 25, 2011 1:04 pm

Does anyone have a source for indices with total return?

I created a synthetic benchmark to match my portfolio's overall asset allocation. 65/35 using total returns from US stock, and Barclay Aggregate Capital.

But the only source I could find for those numbers was on Morningstar. I have to bring up the Growth of 10k chart, and then hover my mouse over the right date, and then copy the numbers to my spreadsheet.

I'd really like a link that would give me those numbers in a format I could import directly. Anyone know of one?

User avatar
CyberBob
Posts: 3184
Joined: Tue Feb 20, 2007 2:53 pm

Post by CyberBob » Fri Mar 25, 2011 1:35 pm

Khuzud wrote:Does anyone have a source for indices with total return?
Simba's backtest spreadsheet has some very useful data.

Bob

User avatar
Ice-9
Posts: 1266
Joined: Wed Oct 15, 2008 12:40 pm
Location: Rockville, MD

Post by Ice-9 » Fri Mar 25, 2011 1:36 pm

Until about a year ago I was using an Excel spreadsheet that queried data from Yahoo, similar to the OP. I just checked that old document, and it still works, though Yahoo changing its formatting on occasion would throw everything up or down a couple lines from time to time. I didn't pull individual numbers for each cell, but rather an entire table for my holdings into a separate spreadsheet in my Excel document, then referred to cells in that spreadsheet in a different spreadsheet within the same document for my personal asset allocation.

Since about a year ago, instead I've been using a gmail spreadsheet document with two sheets just like my old Excel document. The difference is the sheet that pulls the data gets individual security information from Google Finance for each cell.

I no longer have to worry about the occasional Yahoo reformatting of their web page, but I've found that every so often for just a day or two one security won't pull information correctly. For example, two weeks ago, Vanguard Emerging Markets Fund would not show price information even though it showed up fine on finance.google.com. The problem usually fixes itself, as I said, the next day. It's annoying, but at least I don't have to reorganize the spreadsheet every time it happens.

User avatar
LH
Posts: 5490
Joined: Wed Mar 14, 2007 2:54 am

Post by LH » Sat Mar 26, 2011 3:31 am

Here is how I deal with it in openoffice.

Here is the value I have to deal with:

120.980003120.98

I have a column of these price values.
I make another column that points to the above column, consisting of
=LEFT(AA2;5)

where the above 120.980003120.98 value is the AA2 cell.

I then make another column, that points to the =LEFT(AA2;5) column.

=VALUE(AA29)

so above the zz29=LEFT(AA2;5)

These can be combined I would think, but it took me a while to figure out what to use to transform it, so I broke it up into parts. Most likely a single cell of

=VALUE(LEFT(AA2;5))

Would convert the raw data of
120.980003120.98

Anyway, today, the stupid values shifted over one cell, but all I had to do, was change the first cell of the column from =VALUE(AA29) to =VALUE(AB29) then copy and past that overtop of the ones below it, and viola, everything back to normal.

minimally painful, but this time, took me only about 1 minute to get it working again.

I half bet Yahoo realizes people are doing this with thier spreadsheets which do NOT of course read their advertisements, and maybe yahoo advertisers started realizing this, and where going, wait a minute, I am not paying x per hit when 50 percent(whatever) hits are a friggin spreadsheet........

My theory, likely wrong.

Anyway, sorry the above explanation sucks, but I am a spreadsheet hack at best.

Good luck,

LH

User avatar
kcyahoo
Posts: 434
Joined: Mon Feb 19, 2007 9:59 pm
Location: Venice, FL

Post by kcyahoo » Sat Mar 26, 2011 9:39 am

I had the same problem with yahoo finance format. A couple of years ago I switched to the Excel add-on called Update Quote. Update Quote gets its data from MSN Money. In the cell where you want the quote you use this format =ROUND(MSNStockQuote.Functions.MSNStockQuote(E7,"Last Price"),2). The referenced cell has the ticker symbol of the quote you want. I am using Excel 2002 SP3. Works fine.
Retired @ 57, now 75 | was 50/45/5, then 42/54/04, now 35/60/5 | KC

User avatar
Oicuryy
Posts: 1210
Joined: Thu Feb 22, 2007 10:29 pm

Post by Oicuryy » Sat Mar 26, 2011 11:11 am

dave.d wrote:It's stacking two price quotes right next to each other, e.g. I got an S&P 500 quote of "1312.5699461,312.57". Has anyone figured out how to deal with this? Or know anything about what they are doing or why?
These excel formulas should separate out the first and second price. The first price seems to always have 6 characters after the decimal point.
=VALUE(LEFT(A1,FIND(".",A1)+6))
=VALUE(RIGHT(A1,LEN(A1)-FIND(".",A1)-6))

Here is the html for the price cell in yahoo's table.

Image

The text 1313.800049 is styled with display:none so a browser does not display it. Right after it comes the text 1,313.80 which is displayed by a browser.

Ron
Money is fungible | Abbreviations and Acronyms

User avatar
wade
Posts: 539
Joined: Fri Sep 17, 2010 12:38 am
Location: Main Line
Contact:

Post by wade » Sat Mar 26, 2011 9:34 pm

Over the last 5 years or so, I've been using MATLAB to download historical mutual fund data from Yahoo Finance. Two or three times they've changed their formatting for the URLs which caused my program to stop working. I've noticed that this happened again on January 25th (when I try to download historical data now, it only downloads up to January 25 and then stops). I haven't had time to fix this yet, but it usually involves just manually downloading some historical data to see the specific URL and then figure out how the pattern has been changed from before.

User avatar
wintermute
Posts: 186
Joined: Mon Mar 15, 2010 10:36 pm

Post by wintermute » Fri Apr 01, 2011 6:24 pm

Does anyone know how to get that Yahoo CSV url working in OpenOffice? It prompts for CSV import, then goes back to the external data window, but OK is greyed-out.

ayitey
Posts: 77
Joined: Wed Jul 28, 2010 3:51 pm

Post by ayitey » Sat Apr 02, 2011 11:11 am

I've started using a replacement URL (from BigCharts at MarketWatch) for downloading quotes into Excel; so far, so good!

Here's an example:
http://bigcharts.marketwatch.com/quotes ... TIAX+VTRIX

User avatar
wade
Posts: 539
Joined: Fri Sep 17, 2010 12:38 am
Location: Main Line
Contact:

Post by wade » Thu Apr 14, 2011 12:14 am

If anyone is interested, I found out why downloading historical data from Yahoo stopped after January 28. They changed their URLs a bit by adding in a "0" before the month number if it is less than 10.

So, just as an example, the old URL would have been:

Code: Select all

 http://ichart.finance.yahoo.com/table.csv?s=VIPSX&a=11&b=11&c=2007&d=3&e=14&f=2011&g=d&ignore=.csv
but now it is:

Code: Select all

 http://ichart.finance.yahoo.com/table.csv?s=VTIVX&a=11&b=11&c=2007&d=03&e=14&f=2011&g=d&ignore=.csv

User avatar
wintermute
Posts: 186
Joined: Mon Mar 15, 2010 10:36 pm

Post by wintermute » Mon Jun 20, 2011 5:10 pm

Old thread, but someone PM'ed asking if I ever got Yahoo working in OpenOffice again. I couldn't, but here's what I'm using now:

http://bigcharts.marketwatch.com/quotes ... mb=BSV+VTI

User avatar
dratkinson
Posts: 4180
Joined: Thu Jul 26, 2007 6:23 pm
Location: Centennial CO

Post by dratkinson » Mon Jun 20, 2011 7:23 pm

wintermute wrote:Old thread, but someone PM'ed asking if I ever got Yahoo working in OpenOffice again. I couldn't, but here's what I'm using now:

http://bigcharts.marketwatch.com/quotes ... mb=BSV+VTI
If OO will run MS macros, you might want to try using Gummy Yahoo macro to download stock prices. Works for me.

Suggestion. You could use this as the starting point for your "Yahoo data tags": sl1c1p2d1t1jkn . Modify as you see fit.

s = symbol. Use this a your first tag as you want to ensure the returned symbol is the same as the requested symbol. Why? When a symbol is unknown, Yahoo returns nothing and all following data is moved up one line---messes up your data. This can be a tricky problem to identify. The "s" (returned symbol) makes it easy to diagnose as the two symbols (requested and returned) will be side-by-side and either match, or not. Conditional formatting can make it easier to diagnose this problem by highlighting when a difference is detected. (My lesson learned.)

n = name. If you request the name of the instrument (stock, bond, fund,...) be returned, put it as the last tag requested. Why? Because some names contain a comma "," which is used by Yahoo and in Gummy's macro as a data field delimiter. Placing the name last prevents any scrolling problems in your returned data (as interpreted by the macro). (Gummy's lesson learned.)

j/k = 52-week low/high. Returns nothing for funds, but it will for any index you include in your request.



Gummy's Yahoo data download macro: http://www.gummy-stuff.org/Yahoo-data.htm

VennData
Posts: 575
Joined: Mon Feb 26, 2007 5:52 pm

Post by VennData » Mon Jun 20, 2011 8:48 pm

You can also use Google spreadsheet links in your mobile browser and see your AA on your smartphone see this post....

http://www.bogleheads.org/forum/viewtop ... highlight=

... for some approaches. So much easier than that Microsoft/Excel nonsense.

Google's Go programming language has a package called Money you should look at if you can code.

Use Google spreadsheets for this kind of stuff.
Last edited by VennData on Tue Jun 21, 2011 11:32 am, edited 1 time in total.

OkieIndexer
Posts: 384
Joined: Sun Aug 23, 2009 1:10 pm

Re: Yahoo format changed again, affecting spreadsheet import

Post by OkieIndexer » Mon Jun 20, 2011 8:58 pm

dave.d wrote:I got an S&P 500 quote of "1312.5699461,312.57".
This would seem to imply that the S&P 500 is computed to the millionths decimal place (i.e. 1312.569946) and then rounded to the hundredths decimal place for CNBC and whatnot. Is that correct?
"In bull markets, people say 'The more risk I take, the greater my return.' But when people aren't afraid of risk, they'll accept risk without being compensated." -Howard Marks, Oaktree Capital

User avatar
Epsilon Delta
Posts: 7337
Joined: Thu Apr 28, 2011 7:00 pm

More than you want to know

Post by Epsilon Delta » Tue Jun 21, 2011 1:13 pm

OkieIndexer wrote:
dave.d wrote:I got an S&P 500 quote of "1312.5699461,312.57".
This would seem to imply that the S&P 500 is computed to the millionths decimal place (i.e. 1312.569946) and then rounded to the hundredths decimal place for CNBC and whatnot. Is that correct?
Most likely this is just sloppy handling of binary arithmetic. Probably by whoever you got the quote from rather than S&P. Most computers use binary arithmetic.[1] Just as the fraction 1/3 does not have a finite decimal representation, the fraction 1/10 does not have a finite binary binary representation.
Chances are that 1312.569946, is in binary, as close to 1312.57 as the computer can get, and the programmer could not be bothered, or did not no how, to do the rounding when converting from the internal binary to displayed decimal [2]

I would argue that even the first two decimals are of little interest, so in practice this does not matter.

A few other things:

Theoretically the index could be computed to any number of decimals. The prices and divisor are just numbers, and once you have numbers you can compute decimals till you run out of paper.

In practice the computer usually preform a calculation to the limits of their precision[3] and round later. In this case some calculations were probably preformed to 23 binary digits (about 7 decimal digits)[4]


[1] As far as I am aware the only digital computers that perform decimal math are hand held calculators and some big iron from IBM. Things that Google, Yahoo and young whipper-snappers with an iphones view as dinosaurs.

[2] Its surprisingly hard. It's easy to get close but hard to get exactly correct for all cases.

[3] which will be in significant digits rather than decimal places.

[4] the programmer probably deserves a slap with a wet noodle for using single precision rather than double precision, which would give about 15 decimal digits.

greetje
Posts: 262
Joined: Sun Jan 27, 2008 7:58 am

Post by greetje » Tue Jun 21, 2011 3:05 pm

Man do I feel dumb.
Don't understand anything you guys are talking about.

Khanmots
Posts: 1230
Joined: Sat Jun 11, 2011 2:27 pm

Post by Khanmots » Tue Jun 21, 2011 4:00 pm

Has anyone gone the route of implementing a database with a spreadsheet (or whatever) used merely as the way to retrieve, morph, and display the data?

I started down the spreadsheet only path, and while it was initially simpler, it was going to become too messy for ease of long term maintenance. And while I'm sure I could put together a database backed system (and have started down that path) its not my forte and I'd rather not reinvent the wheel if someone has already done so... :)

User avatar
dave.d
Posts: 935
Joined: Mon Mar 19, 2007 10:30 pm
Location: Richmond, VA

Post by dave.d » Fri Jul 01, 2011 9:23 pm

I see Gummy's solution works very quickly and elegantly in IE to pull in a CSV table with all the needed data, but this doesn't seem to work as a link to external data in OO (perhaps if I keep in a different file, but then would I have to open both to update?).

I think I recall that Google Docs imposed a limit on the number of quote functions you could use in a document, which was less than I needed.

Episilon Delta -- what you're looking at is two numbers that should have a space between, being scrunched together.

I wouldn't know offhand how to go about doing a database solution. MS Access has always been greek to me, even though I understand all the principles.

I'll try using Wintermute's source because I am having trouble with intermittent failures updating with ~200 calls to GETQUOTE. To use a single table as a link to external data should be much faster.
Value-based allocation: recently 23% stocks @PE10=27 and real bond yields approx. zero

Post Reply