Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
How to put auto refreshing stock prices in Excel
Old 05-31-2017, 09:43 AM   #1
Dryer sheet aficionado
 
Join Date: Aug 2008
Posts: 29
How to put auto refreshing stock prices in Excel

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
__________________

__________________
RicDee is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 05-31-2017, 10:05 AM   #2
Full time employment: Posting here.
misanman's Avatar
 
Join Date: Apr 2008
Posts: 536
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.
__________________

__________________
"The best thing about the future is that it happens one day at a time." -- A. Lincoln
misanman is offline   Reply With Quote
Old 05-31-2017, 11:29 AM   #3
Recycles dryer sheets
 
Join Date: Jan 2014
Posts: 65
Quote:
Originally Posted by RicDee View Post
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 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.
__________________
greydog17 is offline   Reply With Quote
Old 05-31-2017, 11:48 AM   #4
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
braumeister's Avatar
 
Join Date: Feb 2010
Location: Northern Kentucky
Posts: 8,610
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).
__________________
Pas de lieu Rhône que nous.
braumeister is offline   Reply With Quote
How to put auto refreshing stock prices in Excel
Old 05-31-2017, 02:29 PM   #5
Full time employment: Posting here.
 
Join Date: Aug 2007
Posts: 892
How to put auto refreshing stock prices in Excel

This is one of the main reasons I originally ditched Excel. I use GoogleDocs. The GoogleFinance function makes retrieving this type of data trivial.
__________________
Eat, Drink and Be Merry.
tulak is online now   Reply With Quote
Old 05-31-2017, 04:06 PM   #6
Full time employment: Posting here.
 
Join Date: Mar 2013
Location: Coronado
Posts: 506
Quote:
Originally Posted by RicDee View Post
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
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 is offline   Reply With Quote
Old 06-02-2017, 09:36 AM   #7
Full time employment: Posting here.
Golden sunsets's Avatar
 
Join Date: Jun 2013
Posts: 735
Cathy63 and Ricdee; I'm so happy this question was asked and answered. The Ctrl+Alt+F9 works like a charm. Thx.
__________________
Golden sunsets is offline   Reply With Quote
Old 06-02-2017, 09:44 AM   #8
Recycles dryer sheets
fosterscik's Avatar
 
Join Date: Jun 2013
Posts: 183
eda
Quote:
Originally Posted by cathy63 View Post
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.
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"))
__________________
fosterscik is offline   Reply With Quote
Old 06-02-2017, 09:51 AM   #9
Thinks s/he gets paid by the post
 
Join Date: Feb 2014
Posts: 1,050
You can auto refresh using a looping macro (VBA) in Excel.
__________________
jim584672 is offline   Reply With Quote
Old 06-02-2017, 10:40 AM   #10
Thinks s/he gets paid by the post
target2019's Avatar
 
Join Date: Dec 2008
Posts: 3,708
Quote:
Originally Posted by cathy63 View Post
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.
NUMBERVALUE and WEBSERVICE are not included as functions in Excel 2010 PC.
Just in case someone tries.
__________________
target2019 is offline   Reply With Quote
Old 06-02-2017, 11:37 AM   #11
Dryer sheet aficionado
 
Join Date: Aug 2008
Posts: 29
Quote:
Originally Posted by cathy63 View Post
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.
Thanks Cathy,

That works. You do have to recalculate manually which is not a problem.
Thanks again,
Ric
__________________
RicDee is offline   Reply With Quote
Old 06-02-2017, 01:32 PM   #12
Full time employment: Posting here.
 
Join Date: Mar 2013
Location: Coronado
Posts: 506
Quote:
Originally Posted by target2019 View Post
NUMBERVALUE and WEBSERVICE are not included as functions in Excel 2010 PC.
Just in case someone tries.
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.
__________________
cathy63 is offline   Reply With Quote
How to put auto refreshing stock prices in Excel
Old 06-22-2017, 09:30 AM   #13
Thinks s/he gets paid by the post
 
Join Date: May 2014
Posts: 1,049
How to put auto refreshing stock prices in Excel

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...
__________________
rayinpenn is offline   Reply With Quote
Old 06-22-2017, 10:24 AM   #14
Thinks s/he gets paid by the post
 
Join Date: Mar 2011
Posts: 3,706
Quote:
Originally Posted by RicDee View Post
But I would prefer to use Excel
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
__________________
Living well is the best revenge!
Retired @ 52 in 2005
marko is offline   Reply With Quote
Old 06-22-2017, 12:46 PM   #15
Thinks s/he gets paid by the post
David1961's Avatar
 
Join Date: Jul 2007
Posts: 1,076
Thanks. I assume this would not work in the Open Office spreadsheet
__________________
David1961 is offline   Reply With Quote
Old 06-22-2017, 07:16 PM   #16
Thinks s/he gets paid by the post
target2019's Avatar
 
Join Date: Dec 2008
Posts: 3,708
Quote:
Originally Posted by David1961 View Post
Thanks. I assume this would not work in the Open Office spreadsheet
This thread suggests that it can be done with an LO calc extension, although I have not tried this.

https://forum.openoffice.org/en/foru...hp?f=9&t=89012

Let us know if it works for you.
__________________
target2019 is offline   Reply With Quote
Old 12-06-2017, 03:20 PM   #17
Dryer sheet aficionado
 
Join Date: Aug 2008
Posts: 29
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
__________________
RicDee is offline   Reply With Quote
Old 12-06-2017, 03:52 PM   #18
Full time employment: Posting here.
 
Join Date: Mar 2013
Location: Coronado
Posts: 506
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-...oogle-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 is offline   Reply With Quote
Old 12-06-2017, 03:54 PM   #19
Dryer sheet aficionado
 
Join Date: Aug 2008
Posts: 29
Quote:
Originally Posted by Golden sunsets View Post
Cathy63 and Ricdee; I'm so happy this question was asked and answered. The Ctrl+Alt+F9 works like a charm. Thx.
Hi
Does auto stock quote in your excel sheet still work?
Ric Dee
__________________
RicDee is offline   Reply With Quote
Old 12-06-2017, 04:08 PM   #20
Dryer sheet aficionado
 
Join Date: Aug 2008
Posts: 29
Thanks
I'll give it a try
Ric
__________________

__________________
RicDee is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Refreshing? Surgical center posts all prices, takes no insurance, charges 90% less. samclem Health and Early Retirement 46 01-16-2017 11:52 AM
Hello to a most refreshing forum! Numbers Hi, I am... 11 01-05-2013 06:38 AM
Excel stock quote add-in hlep needed Gonzo Other topics 4 09-01-2006 11:45 AM

 

 
All times are GMT -6. The time now is 01:38 AM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.