Link to external data question

users
#1

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?

Thanks, Jim

#2

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.

#3

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

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.

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.

Thanks, Jim

#4

Hi,
Can you please try to use the file name without the ".CSV" as table name?

Sorry I cannot provide more info now....

#5

Hi :slight_smile:
Changing the file-ending doesn't change the format of the data itself
within the file.

I tried editing the url address to replace csv with ods and a file did get
downloaded but it wasn't really an ods - just some sort of text file which
Calc correctly identified as being like a csv and gave me the usual options
(which Jim already covered).

It would be handy to go back to the web-page the file came from and try to
see if there are any other formats available. One day people might even
start putting Ods files up so that everyone can read them. At the moment
they seem to use XlsX too often (which no-one can read properly unless they
get lucky) and thus 'have to' provide a Pdf too, which is also difficult to
import or copy&paste from. Some wiser places do provide files in the older
MS formats (so that's Xls without the X at the end) as the non-OOXML format
is easily read by everyone at the moment.

So, Jim, is it possible to give a link to the page that you downloaded the
file from? Then we can try to see if there is a better format for
downloading. My guess is that because it's Yahoo they are a bit clueless
about IT issues and don't give good formats.
Regards from
Tom :slight_smile:

#6

Jim Byrnes wrote

Jim Byrnes wrote:

Put http://finance.yahoo.com/d/quotes.csv?s=AAPL&f=a in the URL box

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.

As Mark correctly pointed out this facility is for linking to other (local)
spreadsheet files or (remote) HTML tables. The enhancement request to get
this facility extended to include CSV is
https://bugs.freedesktop.org/show_bug.cgi?id=35040

Try using this link instead:

https://au.finance.yahoo.com/q?p=finance.yahoo.com&s=AAPL

You will still need to select the appropriate HTML table from the page
(probably HTML_2). Also note that under v4.1.6.2 and v4.2.6.2 this facility
is working, but note it has had problems during the v4.0.x, v4.1.x, and
4.2.x series and under v4.3.0.4 it appears to (again) not be working.

#7

Not sure about that.

Have a look at comment #5 in fdo#69320
<https://bugs.freedesktop.org/show_bug.cgi?id=69320>

I followed along with that and was able to insert source link, and the
cell(s) receive the value(s) from the external .CSV resource.

#8

Jim Byrnes 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)

#9

Hi :slight_smile:
Changing the file-ending doesn't change the format of the data itself
within the file.

I tried editing the url address to replace csv with ods and a file did get
downloaded but it wasn't really an ods - just some sort of text file which
Calc correctly identified as being like a csv and gave me the usual options
(which Jim already covered).

It would be handy to go back to the web-page the file came from and try to
see if there are any other formats available. One day people might even
start putting Ods files up so that everyone can read them. At the moment
they seem to use XlsX too often (which no-one can read properly unless they
get lucky) and thus 'have to' provide a Pdf too, which is also difficult to
import or copy&paste from. Some wiser places do provide files in the older
MS formats (so that's Xls without the X at the end) as the non-OOXML format
is easily read by everyone at the moment.

So, Jim, is it possible to give a link to the page that you downloaded the
file from? Then we can try to see if there is a better format for
downloading. My guess is that because it's Yahoo they are a bit clueless
about IT issues and don't give good formats.
Regards from
Tom :slight_smile:

Here is what I was using to test:

http://finance.yahoo.com/d/quotes.csv?s=AAPL&f=a

I don't think Yahoo is at fault for using csv. It is a widely accepted format for transferring data of this type. For example my bank will let me download in Quicken, MS Money and maybe a couple of other formats but the fallback format is csv because most programs can handle it. In this case I was apparently trying to use a feature of clac for something it was not intended.

Regards, Jim

#10

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