Jumat, 29 Juli 2011

[smf_addin] Digest Number 1901

Messages In This Digest (10 Messages)

Messages

1a.

creating a yahoo ticker search

Posted by: "Bartjan Van Hulten" vanhulten@yahoo.com   vanhulten

Thu Jul 28, 2011 4:58 am (PDT)



Hi Randy,
Using your add-in i have now successfully created a bloomberg ticker search which lists the tickers and the exchanges. I have found bloomberg data more consistent across various countries. Also, using the bloomberg tickers, i have been able to deduct the tickers to use with Barrons, which provides good international financials data.

For stock quotes, yahoo is the best source. For this i am now trying to create a yahoo ticker search - and with a table linking the main exchange codes, i will be able to automatically reconcile bloomberg with yahoo tickers. However, i have been having some trouble with getting the table out in excel. The link is

http://finance.search.yahoo.com/search?p=meda&ei=UTF-8&fr=fin-v1&section=quot&fltr=type:Stock when i look with the term "meda" but i can replace that for example with "pfe"

I have been using the smftagcontent and the rchgettablecell, as well as using smfstrextr and also excel functions substitute, right, mid, left etc. However, to no avail as i cant seem to get the table in excel the way it is presented in yahoo. Would you have any suggestions? I am interested in getting "name" "symbol" and "exchange" out from that page.

Many thanks!
Bartjan

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
1b.

Re: creating a yahoo ticker search

Posted by: "dguillett1" dguillett1@gmail.com   donaldb36

Thu Jul 28, 2011 6:52 am (PDT)



If you just want to goto Yahoo to look up the symbol, try this
Private Sub GetYahooSymbol()
ActiveWorkbook.FollowHyperlink Address:= _
"http://finance.yahoo.com/lookup"
End Sub

To goto a yahoo page for a symbol typed in a cell

Private Sub GotoYahoo()
ActiveWorkbook.FollowHyperlink Address:= _
"http://finance.yahoo.com/q?s=" & ActiveCell.Value & ""
End Sub

or in the sheet module
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim mr As Long
mr = [symbolrow]
If Target.Column = 3 And Target.Row > mr Then
ActiveWorkbook.FollowHyperlink Address:= _
"http://finance.yahoo.com/q?s=" & Target.Value & ""
End If

From: Bartjan Van Hulten
Sent: Thursday, July 28, 2011 6:58 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] creating a yahoo ticker search

Hi Randy,
Using your add-in i have now successfully created a bloomberg ticker search which lists the tickers and the exchanges. I have found bloomberg data more consistent across various countries. Also, using the bloomberg tickers, i have been able to deduct the tickers to use with Barrons, which provides good international financials data.

For stock quotes, yahoo is the best source. For this i am now trying to create a yahoo ticker search - and with a table linking the main exchange codes, i will be able to automatically reconcile bloomberg with yahoo tickers. However, i have been having some trouble with getting the table out in excel. The link is

http://finance.search.yahoo.com/search?p=meda&ei=UTF-8&fr=fin-v1&section=quot&fltr=type:Stock when i look with the term "meda" but i can replace that for example with "pfe"

I have been using the smftagcontent and the rchgettablecell, as well as using smfstrextr and also excel functions substitute, right, mid, left etc. However, to no avail as i cant seem to get the table in excel the way it is presented in yahoo. Would you have any suggestions? I am interested in getting "name" "symbol" and "exchange" out from that page.

Many thanks!
Bartjan

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
1c.

Re: creating a yahoo ticker search

Posted by: "Bartjan Van Hulten" vanhulten@yahoo.com   vanhulten

Thu Jul 28, 2011 8:14 am (PDT)



The idea is not to hyperlink to the yahoo address. The idea is to be able to replicate the yahoo table into excel, using the search string. With that, and a simple table that links up the exchange symbol in Yahoo, and that in Bloomberg (e.g. for Norway in Bloomberg is NO, while in Yahoo is OL, whereas US in bloomberg would be NYQ), one can easily and automatically link up bloomberg and yahoo ticker formats and thus use both sources for datainput based on a single ticker entry.

