none
How to get stock quotes RRS feed

  • Question

  • Hi:

    In excel 2007, I'd like to have stock symbols in column A, and set up a macro that would  link to the web and return the last trade quote in column B.  Can someone help me with that? 

    Thanks,

    Jeff

    Saturday, July 30, 2011 5:42 PM

Answers

  • Hi

    This should do it.  Still needs the GetData but not the other two

     

    Sub Build2()
     Dim txtSymbol As String
     Dim txtFormula As String
    
     ' Uncomment (delete ') on line below if you'd rather not watch it working
     'Application.ScreenUpdating = False
     
     Worksheets("Sheet1").Activate
     Range("A2").Activate
     
     Do While ActiveCell.Value <> ""
      txtSymbol = ActiveCell.Value
      Call GetData(txtSymbol)
      Worksheets("Sheet1").Activate
      txtFormula = "=" & txtSymbol & "!$B$1"
      ActiveCell.Offset(0, 1).Formula = txtFormula
      ActiveCell.Offset(0, 1).Copy
      ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      
      Application.DisplayAlerts = False
      Worksheets(txtSymbol).Delete
      Application.DisplayAlerts = True
      ActiveCell.Offset(1, -1).Activate
      
     Loop
     
     ' Uncomment this if you uncommented the other code.
     'Application.ScreenUpdating = True
    End Sub

    Glad to be some help

     

     

     


    G North MCT
    • Marked as answer by jeffals Monday, August 1, 2011 5:05 PM
    Monday, August 1, 2011 3:20 PM

