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

On 08/22/2014 11:05 AM, Mark Bourne wrote:
> Jim Byrnes wrote:
> > On 08/21/2014 03:16 PM, Mark Bourne wrote:
> > > 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...
> >
> > OK I understand now.  Just seems strange that I can get the data I want
> > in the Import Text dialog (just as I have done many times when importing
> > csv files) but I can't get it into the spreadsheet. When I click ok in
> > the Import Text dialog, focus goes to the Insert Dialog and the data is
> > lost.
>
> It is a bit confusing. I think the Import Text dialog is needed to open
> the file - being plain text, it can't automatically determine things
> like the character encoding and whether cells are separated by commas,
> tabs or something else. Then, after opening the file, it finds that
> there are no ranges defined within the file. It would be more sensible
> if it gave some message that there are no named ranges in the file. Or,
> rather than requiring a named range, allow the whole sheet to be
> imported - but perhaps that's the purpose of the Insert > Sheet from
> File option.
>
> > > 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.
> >
> > When I try this option I don't see any place to specify the URL, all I
> > see is a file selection dialog.
>
> You can type (or paste) a URL into the "File name" field and it will
> open it. What I'm not sure of is whether it will automatically update as
> intended; may need some experimentation.
>
> > > 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!
> >
> > The whole thing is getting complex.  I just wanted to link about 10
> > cells so I wouldn't have to type in the data manually.  Not a big
> > problem it's just that I am always looking for ways to automate stuff.
>
> Always handy when possible ;o)

Thanks to everyone that offered advice on this problem.  I found a 
possible solution here:

http://amourtan.com/2013/03/downloading-stock-prices-into-openoffice-calc/

It works but is a little bit of over kill for what I need.  Hopefully 
this weekend I will have a chance to look at the code and get some ideas 
of how to adapt it to my simple needs.

Regards,  Jim



--
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