--- In smf_addin@yahoogroups.com, "dguillett1" <dguillett1@...> wrote:
>
> If you just want to goto Yahoo to look up the symbol, try this
> Private Sub GetYahooSymbol()
> ActiveWorkbook.FollowHyperlink Address:= _
> "http://finance.yahoo.com/lookup"
> End Sub
>
> To goto a yahoo page for a symbol typed in a cell
>
> Private Sub GotoYahoo()
> ActiveWorkbook.FollowHyperlink Address:= _
> "http://finance.yahoo.com/q?s=" & ActiveCell.Value & ""
> End Sub
>
> or in the sheet module
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
> Dim mr As Long
> mr = [symbolrow]
> If Target.Column = 3 And Target.Row > mr Then
> ActiveWorkbook.FollowHyperlink Address:= _
> "http://finance.yahoo.com/q?s=" & Target.Value & ""
> End If
>
> From: Bartjan Van Hulten
> Sent: Thursday, July 28, 2011 6:58 AM
> To: smf_addin@yahoogroups.com
> Subject: [smf_addin] creating a yahoo ticker search
>
>
> Hi Randy,
> Using your add-in i have now successfully created a bloomberg ticker search which lists the tickers and the exchanges. I have found bloomberg data more consistent across various countries. Also, using the bloomberg tickers, i have been able to deduct the tickers to use with Barrons, which provides good international financials data.
>
> For stock quotes, yahoo is the best source. For this i am now trying to create a yahoo ticker search - and with a table linking the main exchange codes, i will be able to automatically reconcile bloomberg with yahoo tickers. However, i have been having some trouble with getting the table out in excel. The link is
>
> http://finance.search.yahoo.com/search?p=meda&ei=UTF-8&fr=fin-v1&section=quot&fltr=type:Stock when i look with the term "meda" but i can replace that for example with "pfe"
>
> I have been using the smftagcontent and the rchgettablecell, as well as using smfstrextr and also excel functions substitute, right, mid, left etc. However, to no avail as i cant seem to get the table in excel the way it is presented in yahoo. Would you have any suggestions? I am interested in getting "name" "symbol" and "exchange" out from that page.
>
> Many thanks!
> Bartjan
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
1d.

Re: creating a yahoo ticker search

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Thu Jul 28, 2011 8:21 am (PDT)



Do a search of the group's message archives using the base Yahoo URL -- it's
been discussed several times in the past.

On Thu, Jul 28, 2011 at 4:58 AM, Bartjan Van Hulten <vanhulten@yahoo.com>wrote:

> Hi Randy,
> Using your add-in i have now successfully created a bloomberg ticker search
> which lists the tickers and the exchanges. I have found bloomberg data more
> consistent across various countries. Also, using the bloomberg tickers, i
> have been able to deduct the tickers to use with Barrons, which provides
> good international financials data.
>
> For stock quotes, yahoo is the best source. For this i am now trying to
> create a yahoo ticker search - and with a table linking the main exchange
> codes, i will be able to automatically reconcile bloomberg with yahoo
> tickers. However, i have been having some trouble with getting the table out
> in excel. The link is
>
>
> http://finance.search.yahoo.com/search?p=meda&ei=UTF-8&fr=fin-v1&section=quot&fltr=type:Stock when i look with the term "meda" but i can replace that for example with
> "pfe"
>
> I have been using the smftagcontent and the rchgettablecell, as well as
> using smfstrextr and also excel functions substitute, right, mid, left etc.
> However, to no avail as i cant seem to get the table in excel the way it is
> presented in yahoo. Would you have any suggestions? I am interested in
> getting "name" "symbol" and "exchange" out from that page.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
2a.

Peer Comparison- Ratio.xls File not Working

Posted by: "rdenotari" rdenotari@yahoo.com   rdenotari

Thu Jul 28, 2011 8:19 am (PDT)



Hi everyone,

I'm just looking for a simple peer comparison table. In the files section there's the Peer Comparison- Ratio.xls file which uses "=RCHGetElementNumber(TICKER,13626)" formula. I noticed this is supposed to pull Reuters data, but I am instead seeing all "Error".

Is there another file similar to this one out there, or a quick way to fix this? I don't want to reinvent the wheel here, so if anyone has any suggestions, please help!

Thank you,

Rob

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
2b.

Re: Peer Comparison- Ratio.xls File not Working

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Thu Jul 28, 2011 8:46 am (PDT)



You probably just need the new smf-elements-5.txt file from the "Works in
Progress" folder of the files area of the group. Reuters changed their
website structure earlier this year, so I had to redefine all of the Reuters
element definitions.

On Thu, Jul 28, 2011 at 6:32 AM, rdenotari <rdenotari@yahoo.com> wrote:

>
> I'm just looking for a simple peer comparison table. In the files section
> there's the Peer Comparison- Ratio.xls file which uses
> "=RCHGetElementNumber(TICKER,13626)" formula. I noticed this is supposed to
> pull Reuters data, but I am instead seeing all "Error".
>
> Is there another file similar to this one out there, or a quick way to fix
> this? I don't want to reinvent the wheel here, so if anyone has any
> suggestions, please help!
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
3a.

Does this add-in apply to securities as well as common stock?

Posted by: "adam klestadt" klaymakerk2000@yahoo.com   klaymakerk2000

Thu Jul 28, 2011 12:10 pm (PDT)



