Import historical quotes with specific dates

[Paffius]

Hello,

I first must say that i know nothing about programming and therefore i really need some help.

I am trying to do a research about historical stock quotes, however, instead of importing complete tables that have date intervals, i wish to importa dpecif historical quotes on specificic dates (ex: 05/01/1981).

The problem is that my spreadsheet will consist of more than 600 companies and 62 different historical quote dates for each. You can see the amount of work and time that this will consume if i do it all by hand.

I will attach a file and i hope that someone can help me with this.

Thank you so much in advance.

Best regards,
Paulo Ferreira

Consumer Discretionary.xls

[Leith Ross]

Hello Paffius,

Welcome to the Forum!

To help you with this, I need you to provide a list of stock symbols for the companies you want the historical quotes for. Is there a particular site you wanted to query for these quotes? If yes then please provide the site's web address.

[Paffius]

Discretionary.xls

Hi Leith,

I posted a new file, with the symbols. Some stocks dont have symbols, so, forget those.
Usually i use the Yahoo Finance website.
For the FORD company, for example, the historical quotes link is: http://finance.yahoo.com/q/hp?s=F+Historical+Prices

One more time, thanks so much for helping. Besides filling the data, i am also very interested in learning how to do it, for the others spreadsheets i will have to make in the near future.

Best regards,
Paulo Ferreira

[Leith Ross]

Hello Paffius,

I downloaded the workbook again but I did not see the ticker symbols, only the company names. I can not use the names for the ticker symbol. For example Ford Motor Company's symbol is "F". Using "Ford" for the ticker symbol will return results for Forward Industries.

Which values do you want for the dates: Open, High, Low, Close, Volume, or Adj Close? Or will high and low be copied to different sheets?

[Paffius]

Hello Leith,

The symbols are in column B in the last file i sent you.
All i want is the CLOSE quotes, nothing else.

Regards,
Paulo

[Leith Ross]

Hello Paffius,

I finished the testing the code and it appears to returning the correct data for the historical dates. The attached workbook currently fills in only the first worksheet. I can change this to handle whichever sheets you want. Have a look and let know what you think. There is a new worksheet "Sheet1". I added this to check that the proper URLs were being accessed. I can remove this for the final version.

[Paffius]

Hi Leith,

You say it takes a little of time, i guess thats why whe i pressed the Get Historical quotes button, my excel started thinking and thinking...

About the companies, i will have to do some research still, because i thought more companies existed in the 80's, and obviously only 2 appeared.
So i will need some days to research a little, probably next week, because of this short holidays.

Thanks for the huge help.
Paulo

[Leith Ross]

Hello Paulo,

You're welcome. I will change the macro to run on both sheets and add something to inform you of the progress.

[Paffius]

Thanks once more.

By the way, is it normal for excel to be much slower or even unresponsive while it gets the data?

Paulo

[Leith Ross]

Hello Paulo,

In this case, yes. The data is being retrieved synchronously. That is Excel requests the data and waits for the server to return the data before continuing on with the code.

[Leith Ross]

Hello Paulo,

In this case, yes. The data is being retrieved synchronously. That is Excel requests the data and waits for the server to return the data before continuing on with the code.

[Leith Ross]

Hello Paulo,

The button will now run the macro on all worksheets except "Copy". The symbol and the current historical prices URL are displayed in Excel Status bar below the worksheet tabs. There is also a button to clear the prices on each sheet. Also, only rows that do not have closing prices in them will be updated. This will allow you to add new companies and not have to wait for the whole page to update.

[Paffius]

Hi Leith,

I am currently using IE9 and Windows 7 Ultimate.

Regards,
Paulo

[Leith Ross]

Hello Paulo,

Try this update. The macro changes the user agent string to be compatible with Internet Explorer 9. This should solve the problems you are having.

[Paffius]

Hi Leith,

Now it doesnt even accept any symbol on column B.
It gives an "Run time error 13 - Type mismatch".

Regards,
Paulo

[Leith Ross]

Hello Paulo,

Your new and old results seem to point to the user agent string not being correct. Since you Windows 7 Ultimate, do you ever run it in Windows 2003 compatibility mode? I would try running the previous workbook in this mode and see if it works.

[Paffius]

Hi Leith,

How do i do that, "run in Windows 2003 compatibility mode"?

Regards,
Paulo