[Solved] Inserting quotes from finance.yahoo.com into a cell

[tjeanneret]

Hello Everybody,

I want to retrieve informations from yahoo finance and insert them into the appropriate cell in a Calc sheet. I have the format description for the query string (URL) that will return a .csv file from yahoo, for exemple http://finance.yahoo.com/d/quotes.csv?s=AAPL&f=a for the simplest form of the last price of Apple.
But I don't know how I can use this information and put it in place in my spreadsheet.
I use the "link to external data" menu. I can specify the URL in the top dialog. Then I <RETURN>, and Calc retrieves the data from Yahoo, opens the "Import" dialog box, show me the data received and ask me what kind of separator I want to use (!). I accept the default, and <OK>. But nothing happens after this point.
The "Link to external data" dialog never gives me a choice for a location where to put the data, and the OK button stays inactive.
I have not found a way to save the .csv file itself in order to process it later.

Could you help me ?

Thanks in advance,

Thierry

[acknak]

I hate to say this, but clicking the "Help" button on the External Data dialog window shows me this (I added the underlines):

Link to External Data
Inserts data from an HTML, Calc, or Excel file into the current sheet as a link. The data must be located within a named range.
To import data from a *.csv or *.txt file, choose File - Open and then select the Files of type "Text CSV".

Give that a try--it works for me with your address.

[tjeanneret]

I am sorry, I must be either very tired or completely stupid in this matter, but this does not solve my problem : I want the quote in one given cell of a given sheet. I have tried other syntaxes for the URL, and with these I got a whole lot of data, something that looks like the "normal" page you can see in the Web browser. I want to simplify all this stuff and pick only what I need. The help is quite imprecise, saying that we will have given the choice of some "named area". Obviously with a .csv I will never get there.
By the way, I don't see how I can use the "file open" dialog on an URL. Could you give me a clue about this ?

Thanks for your answer,

Thierry

[acknak]

I am sorry, I must be either very tired or completely stupid in this matter, but this does not solve my problem...

I'm sure it's neither of those reasons. OOo is immature software in many ways. That's why we try to help each other, and it's never a problem to come back and say "I still don't get it". We're all in the same boat.

I quoted the help because many people forget to look there, and it has a lot of useful information, but the organization and presentation of the information still needs some improvement.

Try this:
1) File > Open
2) In place of a plain file name, paste in your URL

That should bring up the Text Import dialog window. The default settings are fine (it's only one number); click OK.

Note that OOo cannot make this a "live" link--for that it needs a spreadsheet file (either .ods or .xls) on the other end. So you have to manually repeat this process every time you want to update the value.

[tjeanneret]

Acknack,

Thanks to your remarks I have been able to solve my problem. In fact I did not see a way to type anything in the "File Open" Dialog I had as default. The only way to select a file to open was to click on it in the "selector" area. So I digged into the Help and I found the "Use Open Office dialogs" option.
With this kind of "Open" dialog I am able to type in the URL I want, and my data come into a new sheet. I will be able to deal with this.
Sometimes I find Ooo a bit tricky, even if I like it very much. I use it almost exclusively since 6 to 7 years, I have never lost a single document and I have been able to produce a 280 pages well structured book translation with footer notes, index and so on. A real pleasure and a very professional tool.

Thanks for your help, all the best to you,

Thierry

[acknak]

Good job! Thanks for the follow-up; I'm glad you were able to get it working.

[fourcandle]

I've got the same problem. All the above solution does is to import a passive total - it doesn't import an dynamically updating table. Any other ideas?

Thanks in advance.

[acknak]

What happened when you tried "Link to external data"?

Can you provide a URL that we can try?

[donalddon5]

I have a solution that might be of some help:
First look at this:
http://www.tracenotes.com/ptracker.html
The only exceptions on this that I will make is step seven can be skip and instead of having an import button you simply press O.k...
Now at this point be patience because the import data will appear again...just wait until it disappear.
step 10 when linking just hit the = button instead of the fx then simply go
to bottom tab on and hit sheet 1 and reference the address: Example: =Sheet1.B1

Also here is some good info for the Yahoo tags:
www.gummy-stuff.org/yahoo-data.htm

Hopes this help..if unclear let me know and I will try to type up better instructions. The key to it all is having two spreadsheet.
The first (Portfolio) for the heading and the second so you can link Portfolio with sheet1 which has the downloaded data from Yahoo.

[jformacek]

Facing the problem just now, trying to make Calc to process this external URL: http://download.finance.yahoo.com/d/quo ... s=AMD&f=l1
When I enter the URL into Insert -> Link to External data dialog and press enter, Calc opens secondary dialog where I see the data, but when I click OK in the secondary dialog, OK in the first dialog is still disabled so I cannot click it - in my opinion just because the serer response for this URL is not HTML but rather a plain text.
Any ideas on OOO side how to make it working?

Thanks,
Jiri

[acknak]

... in my opinion just because the serer response for this URL is not HTML but rather a plain text.
Any ideas on OOO side how to make it working?

You are correct. Here is the entire output from that URL:
5.42

The way to handle this is to save the results to a file on your system, then load it into Calc, using either File > Open, or Insert > Sheet From File. I prefer the second method for a number of reasons.

[jdr93]

this open office extension makes it easy
http://extensions.services.openoffice.org/node/2514

[potis]

It's been awhile on this thread ... I'm trying to do historical dividend updates from yahoo.
The URL I'm trying to use is:
http://ichart.finance.yahoo.com/table.c ... gnore=.csv
which returns a 2 element array.
Using 'file>open' - will get the data from this URL but, it isn't automatic/persistent. Using 'insert>link to external data' begins to work, I see the data/table but, the "OK" key is greyed out.
using this URL :
http://finance.yahoo.com/d/quotes.csv?s=AAPL&f=a
returns too much data - doesn't seem worth the effort. the 'ichart' URL is cleaner.

if it did work i then want:
to use column 'b' only; summing it first and putting it in a single cell. I've done this with google docs using this line:
=sum(index(ImportData(CONCATENATE("http://ichart.finance.yahoo.com/table.c ... &a=",month(today()-1),"&b=",day(today()),"&c=",year(today())-1,"&ignore.csv")),,2)))
where "A2" is a reference to the stock symbol I'm fetching.

Anyone have a solution to this?