Re: [libreoffice-users] Link to external data question

Jim Byrnes wrote:
> I would like to import some stock quotes from finance.yahoo.com into a
> calc sheet.
>
> As a test I select cell A1
> Insert-->Link to External Data and I get the External Data dialog
> Put http://finance.yahoo.com/d/quotes.csv?s=AAPL&f=a in the URL box
> The OK button stays greyed out
> I hit ENTER and I get a Text Import dialog with a number in the Fields
> section
>
> When I hit OK I am sent back to the External Data dialog, OK button
> still greyed out and when I close the dialog, nothing is entered in A1.
>
> What do I need to do to get the stock quote entered in the cell?

I haven't used that feature before, but 
https://help.libreoffice.org/Calc/Link_to_External_Data says:
> 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.

The URL you're using points to a CSV file, which doesn't contain a named 
range (CSV is a very basic format, and doesn't support naming ranges). 
Using an ODS file with a named range works; after selecting the file, 
the ranges defined within the file are listed in the lower part of the 
"External Data" dialog, and the OK button is enabled once once is 
selected (with a CSV file, there are no ranges to select so OK is never 
enabled). But that's clearly no use to you unless Yahoo provides the 
data you want in that format...

It may be possible to pick up data from a table on an HTML page. That's 
liable to stop working when Yahoo change the layout of the page though, 
requiring you to go through the Insert > Link to External Data again to 
find the table in the new layout, but might work well enough.

Another possibility may be Insert > Sheet from File, specify that URL 
rather than browsing to a local file, and select the "Link" option. It 
seems to be rather slow to import (maybe that's just my Internet 
connection), and I'm not certain if it will actually update as intended, 
but probably worth trying. I'd suggest not modifying the imported sheet, 
in case either you loose your changes when the sheet is updated or 
modifying it prevents future updates. You can then reference cell A1 
from another sheet, which should then pick up changes assuming the 
imported sheet does update as intended.

Yet another option may be to set up a LibreOffice Base database 
connection pointing at the CSV file as its data source. (File > New > 
Database > Connect to existing > Spreadsheet > Next > Enter URL > Next > 
Finish). Not sure offhand if or how you can reference that from your 
spreadsheet though, and it is getting rather extreme just to get a 
single number!

Mark.

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted