Mr Excel stock table macro

[krice1974]

Hey all, I was fortunate enough to pick this one up from Mr. Excel's book. It's a macro that builds a web query list from a string of stock symbols in one sheet, and forms the query in another sheet. A lookup column in the first sheet imports daily price data. It's almost working, but in column 2 of the "master stock list" (the vlookup column) I get "NA."

Any thoughts? Code pasted below, thanks in advance...

Kevin

Sub Update_Stock_List_Web_Query()

'This updates the master stock list pricing/ volume info. It rebuilds the stock list string and rebuilds the query every time its run.

Dim MSL As Worksheet 'Master Stock List
Dim WDI As Worksheet 'Workspace Daily Info
Dim QT As QueryTable
Dim finalrow As Long
Dim i As Integer
Dim connectstring As String
Dim finalresultrow As Long
Dim rowcount As Long

Set MSL = Worksheets("MasterStockList")
Set WDI = Worksheets("WorkspaceDailyInfo")

'Read column A of master stock list to find all symbols.

finalrow = MSL.Cells(Rows.count, 1).End(xlUp).Row

For i = 2 To finalrow
Select Case i
Case 2
connectstring = "URL;http://finance.Yahoo.com/q/cq?d=v1&s=" & MSL.Cells(i, 1).Value
Case Else
connectstring = connectstring & "%2c+" & MSL.Cells(i, 1).Value
End Select
Next i
Worksheets("MasterStockList").Activate

'On the workspace worksheet, clear all existing query tables.

For Each QT In WDI.QueryTables
QT.Delete
Next QT

'Define a new web query

Set QT = WDI.QueryTables.Add(Connection:=connectstring, Destination:=WDI.Range("A1"))

With QT
.Name = "Portfolio"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "11"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
End With

'Refresh the query
QT.Refresh BackgroundQuery:=False

'Define a named range for the results.

finalresultrow = WDI.Cells(Rows.count, 1).End(xlUp).Row
WDI.Cells(1, 1).Resize(finalresultrow, 7).Name = "WebInfo"

'Build a VLOOKUP to get stock info from WDI to MSL

rowcount = finalrow - 1

MSL.Cells(2, 2).Resize(rowcount, 1).FormulaR1C1 = "=VLOOKUP(RC1,webinfo,3,false)"

End Sub

 

[Derek Brown]

This looks the normal error when the VLOOKUP fails to find a match.
To allow for this you need to change the formula along the following lines:
=IF(ISNA(VLOOKUP(RC1,webinfo,3,false)),"Not found",=VLOOKUP(RC1,webinfo,3,false))

 

[krice1974]

Thanks!!

 

[Derek Brown]

Just for completeness, in case anyone else comes across this post, the formula should have been:
=IF(ISNA(VLOOKUP(RC1,webinfo,3,false)),"Not found",VLOOKUP(RC1,webinfo,3,false))

By mistake, I had repeated (copied) the second "=" (I did notice it before and thought that I had corrected it).

 

[krice1974]

Thank you Derek.