Need help with code that will build a URL

[Habanero Time]

The goal is to be able to key in a date and have Excel import historical stock data. Right now, help is needed in setting up the variables in VBA (DIM, Set, etc) and building the URL.

The stock: NKE
Start date is March 19 2007
End date: May 19 2007

The resulting URL is:

http: //finance.yahoo.com/q/hp?s=NKE&a=02&b=19&c=2007&d=04&e=19&f=2007&g=m

Components of the URL

NKE = stock ticker

Start date
02 = March (The months start with Jan = 00 thru Dec = 11)
19 = day
2007 = Year

End Date
04 = May
19 = day
2007 = year

m = provide data by month (d = daily and w = weekly but I and going to stick with retrieving monthly data)

How do I start this code in order to put 3/19/2007 into a single cell and have VBA build the URL with an end date automatically 2 months later?

[Habanero Time]

I'm thinking of having the Start Date input in 3 different columns to help control how users load the info. Maybe this will help avoid having to write code that can handle all of these variations of the same date:

9JAN06
1/09/06
1/09/2006
1.9.06
9.1.2006

[JieJenn]

How much can I charge you to do something like this?

[Habanero Time]

How much can I charge you to do something like this?

OUCH! I'll have to think about that.

[Habanero Time]

My code is a bit raggedy but I pieced together something that does build the correct URL

Sub LoopHistorical_Data()

'Local Variable
Dim IE As InternetExplorer
Dim HTMLDoc As HTMLDocument

Dim rngTicker As Range
Dim rngStMo As Range
Dim rngStDa As Range
Dim rngStYr As Range

Dim rngEMo As Range
Dim rngEDa As Range
Dim rngEYr As Range
Dim rngQuerySymData As Range
Dim qryTableStocks As QueryTable


Set rngTicker = Worksheets("Sell").Range("A2")
Set rngStMo = Worksheets("Sell").Range("M2")
Set rngStDa = Worksheets("Sell").Range("N2")
Set rngStYr = Worksheets("Sell").Range("O2")
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
With ActiveWorkbook.Sheets("Sell")
    Dim LastCol As Long
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    Dim NewCol As Long: NewCol = LastCol + 1

'Set Data Ranges

Set rngEMo = Worksheets("Sell").Range("Q2")
Set rngEDa = Worksheets("Sell").Range("R2")
Set rngEYr = Worksheets("Sell").Range("S2")
Set rngQuerySymData = Worksheets("Web Query Page").Range("A5").Range("E1:P1")
Set qryTableStocks = ThisWorkbook.Worksheets("Web Query Page").QueryTables(1)


    On Error Resume Next
'Loop through list of Ticker Symbols and retrieve Historical Data
Dim RowCount As Long: RowCount = 1
    Do While .Range("A" & RowCount).Value <> ""
Dim Dest_Range As Range: Set Dest_Range = .Cells(RowCount, NewCol)
Dim StockName As String: StockName = .Range("A" & RowCount)

    Dim URL As String
URL = "http://finance.yahoo.com/q/hp?s=" & rngTicker & "&a=" & (rngStMo - 1) & "&b=" & rngStDa & "&c=" & rngStYr & "&d=" & (rngEMo - 1) & "&e=" & rngEDa & "&f=" & rngEYr & "&g=m"

With IE
.navigate URL
.Visible = True

'Wait for page to load
        Do While .readyState <> 4 Or IE.Busy = True


BLAH BLAH BLAH ... until End sub