Help--excel not pulling stock prices

[Schultz]

I use excel to pull quotes off of MSN Money (it comes with that option).

However, alot of quotes on obviously valid stocks (including ETFs) don't
come up.

For example, ticker IWD is the ishares russell 1000 value index. EWW is the
ishares mexico index.

These come up fine anywhere I try to get a quote, but moneycentral.msm.com
acts like they don't exist!

Anyone know what gives?

Is there an easy way to quickly pull current (delayed is ok) quotes into
excel other than through the default microsoft moneycentral?

Thanks.

 

[Dave Miller]

Schultz,

-Put this code into a module ( making sure nothing is red after you
paste it! )
-Type your Ticker into Cell A1
-Type "=GetQuote(A1)" into Cell B1
-You should have your quote in B1

Code:
=======================================================
Option Explicit
Public Const URL As String = "http://finance.yahoo.com/q?s="
Function GetQuote(sTicker As String) As String
'=======================================================
'Fuction written by: David Miller
'Returns a !Yahoo Finanace quote
'=======================================================
Dim ie As Object
Dim sHTML As String

On Error GoTo GetQuote_Err
Application.ScreenUpdating = False

Set ie = CreateObject("InternetExplorer.Application")

If IsNull(sTicker) Then GoTo GetQuote_Err

With ie
.Visible = False
.navigate (URL & sTicker)
Do Until .readystate = 4
DoEvents
Loop
sHTML = .document.body.innerHTML
GetQuote = Mid(sHTML, InStr(sHTML, "yfs_l10_" & LCase(sTicker)) +
Len("yfs_l10_" & sTicker & Chr(34) & ">") - 1, 8)
If InStr(GetQuote, "<") > 0 Then
GetQuote = Left(GetQuote, InStr(GetQuote, "<") - 1)
End If
End With

GetQuote_Exit:
Set ie = Nothing
GetQuote_Err:
GetQuote = Null
Resume GetQuote_Exit
End Function


David Miller

Schultz wote:

 

[Dave Miller]

Schultz,

One more thing, I neglected to add an Exit Function in the
GetQuote_Exit:

You will want to paste this in the GetQuote_Exit:
-Right under "Set ie = nothing"
-Paste "Exit Function"

If you don't do this the function will return an error everytime.

David Miller


Dave Miller wote:

 

[Ron Rosenfeld]

I use excel to pull quotes off of MSN Money (it comes with that option).

However, alot of quotes on obviously valid stocks (including ETFs) don't
come up.

For example, ticker IWD is the ishares russell 1000 value index. EWW is the
ishares mexico index.

These come up fine anywhere I try to get a quote, but moneycentral.msm.com
acts like they don't exist!

Anyone know what gives?

Is there an easy way to quickly pull current (delayed is ok) quotes into
excel other than through the default microsoft moneycentral?

Thanks.

If you don't use the correct symbol, you won't obtain valid information. You
need to learn how to use the symbol lookup feature of moneycentral.msn.com.

It's not unusual for data providers to have somewhat arcane symbology to
represent indices.

I found the following symbols as variations of your IWD and EWW. Hopefully one
of them will "fit".

$IWD.EU iSHARES RUSSELL 1000 VALUE(Estimated Cash Amount Per Creation Unit)
$IWD.NV iSHARES RUSSELL 1000 VALUE(Net Asset Value)
$IWD.SO iSHARES RUSSELL 1000 VALUE(Shares Outstanding)
$IWD.TC iSHARES RUSSELL 1000 VALUE(Total Cash Amount Per Creation Unit)
$IWD.IV iSHARES RUSSELL 1000 VALUE(Underlying Trading Value)


and for the Mexico Index:

$EWW.EU iSHARES MSCI MEXICO INDEX FD(Estimated cash amount per creation unit)
$EWW.IV iSHARES MSCI MEXICO INDEX FD(Indicative Optimized Portfolio Value)
$EWW.NV iSHARES MSCI MEXICO INDEX FD(Net Asset Value)
$EWW.SO iSHARES MSCI MEXICO INDEX FD(Shares Outstanding)
$EWW.TC iSHARES MSCI MEXICO INDEX FD(Total Cash Amount Per Creation Unit)


--ron

 

[Schultz]

Dave, thanks so much. The only problem is I don't know anything about
excel programming. I'll try to figure it out.

But what's a "module"? How do I open one up?

Thanks again!

 

[Schultz]

Ron, thanks. How did you locate those, starting from scratch? I can't seem
to figure it out.

They work in microsoft excel, but I don't see a trading value for EWW, for
example. But these are not really indices, but exchange traded funds
traded on the AMEX! So it seems wierd as heck
that they don't have the trading value!

 

[Schultz]

OK, I figured out how you got those. But its still quite bizarre that they
don't have the current (delayed) trading price, when they trade on the AMEX
just like any other stock. And I really need the current trading price
rather than some proxy.

Thanks fellas.

 

[Gord Dibben]

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP

Dave, thanks so much. The only problem is I don't know anything about
excel programming. I'll try to figure it out.

But what's a "module"? How do I open one up?

Thanks again!

Gord Dibben MS Excel MVP

 

[Ron Rosenfeld]

OK, I figured out how you got those. But its still quite bizarre that they
don't have the current (delayed) trading price, when they trade on the AMEX
just like any other stock. And I really need the current trading price
rather than some proxy.

Thanks fellas.

OK, I did not realize you wanted the ETF. I thought you only wanted the index.
And I don't see a way of getting today's price at the money web site.

But you could perhaps do a web query.

Data/External Data/New Web Query

A browser opens. Go to www.amex.com and enter the ETF Symbol in the quote
window.

Follow the instructions.

You may have to do a separate query for each ETF, or you may be able to combine
them.


--ron