Import historical quotes with specific dates

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

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

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

HI Leith,

I have no words, its exactly what i wanted.

I was just looking at the macro and i even feel bad because it looks so complicated and you must have had lots of work and time spent.

Just 2 questions:

- To the spreadsheet you already done, if i add a new row with a new company, must the macro be changed?

- How can i do to make the second spreadsheet do the same? Just copy the macro or...?

Apologise these questions, but the programming part for me is like Greek. I am an economist just.

Once more, really thank you
Paulo

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

Thanks once more.

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

Paulo

Hello Leith,

Happy New Year!

I will test it and any doubt or error that happens, i will let you know.

Once again, i have no words to express my gratitude.

Just thinking of the next sheets i will have to do, with over 700 companies in total, this is a huge headache you take out of my life.

My best regards,
Paulo

Hi Leith,

I was checking today the file you sent me and i detected some problems.

I noticed that some dates are not returning any quotes. I am not talking about dates where a determined company didn't existed (something frequent in the 80's), but some blank spaces in dates of companies that existed in that period. I havent checked all, but its easy to see in Ford, McDonalds for example, in periods like 1-4-1982, 4-16-1982, 9-1-1982 or 10-12-1982, or in other companies even in more recent periods.
Every blank spot that you see without a quote, and that in the previous dates, for that company, there is a historical quote filled, there must be an error because for that blank spot there also should be a quote.

Another problem i noticed is that, if i add a row, with a new company and then click on the Get Historical Quotes button, the following error appears:
"RUN TIME ERROR 457 - This key is already associated with an element of the collection"
Am i adding rows in a way i shouldn't? Which is the way i should add a new company(ies) to the existent ones that would allow the code to work well?

One more, thank you in advance.
Regards,
Paulo

Hi Leith,

No, i am not duplicating stocks and there will be no duplications in the same sheet.

Try to add a row with IBM for example, or Microsoft (MSFT), just for the sake of an example. And tell me how you added, perhaps it can only be added in the end and not in between existing rows?

Paulo

Hi Leith,

Thats a bit of a problem to me, as i told you i really don't understand programming and to be fair, i haven't understood what you meant in the last post.
Forgive my ignorance in this matter.

Paulo

Hi Leith,

So you just written "IBM" on the B column and nothing else? Not even changed format in cells or so?

Best regards,
Paulo

HI Leith,

But for example, i just added two new companies, GE (General Electric) and JNJ (Johnson & Johnson), to the lines exactly beneath IBM and, in the case of GE it returned starnge values like 7625 (7-11-1986) or 11300 (6-30-1999), among others, and no values at all before 1985; while in the case of JNJ it didnt return any,

I then clicked on the Button for a second time, and now, it returned more dates in either, but not all. However the quotes are all wrong, still with number like 5690 or 7050 or so, in the thousands, which is impossible.

Does this happen with you aswell?

Regards,
Paulo

Hi Leith,

No, i am using a home computer, with no firewalls. Just a simple PC.

Regards,
Paulo

Hi Leith,

I am currently using IE9 and Windows 7 Ultimate.

Regards,
Paulo

Hi Leith,

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

Regards,
Paulo

Hi Leith,

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

Regards,
Paulo

Hi Leith,

This last one you posted "Discretionary IE9 ver 1.xls‎" returns that error i told you, and it doesnt accept any company name (ticker) on column B.

About the blank dates, have you yet come up with a conclusion?

Paulo

Hi Leith,

I understand.

I will fill the blank spaces one to one.

When i create the other worksheets for the rest of the companies, if i may, can i just send u so u attach the macro. Even if it returns blank spaces, i will fill them individually.

I really thank you for all the help you gave, believe me, for someone with null programming skills like me, this was a huge help.

Muito obrigado,
Paulo

Thanks Leith,

This workbook u sent me is one of nine (well, 18, if you count the SUB and DESC sheets)

When i get the others ready i will send you. Won't be for now, coz im still doing research for this specific sector (Consumer Discretionary), but when its done, i will, if i may.

Thank you once more,
Paulo

Hi Leith,

I wonder if i could ask you that, instead of using the "closing price" in the last workbook version 1c, you could use the "adjusted price"?

I have just realized that it must be using adjusted prices.

Thanks once more.

Have a great weekend,
Paulo