Stock Prices import

Is there a URL you can grab from Yahoo that will always show the data you describe for the current day in a table? If so you should be able to set it up as a web data import (Data, Import External Data, New Web Query).

If you aren't familiar with a web table import then I can give more detail, if you give more detail about what data you're getting and how you're getting it from Yahoo.

(Your 30-day interval is clear but I think the dates in your example are a little hosed up. If today's date is Dec 7 then you want prices from Nov 8-Dec 7. The next day you want prices from Nov 9-Dec 8.)

First I suggest you look at how to import web data using the menu sequence I provided above, possibly checking Excel Help for Web Data.

It looks like the URL has the desired date hard-coded as parameters, so I'll have to look into the possibility of generating the URL dynamically, or possibly finding another way to get there.

This was an interesting little project, I learned a lot.

Attached is a workbook that is seeded with a range of dates from earlier in the year, just to demonstrate how this works. When you open the workbook, you must allow macros to run. Then it automatically updates the URL to reflect the current date, and updates the web query with the new URL.

One note: In the Yahoo page, if you enter Dec 9 as the end date, the latest date in the table is Dec 8 (at this moment). I don't know if there is a lag from the closing bell to when they make the data available in this form.

Originally Posted by roger00782006

Thank you for your hardwork. A small request : is it possible to NOT to have 11-Feb-10 0.025 Dividend
showing up on excel?

Not sure. Excel web import is designed to identify an HTML table in a web page and grab the entire table. It doesn't have hooks to edit it on the import step. You could create a new worksheet, and populate it with formulas referencing the imported data, skipping the stuff you don't want. Also, VBA might be able to edit the data once it's imported, although I don't know how that will affect the ability to accept future refreshes of the data. I may have time to take a look at this.

and yeah i guess a more challenging project: its again on stock quotes: I would like to have something like based on price parameters on sheet 1 (for eg: on sheet 1 put in a price range), have the excel file find stocks within that price range from yahoo finance ( on sheet 2) and once the stocks are listed on sheet 2, corresponding sheets get setup automatically for each stock (the price quotes would be just like you had done the first coding for) . i think this would be a rather challenging one eh..thanks once again..

I'll let you know what my consulting rate is

Here is an update that removes the dividend lines.