updating stock price on Excel spreadsheet

[Seguy]

Is there somebody who could tell me how to update automatically stock prices on an Excel spreadsheet? Thank you

 

[cathy63]

Here's a thread that should help:
http://www.early-retirement.org/forums/f44/how-to-put-auto-refreshing-stock-prices-in-excel-87002.html

 

[trixs]

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

Where G90 is equal the ticker you want. Press Ctrl+alt+F9 to refresh.

 

[T-Minus]

I fought this battle for quite a while...including using the above-mentioned formula in Excel. Said formula worked sporadically for me. I fixed it by converting all my spreadsheets to Google Sheets a few months back. Problem solved.

 

[jetpack]

Yeah, Google Sheets has quotes built in!

 

[Golden sunsets]

Trixs formula, which is the same as in the referenced thread has been working flawlessly for me. Don't forget to control+alt+f9 to get the updated quotes.


Sent from my iPad using Early Retirement Forum

 

[Tadpole]

After Yahoo broke the StockQuote() function I was using, I joined the smf group at yahoo: https://groups.yahoo.com/neo/groups/smf_addin/info
They have been modifying their functions as yahoo changed their formats. I only use smfPricesByDate function but it has worked for me. Joining the group helps keep up with any changes they need to make.

 

[marko]

+1 Google sheets.

=GOOGLEFINANCE("JNJ","price")

 

[dadu007]

I fought this battle for quite a while...including using the above-mentioned formula in Excel. Said formula worked sporadically for me. I fixed it by converting all my spreadsheets to Google Sheets a few months back. Problem solved.

Me too. While I really want my local Excel file to work, it just doesn't. Often. Even after refreshing. I scoured the internet for a decent solution to no avail.
I gave up and now use the Google spreadsheet.
Incredible that MS cannot get this to work reliably.

 

[grasshopper]

+1 Google sheets.

 

[tulak]

It baffles me that MS makes it so difficult to integrate stock quotes into a spreadsheet.

+1 to GoogleSheets.

 

[Golden sunsets]

This formula is no longer working in my excel spreadsheet. =NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s=ivv&f=l1")) Where IVV is the cell number that the ticker is located in - in my case C5.
The last time I refreshed was about a month ago and it worked well as it has been for a year or so. Any ideas on what may have happened?

 

[NgineER]

This explains it...

When you enter the URL in a browser this appears:

"It has come to our attention that this service is being used in violation of the Yahoo Terms of Service. As such, the service is being discontinued. For all future markets and equities data research, please refer to finance.yahoo.com."

 

[Golden sunsets]

Well that s*cks. Does any one know of a substitute, or how does one conform to the Terms of Service?

 

[MRG]

Well that s*cks. Does any one know of a substitute, or how does one conforms to the Terms of Service?

Here's a thread that should help:
http://www.early-retirement.org/for...o-refreshing-stock-prices-in-excel-87002.html

Check upstream

 

[cathy63]

I believe you'll have to switch from Excel to Google sheets if you want to do it with a formula in a cell. Yahoo has shut down their stock price service, and Google seems to be the only free alternative available.

If you really want to use Excel, you'll need to write some VBA code to do screen scraping or parse the results of a SOAP service.

 

[kgtest]

+1 Google sheets.

=GOOGLEFINANCE("JNJ","price")

Sometimes the ticker is unavailable or the data is unavailable. you can even drop a formula in to see lastUpdated time/date.

 

[kgtest]

I believe you'll have to switch from Excel to Google sheets if you want to do it with a formula in a cell. Yahoo has shut down their stock price service, and Google seems to be the only free alternative available.

If you really want to use Excel, you'll need to write some VBA code to do screen scraping or parse the results of a SOAP service.

I heard Google Porfolios is going away. Not sure what that means for google finance but I would imagine if they took away our precious =GOOGLEFINANCE function from SHEETS it would be an uproar.

I love that you suggested a screenscrape script lol. I do this to cross-check my actual google sheet data. I login to each account and store the variables out using a webdriver. Problem is the account web portals lag a day on some index funds/mutual funds...so I just switched to all ETFs to avoid that problem.

 

[Golden sunsets]

Check upstream

The only info in your link relates to the formula I have been using and which no longer works, or using Google Sheets or using a VAB macro, which I am not familiar with. Did I miss something?

 

[MRG]

The only info in your link relates to the formula I have been using and which no longer works, or using Google Sheets or using a VAB macro, which I am not familiar with. Did I miss something.

Those are the solutions others here have come up with.

 

[Golden sunsets]

Those are the solutions others here have come up with.

So no other options if one uses excel. MRG are you suggesting their are other Excel options?

 

[MRG]

So no other options if one uses excel. MRG are you suggesting their are other Excel options?

No I'm not.

I thought there was a quick kill for you there. Sorry.

I'm retired(IT) and I avoid stuff like this because I'd have to support it. I couldn't afford to pay myself my old salary so no way.

 

[JoeWras]

Does anyone feel like the internet is going backwards the last few years?

Lots of good stuff going away, usually because someone is taking advantage of the situation. Here, I think it was commercial interests misusing the quotes.

And discussions blogs, articles, etc. got taken over by cranks. I used to love finding out a lot about movies on the IMDB boards, but they took them away supposedly because people didn't care. I doubt that was truth, it was more about the cranks with their horrible comments that probably opened the board operator to potential liability (you know, bogus comments about actors and such).

 

[bobandsherry]

Does anyone feel like the internet is going backwards the last few years?

Lots of good stuff going away, usually because someone is taking advantage of the situation. Here, I think it was commercial interests misusing the quotes.

Unless there's a way to monetize, free can't remain free, even if they try to make it up in volume. And free can't remain free when others, including some companies, take advantage of the free service to make a profit for their own benefit.

 

[fosterscik]

I too was frustrated when the yahoo financial data was withdrawn. I've been using the method described on this web page for the last couple of weeks:
https://www.vertex42.com/ExcelTemplates/excel-stock-quotes.html
It's a clever workaround using google finance to get prices for your funds, publishing that list of prices to a google doc, then importing the fund prices into excel. I don't see any security issues because the custom price list only includes pricing and not my share holdings.

It works well for all my etf and mutual funds (however mutual fund closing prices only become available the next day and not in the early evening; a minor problem)