All replies

  • The best option would be to get an Add-In that provides a function. I haven't tried these, so it's just a small list of what I've found in a short search:

    http://www.jabsoft.com/stock_quotes_for_excel/stock_quotes_for_excel.htm

    http://jbaurle.wordpress.com/2009/01/07/update-installer-for-msn-like-stock-quotes-add-in-for-excel-2007/

    MSN MoneyCentral Excel Add-in (maybe you can still find it somewhere, because the download is not available at Microsoft anymore)

     

    I can't tell if Microsoft still provides Smarttags or Research services (Alt-Click a cell). At least in my language version there's nothing.

    Sunday, July 31, 2011 8:23 AM
  • Hi

    As you are using 2007 you still have the Smart Tag option suggested by Alexander.  This link will tell you more about the feature especially how to enable it as it's off by default.

    Another alternative is to use a web query to extract the data from web pages of financial services.  Once the query is setup you only need to refresh it to get the price updates.

    Have a look at this thread although it doesn't do exactly what you are asking for it can be adapted.

    Hope this helps


    G North MCT
    Sunday, July 31, 2011 9:36 AM
  • G North,

    I tried the macro you created in that link you sent me, and I was impressed beyond belief!  I've copied it here, for ease of reference...

    Could you please "tweak" it just a little for me? (at least I think it would just be a little).  If not, I'll probably be able to figure it out...(However, my goal is not to become an excel programmer, but just to be able to get these quotes :)    )

      a.  The top line returned by this contains the only information I need, but would need to be parsed.  For example, when I ran it, I got,,,

             (On Jul 29: 27.40Down 0.32 (1.15%)  I just need the value;  27.40. 

      b.  Instead of hardcoding the symbols, i.e. (txtSymbols(0,1)="MSFT"),    I'd like the macro to be able to scroll thru the rows in column A, picking up the symbols from there, until it hits a blank row.

      c.  Instead of creating a new worksheet for each symbol, I'd like the value returned to be stored in column B. 

    Thanks so much,

    Jeff

    ============================

    Sub Macro2()
     Dim conString As String
     Dim conName As String
     Dim txtSymbols(0, 2) As String

     txtSymbols(0, 1) = "MSFT"
     txtSymbols(0, 2) = "CSCO"

     For i = 1 To 2

     Sheets.Add After:=Sheets(Sheets.Count)
     ActiveSheet.Name = txtSymbols(0, i)


     conString = "URL;http://finance.yahoo.com/q/ks?s=" & txtSymbols(0, i) & "+Key+Statistics"
     conName = "ks?s=" & txtSymbols(0, i) & "+Key+Statistics"

     With ActiveSheet.QueryTables.Add(Connection:=conString, Destination:=Range("$A$1"))
     .Name = conName
     .FieldNames = True
     .RowNumbers = False
     .FillAdjacentFormulas = False
     .PreserveFormatting = True
     .RefreshOnFileOpen = False
     .BackgroundQuery = True
     .RefreshStyle = xlInsertDeleteCells
     .SavePassword = False
     .SaveData = True
     .AdjustColumnWidth = True
     .RefreshPeriod = 0
     .WebSelectionType = xlSpecifiedTables
     .WebFormatting = xlWebFormattingNone
     .WebTables = """yfncsubtit"",8,10,11,13,15,17,19,21,23"
     .WebPreFormattedTextToColumns = True
     .WebConsecutiveDelimitersAsOne = True
     .WebSingleBlockTextImport = False
     .WebDisableDateRecognition = False
     .WebDisableRedirections = False
     .Refresh BackgroundQuery:=False
     End With
     Next i
    End Sub

    You can use

     ActiveWorkbook.RefreshAll

    To refersh the data gathered by these queries

    Or

     Selection.QueryTable.Refresh BackgroundQuery:=False

    to refresh just the query on the current page.

     

     

     

    Sunday, July 31, 2011 12:36 PM
  • Hi Alexander,

     

    I took a look at the top one; (jabsoft) -- and it may be an option -- except they want $99.00 for it;   but G. North has coded one up that he pointed me to that works great!  -- Except it does way more than I need, so hoping I can talk him into tweaking it for me; if not, I'll probably try and figure out how to do that myself. 

    I did try the smarttag idea, but couldn't get it to work.  I think it was having problems trying to find that msn moneycentral webpage.

     

    Thanks again,
    Jeff S

    Sunday, July 31, 2011 12:43 PM
  • I wrote a little function that opens the web page with an InternetExplorer object. So you don't have to use web queries:

     

     

    Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    
    Function GetStockValue(sCode)
    Dim sQuery As String
    Dim objIE
    Dim doc As HTMLDocument 'Ref: Microsoft HTML Object Library
     GetStockValue = "FAILED"
     sQuery = "http://finance.yahoo.com/q/ks?s=" & sCode
     Set objIE = CreateObject("InternetExplorer.Application")
     objIE.Visible = False
     objIE.Navigate2 sQuery
     Sleep 3000
     Set doc = objIE.document
     sElement = "yfs_l10_" & LCase(sCode)
     Set span = doc.getElementById(sElement)
     
     If Not span is nothing Then
     GetStockValue = CDbl(span.innerHTML)
     End If
     
     objIE.Quit
     Set objIE = Nothing
     
    End Function
    
    Sub Test1()
     MsgBox GetStockValue("MSFT")
    End Sub

     I haven't done much error handling, so it's just a code snippet to start with.

     



    Sunday, July 31, 2011 3:07 PM
  • Hi Alex,

     

    I couldn't get it to work.  I'm not an excel programmer, so not sure what's wrong.   Just looking at the code, tho, it appears that even if I did get it to work, it wouldn't quite be what I need.  Again it's got the symbol hard coded, and looks like this is going to return the quote inside a message box.  I want to be able to have symbols in column "A", that may vary as I buy and sell stocks, and run a macro that will return the last price in column B.   So...like this:

    Symbol Price

    IBM 45.32

    HWP 24.95

    I really do appreciate, tho, you taking your valuable time to try and help me out.  If I have to, I'll probably wind up buying an excel programming book and, using either your code snippet above or the one G North sent as a starting point, try and figure out how to modify it to meet my needs.

    Thanks again!

    Jeff

     

     

    Sunday, July 31, 2011 3:40 PM
  • Test1 is only an example. Put the function into your personal workbook and use it as custom function.

    Contents of A1: IBM

    Contents of B1: =PERSONAL.XLSB!GetStockValue(A1)

    Replace

    Dim doc As HTMLDocument 'Ref: Microsoft HTML Object Library

    by

    Dim doc

    if you don't know how to set a reference. The code works without it.

    Sunday, July 31, 2011 7:24 PM
  • Well, I'm running Vista, and from earlier notes, I thought the personal.xlsb is supposed to be in c:\users\jefals\appdata\local\microsoft\excel\xlstart.

    I discovered that I could navigate to the microsoft folder, but then I did not see an excel folder off of that.   And I know I was showing hidden folders, because I had to turn that on in order to see the appdata folder.  So, I did the following:

     1.  Navigated to c:\users\jefals\appdata\local\microsoft. 

     2.  Created an EXCEL folder.  Then, from the Excel folder, created an XLSTART folder.

     3.  Created a sheet in XLSTART called Personal.xlsb.

     Tried creating a macro inside personal.xlsb with your code in it.  Is that what I'm supposed to do?  Is it supposed to be a function instead of a macro?   Not sure how to do that...I did change that DIM statement to DIM DOC as you suggested.  So I have this saved as a macro called GetStockQuote inside  personal.xlsb, but when I open another worksheet and put IBM in A1 and put =personal.xlsb!getstockvalue(a1) in column B, i get back "#NAME?".

    Below is what my macro looks like.  Other issues:

      a)  Every time I open that personal.xlsb, it tells me macros are disabled.

      b)  I was getting an error also on that "Declare" statment, so I currently just commented it out.   With all the other issues, I can't recall for sure the problem on that one; I think it was just expecting an "end sub"...

    Thanks,

    Jeff

    Sub Getstockquote()
    ' Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

    Function GetStockValue(sCode)
    Dim sQuery As String
    Dim objIE
    Dim doc
     GetStockValue = "FAILED"
     sQuery = "http://finance.yahoo.com/q/ks?s=" & sCode
     Set objIE = CreateObject("InternetExplorer.Application")
     objIE.Visible = False
     objIE.Navigate2 sQuery
     Sleep 3000
     Set doc = objIE.document
     sElement = "yfs_l10_" & LCase(sCode)
     Set span = doc.getElementById(sElement)
     
     If Not span Is Nothing Then
     GetStockValue = CDbl(span.innerHTML)
     End If
     
     objIE.Quit
     Set objIE = Nothing
     
    End Function
    End Sub

     

     

    Monday, August 1, 2011 12:51 AM
  • Hi

    I never intended that the code in this thread would solve your problem.  But if your interested in using something similar then here is some code that does what I think you are asking for.  The code is based on you having one sheet (Sheet1) with Symbols in column A (with a heading, ppossibly Symbol).

    I have included one routine to build the data as per your request, Build.  This procedures calls GetData to perform the necessary Web queries (If a smymbol cannot be located then 0 will appear in column B).  The other two depend on your preference for maintaining this.  Update prices will refresh the web query to get the latest prices.  TidyUp will give you exactly what you asked for one sheet with the required numbers, to get an update you'll need to re-run the Build code. 

    Sub Build()
      Dim txtSymbol As String
      Dim txtFormula As String
    
      Worksheets("Sheet1").Activate
      Range("A2").Activate
      
      Do While ActiveCell.Value <> ""
        txtSymbol = ActiveCell.Value
        Call GetData(txtSymbol)
        Worksheets("Sheet1").Activate
        txtFormula = "=" & txtSymbol & "!$B$1"
        ActiveCell.Offset(0, 1).Formula = txtFormula
        ActiveCell.Offset(1, 0).Activate
      Loop
      
    End Sub

    Sub GetData(txtSymbol)
      Dim ConnStr As String
      
      ConnStr = "URL;http://finance.yahoo.com/q?s=" & txtSymbol & "&ql=1"
      ActiveWorkbook.Worksheets.Add
      With ActiveSheet.QueryTables.Add(Connection:=ConnStr, Destination:=Range("A1"))
        .Name = "q?s=CSCO&ql=0"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """table1"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
      End With
      ActiveSheet.Name = txtSymbol
    End Sub
    Sub UpdatePrices()
    
      ActiveWorkbook.RefreshAll
    End Sub

    Sub TidyUp()
      Dim sCount As Integer
      
      Sheets("Sheet1").Activate
      Range("B2").Select
      Range(Selection, Selection.End(xlDown)).Select
      Selection.Copy
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
      
      sCount = ActiveWorkbook.Sheets.Count
      
      Application.DisplayAlerts = False
      Range("A2").Activate
      Do While ActiveCell.Value <> ""
        Worksheets(ActiveCell.Value).Delete
        ActiveCell.Offset(1, 0).Activate
      Loop
      
      Application.DisplayAlerts = True
    End Sub

    Just copy this code in to a code module.

    Hope this helps

     

     

     

     

     

     

     

     

     

     


    G North MCT
    Monday, August 1, 2011 9:05 AM
  • Please delete your XLSTART folder and the file you created there. This is not the right place.

    How to create a personal.xlsb:

    1. Enable the Developer tab in Excel Options (Office Button)
    2. Now you'll see a little icon in the status bar with a red dot. Click it.
    3. Select Personal Macro Workbook from the dropdown list an click OK.
    4. Go to A1, Type the word "something". Press Enter.
    5. Click ths square icon in the status bar to stop the recording.
    6. Press Alt-F11
    7. Search something like VBAProject (PERSONAL.XSLB). Open the container Module. Double-click Module1 below it.
    8. Look in the right window and delete everything that is there.
    9. Copy the following code into the right window (and nothing more or less):

    Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    
    Function GetStockValue(sCode)
    Dim sQuery As String
    Dim objIE
    Dim doc
     GetStockValue = "FAILED"
     sQuery = "http://finance.yahoo.com/q/ks?s=" & sCode
     Set objIE = CreateObject("InternetExplorer.Application")
     objIE.Visible = False
     objIE.Navigate2 sQuery
     Sleep 3000
     Set doc = objIE.document
     sElement = "yfs_l10_" & LCase(sCode)
     Set span = doc.getElementById(sElement)
     
     If Not span is nothing Then
     GetStockValue = CDbl(span.innerHTML)
     End If
     
     objIE.Quit
     Set objIE = Nothing
     
    End Function
    

    10. Press Ctrl-S and close the window.
    11. Write "IBM" in A1
    12. Write =PERSONAL.XLSB!GetStockValue(A1) in B1, Press Enter und wait 3 seconds.
    13. Observe the result.

    Monday, August 1, 2011 10:12 AM
  • Hi G North,

    This works great.   I know you've spent significant time on this and I really appreciate it;  HOWEVER :)  if, you could see your way to one more mod for me...:)

    As I will likely have a lot of symbols in column A, I'm concerned about the number of sheets that will get created during the build.   So, if the build could be modified to do one row at a time, deleting the background sheet after it loads the price into column B, that would be ideal.  Then I wouldn't need the "UPdatePrices" or "TidyUp".   

    But, if you need to move on to other things, I really appreciate what you've done so far!

    Thanks so much, G!

    Jeff

    Monday, August 1, 2011 3:04 PM
  • Hi

    This should do it.  Still needs the GetData but not the other two

     

    Sub Build2()
     Dim txtSymbol As String
     Dim txtFormula As String
    
     ' Uncomment (delete ') on line below if you'd rather not watch it working
     'Application.ScreenUpdating = False
     
     Worksheets("Sheet1").Activate
     Range("A2").Activate
     
     Do While ActiveCell.Value <> ""
      txtSymbol = ActiveCell.Value
      Call GetData(txtSymbol)
      Worksheets("Sheet1").Activate
      txtFormula = "=" & txtSymbol & "!$B$1"
      ActiveCell.Offset(0, 1).Formula = txtFormula
      ActiveCell.Offset(0, 1).Copy
      ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      
      Application.DisplayAlerts = False
      Worksheets(txtSymbol).Delete
      Application.DisplayAlerts = True
      ActiveCell.Offset(1, -1).Activate
      
     Loop
     
     ' Uncomment this if you uncommented the other code.
     'Application.ScreenUpdating = True
    End Sub

    Glad to be some help

     

     

     


    G North MCT
    • Marked as answer by jeffals Monday, August 1, 2011 5:05 PM
    Monday, August 1, 2011 3:20 PM
  • Thanks G North,

    This one did just what I wanted -- I think.  As I was watching it, I noticed it creating "sheet 4", "sheet 5", etc, until it was done.   But I think, in reality, it was still doing one sheet at a time and deleting before creating the next sheet -- it was just using the sequential numbers for the name of the next sheet.  Isn't that correct?  

    I've been debating with myself, whether to ask you for one more thing, and decided not to.  However, if I WERE going to (:) ),it would be regarding the time it takes to get all these quotes.   Just wondering if the symbols could all be passed to the web and the quotes returned, in one web access....Well, that can be something for another time.  Right now, I am very happy with the way this works, so thanks again!

    Jeff

    Monday, August 1, 2011 5:05 PM
  • Hi Alexander,

    I'm at the office right now, so will wait till I get home back to my computer tonight, and give this a try.  Thanks for your help!

    Jeff

    Monday, August 1, 2011 5:07 PM
  • Hi

    Yes, each new sheet is automatically numbered as the next in the series, irrespective of the fact that the previous sheet no longer exists.

    I was thinking it wouldn't be to much to have all the queries arranged on one page, which you could keep and just refresh, to save time rebuilding all the time. Each query table coud be arranged in a grid, one each 11 rows down the sheet and then across every three columns.  If your going to do that than I'll need to fix an oversight in the code I gave you.  In GetData

     .Name = "q?s=CSCO&ql=0"

    should have been

    .Name = "q?s=" & txtSymbol & "&ql=0"

    To give each query a unique Id.  Doesn't matter at the moment as the query table is deleted before the next is created.

    The only danger in coding something like this is that the page layout changes and all of a sudden the query returns more rows.

    One query to do all would only be possible if you could find a site with a list of all the symbols you need in one table, even if you pulled that in Excel via a web query you'd need to filter the results to just the symbols you wanted.  Out of interest how many quotes symbols do you have?

     

     


    G North MCT
    Monday, August 1, 2011 5:33 PM
  • Hi G North,

    It took me a couple of reads, but I think I understand now what you are saying.  Your goal would be to save the time involved in creating each new sheet, but your concern is that the information returned from the query might change due to the web page changing, and that would affect moving the actual quotes in to sheet1 column B... (that about sums it up, right?)

    I do know that if you go to CNBC.com, there's a box where you can enter symbols  separated by commas, and it will return all the quotes.   Don't know if you'd be able to make use of that for this purpose, tho...(and also not sure if there's a limit)...

    As to the number I have that will vary, but 150 wouldn't be outside the realm of possibility....

    Thanks,

    Jeff

     

    Monday, August 1, 2011 6:30 PM
  • Hi

    I doubt it would be possible to use the CNBC input box method you describe.

    My worry is that at present the query returns a set number of rows.  If this changes, the formula that takes the values to the summary page (Sheet1) will need to adjust. I'll look into writing this formula, getting the queries below each other is a minor tweek to the code I gave you. Of course opening a file with 150 web queries will take some time as they all update, this could be turned off so that the file updates only when you refresh it.

     


    G North MCT
    Monday, August 1, 2011 7:26 PM
  • Hi

    Here is a new set of Build and GetData to place all the queries in one sheet (Sheet2).  The build only needs to run once.  After that you can use the UpdatePrices from the earlier post to refresh the queries and therefore get new prices on Sheet1.  I've change the refreshstyle to hopefully see off any changes to the returned table dimensions.

    Sub GetDataNew(txtSymbol, qCount)
     Dim ConnStr As String
     Dim TargCell As String
     
     ConnStr = "URL;http://finance.yahoo.com/q?s=" & txtSymbol & "&ql=1"
     If Not qCount = 0 Then
      TargCell = "A" & 11 * qCount
     Else
       TargCell = "A1"
     End If
     Worksheets("Sheet2").Activate
     With ActiveSheet.QueryTables.Add(Connection:=ConnStr, Destination:=Range(TargCell))
      .Name = txtSymbol
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertEntireRows
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .WebSelectionType = xlSpecifiedTables
      .WebFormatting = xlWebFormattingNone
      .WebTables = """table1"""
      .WebPreFormattedTextToColumns = True
      .WebConsecutiveDelimitersAsOne = True
      .WebSingleBlockTextImport = False
      .WebDisableDateRecognition = False
      .WebDisableRedirections = False
      .Refresh BackgroundQuery:=False
     End With
    End Sub
    
    
    Sub BuildNew()
     Dim txtSymbol As String
     Dim txtFormula As String
     Dim qCount As Integer
     Dim fCell As String
     ' Uncomment (delete ') on line below if you'd rather not watch it working
     'Application.ScreenUpdating = False
     
     qCount = 0
    
     Worksheets("Sheet1").Activate
     Range("A2").Activate
     
     Do While ActiveCell.Value <> ""
      txtSymbol = ActiveCell.Value
      Call GetDataNew(txtSymbol, qCount)
      Worksheets("Sheet1").Activate
      fCell = Worksheets("Sheet2").Range(txtSymbol).Cells(1, 2).Address
      txtFormula = "=Sheet2!" & fCell
      ActiveCell.Offset(0, 1).Formula = txtFormula
      ActiveCell.Offset(1, 0).Activate
      qCount = qCount + 1
     Loop
     
    ' Uncomment this if you uncommented the other code.
    'Application.ScreenUpdating = True
    End Sub
    

     

     


    G North MCT
    Monday, August 1, 2011 8:03 PM
  • Hi G,

    Regardless how many rows the query returns, wouldn't there be a way to recognize which row contains the quote, and only save that row -- or, in other words, delete all the other rows returned, except that one?   That's the only row we're interested in.  So that way, regardless of what other changes there are to the page, as long as the method of identifying the row with the quote remains constant, it seems like we'd be okay....What do you think?

    Monday, August 1, 2011 8:04 PM
  • Thanks G,

    I just tried it.   It is an improvement, in that it all goes to the one sheet.   Still not sure, tho, that "UpdatePrices" is necessary;  It doesn't seem any faster to me, than running the "buildnew".   

    Monday, August 1, 2011 8:16 PM
  • Hi

    I'll leave the decision up to you which one you want to use.  However once the BuildNew has run it cannot be run again without deleting Sheet2 completely and designating a new sheet as Sheet2.  It contains names (the symbol text) that when run a second time have "_1" added to the end.  This means the rewriting of the formulae will fail.

    150 web queries will never be quick, most of the time is the fetching the data from the website, the same when refreshing or creating as new.

    I'm not a stocks and shares guru. But if you find a better source of information a reduced number of queries will reduce the time.

    If there's anymore you need to know, feel free to reply.


    G North MCT
    Monday, August 1, 2011 8:43 PM
  • Thanks, G North.  Now I see the need for UpdatePrices.   Thanks again for all your help!

    Monday, August 1, 2011 8:52 PM
  • Hi Alexander,

     

    Ok, had a couple issues with  this one this time.   I've got the one G North did for me to work tho.  But I still tried this one.  First, when I go to step 10, CNTL-S to close the window, that did not work -- it didn't close the window.  I did get it closed, tho, somehow.  Then, in sheet1, i did steps 11 and 12.  I got a compile error when running it, and it seemed like it was either on that 1st line (Declare), or just after that.  It said it was expecting an ENd function or end sub, or something like that.  Normally, I've noticed, the macros start with a line like "SUB Macro1()" -- but this one doesn't have that, so I don't know if that's the cause or not.  

    However, unless this one would work faster than G North's, his is working fine, so I'll just stick with it.  But I sincerely appreciate your taking the time to try and help me with this!

     

    Thanks again,

    Jeff S

    Tuesday, August 2, 2011 1:14 AM
  • Can |I jump in here. I have been doing this for a good number of years by creating a portfolio and then using a web query to retrieve the whole portfolio table(s). I then parse the sheet looking for the values I need.

    Last week the webquery started to return no data. I am wondering if anyone can help explain why no data is returned. I create the query using the Excel Interface to URL

    http://www.iii.co.uk/view_portfolio/?folioname=MyPortfolio&folioid=BF4C0D1D1C9BC876D3B1A95D0D06B73712FFCDD36BE115ECD87D6D7FF0F12E3A

    If I start a new workbook and use the Data tab to create a web query to the above URL then select the table next to "LSE" and Import then I get an error "The web query returned no data"

    This is my personal list of stocks but all I need to understand is why it no longer returns any data and what if anything I can do to fix it. It is not too difficult to parse the results to locate the data but when nothing is returned I am stuck.

    Tuesday, August 16, 2011 7:43 PM
  • Update. Without me making any change the whole thing has started working again after a week of continuous failure. I guess they changed something and then fixed it.
    Wednesday, August 17, 2011 4:07 PM
  • I love it when that happens, Dave.   I once got an extra year and a half on a contract I was working because of a similar situation!   Something where I did the same thing everybody else did, but it worked for me, but not for them! :) 
    Wednesday, August 17, 2011 4:24 PM
  • Hi

    I looked in to this whe you first posted and like you couldn't return any data.  Didn't have time to look in to it, but suspected something on the web site might have been the issue.

    Just like you it is working for me again.  I checked my Update log and nothing had been updated since you posted, so I can only suggest that there was something going on at the website end.  Not a web expert so couldn't suggest what that might have been.


    G North MCT
    • Proposed as answer by Dave Mills Wednesday, August 17, 2011 7:47 PM
    Wednesday, August 17, 2011 5:57 PM
  • Thanks for the reply and checking it out. At least I know I am not going nuts :-)

    I did notice at one time I got a script error on the page. Maybe that was causing Excel to abort the data read without error. Do you know if it is possible to set Excel to ignore script errors on the page and just continue processing. I think that is what IE does in its default settings.  

    Wednesday, August 17, 2011 7:47 PM
  • jeffals and G North,

    In case you are interested my web query ends up producing this

    http://www.yellowdots.co.uk/Members/ShareMonitor.htm

    Thursday, August 18, 2011 7:21 PM