Real Time Stock Price from Google Finance in Excel 2007

intradayteam

New Member
Joined
Feb 15, 2010
Messages
29
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

 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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



******** ******************** ************************************************************************><center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td colspan="6" style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0);" bgcolor="#0c266b"><table align="center" border="0" width="100%"><tbody><tr><td align="left">Microsoft Excel - Book2</td><td style="font-size: 9pt; color: rgb(255, 255, 255); font-family: caption;" align="right">___Running: xl2002 XP : OS = Windows XP </td></tr></tbody></table></td></tr><tr><td colspan="6" style="border-right: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); height: 25px;" bgcolor="#d4d0c8"><table valign="MIDDLE" align="center" border="0" width="100%"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">(F</td></tr></tbody></table></td></tr></tbody></table></center>
<center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0);" colspan="6" bgcolor="#0c266b">
</td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); height: 25px;" colspan="6" bgcolor="#d4d0c8"><table valign="MIDDLE" align="center" border="0" width="100%"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</td><td align="right" valign="center"><form name="formCb605117"><input *******="window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);" value="Copy Formula" name="btCb942116" type="button"></form></td></tr></tbody></table></td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0);" colspan="6" bgcolor="white"><table border="0"><tbody><tr><form name="formFb202339"></form><td style="width: 60px;" align="middle" bgcolor="white"><select onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name="sltNb447362"><option value="" selected="selected">A1</option></select></td><td align="right" bgcolor="#d4d0c8" width="3%">=</td><td align="left" bgcolor="white"><input size="80" name="txbFb150492"></td></tr></tbody></table></td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200);" align="middle" width="2%">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>A</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>B</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>C</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>D</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>E</center></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>1</center></td><td style="border-width: 0.5pt; border-style: solid; border-color: rgb(0, 0, 0) rgb(212, 208, 200) rgb(212, 208, 200) rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">C2 taken from http://www.etraderzone.com/free-scripts/50-yahoo-stock-quotes.html</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>2</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(204, 255, 255); text-align: left;">nl1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>3</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">n stands for name and l1 for CMP</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>4</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>5</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 255); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>6</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">YAHOO CODE</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">name</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">CMP</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>7</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">msft</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">Microsoft Corpora</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">29.61</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>8</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">goog</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 255); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">Google Inc.</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">566.4</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>9</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">ibm</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: left;">International Bus</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">128.38</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-width: 0.5pt; border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); background-color: rgb(212, 208, 200);" colspan="6"><table valign="TOP" align="left" width="100%"><tbody><tr><td style="border-width: 0.5pt; border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); width: 120pt; background-color: rgb(255, 255, 255);" align="left">Sheet1</td><td>
</td></tr></tbody></table></td></tr></tbody></table>
[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.</center>
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top