Is there a function in which I can plug in a CUSIP and it will return ratings (on both the S&P and Moody's scales)?

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
3b.

Re: Does this add-in apply to securities as well as common stock?

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Thu Jul 28, 2011 12:13 pm (PDT)



It would depend on the source of your data elements. Nothing is
pre-programmed for them.

Likely, you would use either the RCHGetTableCell() or smfGetTagContent()
functions.

On Thu, Jul 28, 2011 at 10:58 AM, adam klestadt <klaymakerk2000@yahoo.com>wrote:

> Is there a function in which I can plug in a CUSIP and it will return
> ratings (on both the S&P and Moody's scales)?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
4a.

Re: Quotes on Stock Options including Greeks

Posted by: "Miguel" spectratecharchitecture@gmail.com   spectratecharchitecture

Thu Jul 28, 2011 12:11 pm (PDT)



Hello,
I am trying to do the same thing, get options quotes to excel, but I am having trouble understanding how these formulas/functions work (the RCGetTableCell). I have very basic VBA skills, I was wondering if i can see your code to get those options prices so I can reverse engineer them and see how they work? I downloaded the examples but i cant get them to work either...

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Here's what I just sent to someone else (one more reason I like to keep such
> discussions on the Yahoo group):
>
> Examples of the formulas I was using in a workbook I had a while back:
>
> Item Formula Strike =RCHGetTableCell("
> https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Symbol=IWM&ChainType=3&Range=0&lstMonths=2/20/2010;1
> ",1,".DIWBK",,,,-1) Last =RCHGetTableCell("
> https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Symbol=IWM&ChainType=3&Range=0&lstMonths=2/20/2010;1
> ",1,".DIWBK") Bid =RCHGetTableCell("
> https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Symbol=IWM&ChainType=3&Range=0&lstMonths=2/20/2010;1
> ",2,".DIWBK") Ask =RCHGetTableCell("
> https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Symbol=IWM&ChainType=3&Range=0&lstMonths=2/20/2010;1
> ",3,".DIWBK") Theoretical Value =RCHGetTableCell("
> https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Symbol=IWM&ChainType=3&Range=0&lstMonths=2/20/2010;1
> ",4,".DIWBK") Open Interest =RCHGetTableCell("
> https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Symbol=IWM&ChainType=3&Range=0&lstMonths=2/20/2010;1
> ",5,".DIWBK") Delta =RCHGetTableCell("
> https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Symbol=IWM&ChainType=3&Range=0&lstMonths=2/20/2010;1
> ",6,".DIWBK") Gamma =RCHGetTableCell("
> https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Symbol=IWM&ChainType=3&Range=0&lstMonths=2/20/2010;1
> ",7,".DIWBK") Rho =RCHGetTableCell("
> https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Symbol=IWM&ChainType=3&Range=0&lstMonths=2/20/2010;1
> ",8,".DIWBK") Theta =RCHGetTableCell("
> https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Symbol=IWM&ChainType=3&Range=0&lstMonths=2/20/2010;1
> ",9,".DIWBK") Vega =RCHGetTableCell("
> https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Symbol=IWM&ChainType=3&Range=0&lstMonths=2/20/2010;1
> ",10,".DIWBK") Implied Volatility =RCHGetTableCell("
> https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Symbol=IWM&ChainType=14&Range=0&lstMonths=2/20/2010;1
> ",5,".DIWBK")
> You'll need to change the underlying ticker symbol (i.e. "Symbol=IWM"),
> expiration date (i.e. "lstMonths=2/20/2010;1"), and option ticker symbol
> (i.e. ".DIWBK") as needed. Also, note that the implied volatility is coming
> from a different web page than everything else (i.e. "ChainType=14"). OX has
> a number of different web page displays for options.
>
> On Wed, Jan 20, 2010 at 12:33 PM, Dwight Pearson <dwightpearson@...>wrote:
>
> >
> > Thanks Randy. True indeed these URLs display option chains from
> > OptionsXpress with the Greeks and IV. Now what would be the next logical
> > step in bringing this info into Excel, as done by SMF Add-in functions, for
> > a specific option?
> >
> > Dwight Pearson
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)
4b.

Re: Quotes on Stock Options including Greeks

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Thu Jul 28, 2011 12:15 pm (PDT)



Take a look at the smfGetOptionQuotes() function.

The VBA project is not password-protected, so you can access the source
code.

The examples you were looking at were outdated because option symbology has
changed significantly since then.

On Thu, Jul 28, 2011 at 10:43 AM, Miguel
<spectratecharchitecture@gmail.com>wrote:

>
> I am trying to do the same thing, get options quotes to excel, but I am
> having trouble understanding how these formulas/functions work (the
> RCGetTableCell). I have very basic VBA skills, I was wondering if i can see
> your code to get those options prices so I can reverse engineer them and see
> how they work? I downloaded the examples but i cant get them to work
> either...
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)
Recent Activity
Visit Your Group
Ads on Yahoo!

Learn more now.

Reach customers

searching for you.

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! News

Odd News

You won't believe

it, but it's true

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web
Diposting oleh EXCEL FEED LEARNING di 03.48

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)