Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Real Time Stock Price from Google Finance in Excel 2007

  1. #1
    New Member
    Join Date
    Feb 2010
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Real Time Stock Price from Google Finance in Excel 2007

    well i dont know weather this is possible or not with Excel 2007, I did this with Google Docs Spreadsheet long before and it was wonderfull.

    So hereby I call all genius here to tell me if this is possible.

    As the Title says, i want real time stock price in my Excel 2007 from Google finance.
    This is easily possible if i have my portfolio ready & i dont add new stocks daily in my portfolio, I simply go to Data > From Web and then somehow i import the Stock Price with Yellow or Green arrow in my excel file.

    But my query is little bit different.

    For example, Say if

    A1 has a value MSFT

    then B1 should be http://www.google.com/finance?q=msft
    (I used formula =HYPERLINK("http://www.google.com/finance?q="&A1&"")

    So now i have a hyper link or URL of Google finance page from which i need to import data.

    Now my question is, Is there any possibility that excel opens that links as soon as I enter a new value like GOOG in A1 and import the Stock price automatically in cell C1.

    As i have lots of stocks, i dont want to import data manually everytime i add a new stock in my portfolio. I need a formula by which i get the latest stock price as soon as i write a Stock Name in cell A1.

    Example

    A1 = MSFT
    B1 = http://www.google.com/finance?q=msft
    C1 = 29.60

    A2 = GOOGLE
    B2 =
    http://www.google.com/finance?q=GOOGLE
    C2 = 570

    It doesnt matters if B1 uses some different formula then Hyperlink, as i am concerned about values in A & C only.

    So if i shorten my query in a single sentence, then i would say.

    If i write some stock name in A1, then C1 must show its current market price.

    I would like to do this with Google Finance only as nothing is as fast as google finance.

    I hope this is possible.

    Waiting for your replies.
    Regards


  2. #2
    Board Regular
    Join Date
    Aug 2005
    Posts
    4,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Real Time Stock Price from Google Finance in Excel 2007

    donaldb36 has given a very good macro for this
    ref : http://tech.groups.yahoo.com/group/xltraders/files/
    in the file list go down and use the file given by donaldb36


    At present I am not able to think of any formula . There is an elegant solution by donalsb36 see reference above

    This is based on yahoo.finance

    I am sending you the sheet below. the important cells are
    C2 which contains the parameters required. see reference for this in C1 of the sheet and explanation in c3.
    the data starts ONLY from A7 (macro is is designed like this.can be modified if you want)
    maximum of about 70 stocks can be entered in A7 down.
    the latest price will be in D7 down.

    remember the stock codes are yahoo codes for e.g for google the yahoo code is goog. i HAVE NOT DONE ANY MODIFICTION TO HIS CODE

    If this is useful thank donalb36 and not me.

    the macro is (as given by donaldb36). If you want you can have button for this macro on the sheet. That is what donald has done.

    you have to fill up the codes in column A and run the macro

    if you are familiar with macros you can go the main reference I have given above and download donald's file (This will be the best thing)

    Code:
    Sub GetData()
    'this is a code taken from the files of the  forum "xltraders"
    'http://tech.groups.yahoo.com/group/xltraders/files/
    'file by donaldb36
    
    Dim yahoourl As String
        Dim QuerySheet As Worksheet
        Dim DataSheet As Worksheet
        Dim qurl As String
        Dim i As Integer
        
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Application.Calculation = xlCalculationManual
        
        Set DataSheet = ActiveSheet
      
        Range("C7").CurrentRegion.ClearContents
        i = 7
        yahoourl = "http://quote.yahoo.com/d/quotes.csv?s=" + Cells(i, 1)
        i = i + 1
        While Cells(i, 1) <> ""
            yahoourl = yahoourl + "+" + Cells(i, 1)
            i = i + 1
        Wend
        yahoourl = yahoourl + "&f=" + Range("C2")
    
    QueryQuote:
                 With ActiveSheet.QueryTables.Add(Connection:="URL;" & yahoourl, Destination:=DataSheet.Range("C7"))
                    .BackgroundQuery = True
                    .TablesOnlyFromHTML = False
                    .Refresh BackgroundQuery:=False
                    .SaveData = True
                End With
                
                Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                    Semicolon:=False, Comma:=True, Space:=False, other:=False
                            
        
    
        Application.Calculation = xlCalculationAutomatic
        Application.DisplayAlerts = True
        Columns("C:C").ColumnWidth = 28#
        Cells(2, 3).Select
    End Sub
    
    
    
    ******** ******************** ************************************************************************>
    Microsoft Excel - Book2___Running: xl2002 XP : OS = Windows XP
    (F

    )ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    1


    C2 taken from http://www.etraderzone.com/free-scripts/50-yahoo-stock-quotes.html

    2


    nl1

    3


    n stands for name and l1 for CMP

    4





    5





    6
    YAHOO CODE
    nameCMP
    7
    msft
    Microsoft Corpora29.61
    8
    goog
    Google Inc.566.4
    9
    ibm
    International Bus128.38
    Sheet1

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    I am not an expert. So better solutions may be available
    MinE WINDOWS 7 AND excel 2007(compatbililty mode)
    venkat1926(at)gmail(dot)com
    preferably do not send private messages in the newsgroup reply to newsgroup

  3. #3
    New Member
    Join Date
    Nov 2010
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Real Time Stock Price from Google Finance in Excel 2007

    Sorry to bring up an old thread, but I am new to VBA and in trying to use this code I have a little problem.

    I change the somewhat, stock code start from A2 and print the data across in the respective columns. However, I cant seem to change it from looking for codes at row 7. I would like it to look at codes from A2.

    Any suggestions?

  4. #4
    Board Regular
    Join Date
    Aug 2005
    Posts
    4,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Real Time Stock Price from Google Finance in Excel 2007

    ajn946946

    will it be possible for you to post the modified code
    I am not an expert. So better solutions may be available
    MinE WINDOWS 7 AND excel 2007(compatbililty mode)
    venkat1926(at)gmail(dot)com
    preferably do not send private messages in the newsgroup reply to newsgroup

  5. #5
    New Member
    Join Date
    Nov 2010
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Real Time Stock Price from Google Finance in Excel 2007

    Thankyou kindly for the response.

    I intended to have: A2 and down to be the stock code, and B, C to show name, price respectively.

    Code:
    Sub GetData()
    'this is a code taken from the files of the  forum "xltraders"
    'http://tech.groups.yahoo.com/group/xltraders/files/
    'file by donaldb36
    
    Dim yahoourl As String
        Dim QuerySheet As Worksheet
        Dim DataSheet As Worksheet
        Dim qurl As String
        Dim i As Integer
        
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Application.Calculation = xlCalculationManual
        
        Set DataSheet = ActiveSheet
      
        Range("B2").CurrentRegion.ClearContents
        i = 7
        yahoourl = "http://quote.yahoo.com/d/quotes.csv?s=" + Cells(i, 1)
        i = i + 1
        While Cells(i, 1) <> ""
            yahoourl = yahoourl + "+" + Cells(i, 1)
            i = i + 1
        Wend
        yahoourl = yahoourl + "&f=" + Range("H1")
    
    QueryQuote:
                 With ActiveSheet.QueryTables.Add(Connection:="URL;" & yahoourl, Destination:=DataSheet.Range("B2"))
                    .BackgroundQuery = True
                    .TablesOnlyFromHTML = False
                    .Refresh BackgroundQuery:=False
                    .SaveData = True
                End With
                
                Range("B2").CurrentRegion.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                    Semicolon:=False, Comma:=True, Space:=False, other:=False
                            
        
    
        Application.Calculation = xlCalculationAutomatic
        Application.DisplayAlerts = True
        Columns("B:B").ColumnWidth = 28#
        Cells(2, 3).Select
    End Sub
    I am not really sure what I am doing but when I change the line i = 7 to anything else the code no longer works..

    Any ideas, I am sure this is a simple fix.

    Cheers

  6. #6
    Board Regular
    Join Date
    Aug 2005
    Posts
    4,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Real Time Stock Price from Google Finance in Excel 2007

    your heading gives google.finance. but macro is yahoo.finance. My old file is lost due to some repairs to my computer. can you post the sheet or send the whole workbook by email to my email address given in my previous postings (below the message) provided the moderator has no objection.
    I am not an expert. So better solutions may be available
    MinE WINDOWS 7 AND excel 2007(compatbililty mode)
    venkat1926(at)gmail(dot)com
    preferably do not send private messages in the newsgroup reply to newsgroup

  7. #7
    New Member
    Join Date
    Dec 2012
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Real Time Stock Price from Google Finance in Excel 2007

    For a VBA function that returns any parameter provided by Google Finance's XML stream, try

    Philadelphia Reflections: Exchange Quotes from XML using Excel (Google)

  8. #8
    New Member
    Join Date
    Jun 2011
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Real Time Stock Price from Google Finance in Excel 2007

    Quote Originally Posted by ajn946946 View Post
    Sorry to bring up an old thread, but I am new to VBA and in trying to use this code I have a little problem.

    I change the somewhat, stock code start from A2 and print the data across in the respective columns. However, I cant seem to change it from looking for codes at row 7. I would like it to look at codes from A2.

    Any suggestions?
    Try examining this spreadsheet for downloading google finance stock quotes into Excel. The VBA works perfectly in Excel 2010

  9. #9
    New Member
    Join Date
    Nov 2017
    Location
    Conte Subdivision, Tres de Mayo, Digos City, Davao del Sur Philippines
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Real Time Stock Price from Google Finance in Excel 2007

    I recommend using MarketXLS as an alternative.
    MarketXLS have regular updates and customer support unlike yahoo.
    It cost a little but it will definitely save you time.
    Hope it helps.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •