VBA code needed / stock price macro ?

**toekneetn** · 07-09-2012, 12:57 PM

hi everyone, can someone please help me with this one function i need to finish up a school project on historical stock prices in excel?? basically i need a way to perform this function:

in row 1, i have 30 RANDOM historical dates, starting in B1 and going right

in column A, i have 10 stock symbols, starting in A2 and going down

Is there a way to automatically grab the ADJUSTED CLOSING PRICE for each symbol for each corresponding date?? if not i have to do it manually!!

Your help in this matter is much appreciated!!!!

**zbor** · 07-09-2012, 01:04 PM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**toekneetn** · 07-09-2012, 02:25 PM

thx, i changed the title...now does anyone know how to do this? is there a code?

**JoeMarfice** · 07-09-2012, 02:35 PM

1. Download the historical prices for each stock from Yahoo.

2. Embed them in a spreadsheet.

3. Use HLookup/VLookup to find the date required.

**toekneetn** · 07-09-2012, 04:06 PM

thx for ur response joe...is there a way to just enter the symbol in excel and automatically grab the info from yahoo finance or whatever website?

**JoeMarfice** · 07-09-2012, 04:42 PM

Not easily. You'll need to write the code, or find an example online that you can modify to suit your purposes.

**Kyle123** · 07-10-2012, 06:32 AM

Like this:

Public Function GetClose(ticker As String, closeDate As Date) As Double
Dim results
Dim m0nth As String
If month(closeDate) < 10 Then m0nth = "0" & month(closeDate) - 1 Else m0nth = month(closeDate) - 1

With CreateObject("msxml2.xmlhttp")
    .Open "Get", "http://finance.yahoo.com/q/hp?s=" & ticker & _
                        "&a=" & m0nth & _
                        "&b=" & Day(closeDate) & _
                        "&c=" & Year(closeDate) & _
                        "&d=" & m0nth & _
                        "&e=" & Day(closeDate) & _
                        "&f=" & Year(closeDate) & "&g=d"
    .send
    Do: DoEvents: Loop Until .readyState = 4
    results = Split(.responseText, "<td class=""yfnc_tabledata1"" align=""right"">")
    .abort
    GetClose = Val(results(UBound(results)))
End With
End Function

**JoeMarfice** · 07-10-2012, 03:31 PM

You've also solved my problem with code that crashes Excel mysteriously.

Different code does not crash Excel, but achieves same purpose = no more problem.

Thank you!