How to put auto refreshing stock prices in Excel

R

RicDee

Recycles dryer sheets
Joined
Aug 27, 2008
Messages
50
How to put auto refreshing stock prices in Excel

I tried
=NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s=SYMBOL&f=l1"))
SYMBOL is the Yahoo Finance Stock Symbol
It works initially, but does not update automatically

I tried
=googlefinance("SYMBOL","price")
SYMBOL is the Google Finance Stock Symbol
In Google > Drive > Spreadsheets
And it does work and updates automatically
But I would prefer to use Excel
I tried to copy it to Excel and it would not work

I tried
Excel > Data > Workbook Connections > Add
And get “no connections found…”

Thanks,
Ric
 
I use MarketXLS. I am away from home but will check details when I get back. There is some cost but well worth it. My spreadsheets total to the same as fidelity whenever market is closed.
 
RicDee said:
How to put auto refreshing stock prices in Excel

I tried
=NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s=SYMBOL&f=l1"))
SYMBOL is the Yahoo Finance Stock Symbol
It works initially, but does not update automatically

I tried
=googlefinance("SYMBOL","price")
SYMBOL is the Google Finance Stock Symbol
In Google > Drive > Spreadsheets
And it does work and updates automatically
But I would prefer to use Excel
I tried to copy it to Excel and it would not work

I tried
Excel > Data > Workbook Connections > Add
And get “no connections found…”

Thanks,
Ric
Click to expand...
I had same issue with excel - gave up and used google spreadsheet. Have to admit I've come to like the google auto updating and saved the spreadsheet link as a favorite to my bookmark bar and its ready whenever opened or if I keep it open all day its constantly updated. If I ever need a snapshot say at end of month or quarter I can copy and paste into excel.
 
Time to come over from the dark side?

Apple's Numbers spreadsheet handles this easily.

(It's a new feature they added to Numbers in the last few months).
 
This is one of the main reasons I originally ditched Excel. I use GoogleDocs. The GoogleFinance function makes retrieving this type of data trivial.
 
RicDee said:
How to put auto refreshing stock prices in Excel

I tried
=NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s=SYMBOL&f=l1"))
SYMBOL is the Yahoo Finance Stock Symbol
It works initially, but does not update automatically

I tried
=googlefinance("SYMBOL","price")
SYMBOL is the Google Finance Stock Symbol
In Google > Drive > Spreadsheets
And it does work and updates automatically
But I would prefer to use Excel
I tried to copy it to Excel and it would not work

I tried
Excel > Data > Workbook Connections > Add
And get “no connections found…”

Thanks,
Ric
Click to expand...

Not sure what you mean by "does not update automatically", but you do have the wrong URL in your formula. Try this:

=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s=INTU&f=l1"))

You can force Excel to recalculate everything by using Ctrl+Alt+F9. I think it does this automatically when you close and reopen the file, but I didn't test it, and it may be a setting that is buried somewhere in the options.
 
Cathy63 and Ricdee; I'm so happy this question was asked and answered. The Ctrl+Alt+F9 works like a charm. Thx.
 
eda
cathy63 said:
Not sure what you mean by "does not update automatically", but you do have the wrong URL in your formula. Try this:

=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s=INTU&f=l1"))

You can force Excel to recalculate everything by using Ctrl+Alt+F9. I think it does this automatically when you close and reopen the file, but I didn't test it, and it may be a setting that is buried somewhere in the options.
Click to expand...

I was also using links without the 'download' in its address. It worked most of the time, but was flaky. This simple change has made all the difference. Thanks!

eta: I refer to another cell to choose which symbol to look up (In this example INTU would be in cell L25):
=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s="&L25&"&f=l1"))
 
Last edited:
You can auto refresh using a looping macro (VBA) in Excel.
 
cathy63 said:
Not sure what you mean by "does not update automatically", but you do have the wrong URL in your formula. Try this:

=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s=INTU&f=l1"))

You can force Excel to recalculate everything by using Ctrl+Alt+F9. I think it does this automatically when you close and reopen the file, but I didn't test it, and it may be a setting that is buried somewhere in the options.
Click to expand...

NUMBERVALUE and WEBSERVICE are not included as functions in Excel 2010 PC.
Just in case someone tries.
 
cathy63 said:
Not sure what you mean by "does not update automatically", but you do have the wrong URL in your formula. Try this:

=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s=INTU&f=l1"))

You can force Excel to recalculate everything by using Ctrl+Alt+F9. I think it does this automatically when you close and reopen the file, but I didn't test it, and it may be a setting that is buried somewhere in the options.
Click to expand...

Thanks Cathy,

That works. You do have to recalculate manually which is not a problem.
Thanks again,
Ric
 
Last edited:
target2019 said:
NUMBERVALUE and WEBSERVICE are not included as functions in Excel 2010 PC.
Just in case someone tries.
Click to expand...

Yes, both functions were added in Excel 2013. Prior to that you'd have to write some VBA code to call a web service, though you could use the VALUE function to convert text to numbers.
 
I wrote a little vba macro that calls the stock price and dividend from the yahoo web service and re prices my portfolios ...401k, and taxable holdings... I've used it for years.

I try not to run it often because that would be against my long term buy and hold forever dividend payers...
 
Last edited:
RicDee said:
But I would prefer to use Excel
Click to expand...

I came up through VisiCalc, Lotus 123 and Excel. Been on Googlesheets for several years now.

Personally I see no big difference between Excel and Googlesheets. At least for what I'm doing.

YMMV
 
Thanks. I assume this would not work in the Open Office spreadsheet
 
Hi Cathy,
The Yahoo stock quotes in my Excel stopped working

=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s=WFC-PL&f=l1"))
Ctrl/Alt/F9

Did something change
 
Yes, Yahoo has shut it down due to some commercial programs that were using it in violation of their terms of service.

In another thread, someone pointed out this rather clever solution for importing this data from Google instead: https://office-watch.com/2016/excel-stock-prices-from-google-finance/

It requires a bit more setup, and you may have to use a lookup function in Excel to get the exact number into the exact cell you want, but it does work.
 
cathy63 said:
Yes, Yahoo has shut it down due to some commercial programs that were using it in violation of their terms of service.
Click to expand...

"It was so popular we had to shut it down." That sums up Yahoo's problems right there.
 
GrayHare said:
"It was so popular we had to shut it down." That sums up Yahoo's problems right there.
Click to expand...

It's more like "we didn't realize that people were going to steal this stuff that we are just renting from someone else and make us liable for damages if we didn't lock it up" ... which is also a sad commentary on Yahoo's problems.
 
You must log in or register to reply here.

Similar threads

T
How to get stock/bond prices into excel
Replies
15
Views
413
Texas Proud
T
NW-Bound
Failure to exercise in-the-money option
2
Replies
27
Views
3K
aja8888
aja8888
F
Oddball Inheritor hoping to retire in 2021
2
Replies
34
Views
5K
Beachcomber
B
S
Yahoo’s new interface
Replies
10
Views
1K
Lsbcal
Lsbcal
S
Yahoo finance new format is useless.
2
Replies
47
Views
5K
Texas Proud
T

Latest posts

Back
Top Bottom