-1

I was able to import financial statements into excel using web query on the link "http://www.advfn.com/stock-market/NASDAQ/NVDA/financials?btn=annual_reports&mode=company_data". I recorded the import process into a macro and patched up the vba code a bit to have it run. Now, I would like to make the imported financial statements refresh based on a stock ticker I have in cell A1. However, I am getting a subscript is out of range error and i can't seem to figure out why. Please view the code below and provide any insights if possible.

Sub finstate()
'
' finstate Macro
'
'
  With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://www.advfn.com/stock-market/NASDAQ/" & Worksheets("Input").Range("A1").Value & "/financials?btn=annual_reports&mode=company_data" _
    , Destination:=Range("B2"))
    .Name = "financials?btn=annual_reports&mode=company_data"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = False
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingAll
    .WebTables = "6"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With
End Sub

Thank you so much!

Jason Huang
  • 1
  • 1
  • 1

1 Answers1

0

I think it might be your references, looks like you are reading the ticker value from the Input sheet but trying to write to the activesheet. Cause other than that possibility it seemed to work nicely for me.

I added a variable and test for the current worksheet. The first line of the with changed a little as well.

  sTicker = Range("A1").Value
  If sTicker = "" Then
    MsgBox "No value to look up"
    Exit Sub
  End If

  With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://www.advfn.com/stock-market/NASDAQ/" & sTicker & "/financials?btn=annual_reports&mode=company_data" _
    , Destination:=Range("B2"))
Captain Grumpy
  • 530
  • 5
  • 9
  • Thank you so much for your reply and pointing out the reference issue! It appears that the code is now running but I am receiving an error that reads "unable to open http://www.advfn.com/stock-market/NASDAQ/msft/financials?btn=annual_reports&mode=company_data. Cannot download the information you requested". Any clues as to why this is happening? – Jason Huang Sep 12 '16 at 01:28
  • Just tried and worked for me. maybe try wrapping sTicker = Range("A1").Value in UCase() – Captain Grumpy Sep 12 '16 at 02:22
  • Thank you so much! It wasn't working earlier due to internet explorer settings but it is perfectly functional now.! – Jason Huang Sep 12 '16 at 19:57