Minggu, 18 Maret 2012

[smf_addin] Digest Number 2149[1 Attachment]

Messages In This Digest (14 Messages)

Messages

1a.

CBOE Symbol List of Weekly Option

Posted by: "JB" jonb0821@comcast.net   jbrown130214

Sat Mar 17, 2012 4:27 am (PDT)



I would like to obtain a list of weekly stock symbols from CBOE site.

The data is in a table at the below listed url

https://www.cboe.com/micro/weeklys/availableweeklys.aspx

Currently I visit the site and download the listing from the available xls download on that same page...

Is it possible to get the list off the page directly?

Thanks in advance for your help...

jb

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

Re: CBOE Symbol List of Weekly Option

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

Sat Mar 17, 2012 4:46 am (PDT)



You don't even need the add-in -- just use a normal EXCEL referral to copy
the data directly from their XLS file. For example:

='https://www.cboe.com/publish/weelkysmf/[weeklysmf.xls]Sheet1'!A1

Just copy down and across as needed.

Otherwise, you can extract the table from the web page with:

=RCHGetHTMLTable("https://www.cboe.com/micro/weeklys/availableweeklys.aspx","(Last
Updated",-1,"",1)

On Sat, Mar 17, 2012 at 4:03 AM, JB <jonb0821@comcast.net> wrote:

> I would like to obtain a list of weekly stock symbols from CBOE site.
>
> The data is in a table at the below listed url
>
> https://www.cboe.com/micro/weeklys/availableweeklys.aspx
>
> Currently I visit the site and download the listing from the available xls
> download on that same page...
>
> Is it possible to get the list off the page directly?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
2a.

s&p 500 symbols list

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

Sat Mar 17, 2012 6:03 am (PDT)



Does anyone have a spreadsheet they are willing to share, with the current s&p 500 symbols list?

Happy St. Patricks Day,

Thanks

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

Re: s&p 500 symbols list

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

Sat Mar 17, 2012 8:10 am (PDT)

[Attachment(s) from dguillett1 included below]



Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@gmail.com

From: rr76012
Sent: Saturday, March 17, 2012 8:03 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] s&p 500 symbols list

Does anyone have a spreadsheet they are willing to share, with the current s&p 500 symbols list?

Happy St. Patricks Day,

Thanks

Attachment(s) from dguillett1

1 of 1 File(s)

Symbols_S&P500.xls
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
3.

Alert when stock market indicator changes

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

Sat Mar 17, 2012 1:39 pm (PDT)



Have Buy/Sell indicators linked to stock price for various companies in an Excel spreadsheet. When the indicator changes to Buy from Sell or vice versa, would like an alarm to sound and message to appear on the screen telling me the Stock whose indicator has changed and what is the new indicator reading.

Has anyone developed such an alert system for their stocks and bonds?

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

Returning Errors: Trend Values for http://www.masterdata.com/

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

Sat Mar 17, 2012 2:05 pm (PDT)



Randy,
I have a file with 92 ETF symbols. The formula works for some of the symbols, but not others. For example, B2 is "BND" on the sheet and returns data. B3 is "BRF" on the sheet and returns an error. Most of the 92 symbols return error. I check the site and find "BRF" and the other problem symbols exits on the site. Can't seem to find out what I'm doing wrong. Any help would be greatly appreciated. I can send a file if that helps.

=SUBSTITUTE(RCHGetTableCell("http://www.masterdata.com/ComponentFrames/"&LEFT(B2,1)&"/"&B2&".htm",1,">Current Trend"),"&nbsp","")

=SUBSTITUTE(RCHGetTableCell("http://www.masterdata.com/ComponentFrames/"&LEFT(B3,1)&"/"&B3&".htm",1,">Current Trend"),"&nbsp","")

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Just do:
>
> =SUBSTITUTE(...formula...,"&nbsp","")
>
> On Tue, Mar 6, 2012 at 4:34 PM, smithjhhic <smithjhhic@...> wrote:
>
> >
> > Thank you for your help. I'm an Excel novice. How do I add the
> > substitute function into the two formulas below?
> >
> > =RCHGetTableCell("http://www.masterdata.com/ComponentFrames/"&LEFT(B8,1)&"/"&B8&".htm",2,">Current
> > Trend")
> >
> > =RCHGetElementNumber($C2,702)
> >
>

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

Re: Returning Errors: Trend Values for http://www.masterdata.com/

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

Sat Mar 17, 2012 2:58 pm (PDT)



Not sure what to tell you. BRF works fine here. I tried a list of 41 and
got 4 "Error" returns -- all 4 were missing the data when I checked the
page...

On Sat, Mar 17, 2012 at 2:05 PM, smithjhhic <smithjhhic@yahoo.com> wrote:

>
> I have a file with 92 ETF symbols. The formula works for some of the
> symbols, but not others. For example, B2 is "BND" on the sheet and returns
> data. B3 is "BRF" on the sheet and returns an error. Most of the 92
> symbols return error. I check the site and find "BRF" and the other
> problem symbols exits on the site. Can't seem to find out what I'm doing
> wrong. Any help would be greatly appreciated. I can send a file if that
> helps.
>
> =SUBSTITUTE(RCHGetTableCell("http://www.masterdata.com/ComponentFrames/"&LEFT(B2,1)&"/"&B2&".htm",1,">Current
> Trend"),"&nbsp","")
>
> =SUBSTITUTE(RCHGetTableCell("http://www.masterdata.com/ComponentFrames/"&LEFT(B3,1)&"/"&B3&".htm",1,">Current
> Trend"),"&nbsp","")
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (13)
4c.

Re: Returning Errors: Trend Values for http://www.masterdata.com/

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

Sat Mar 17, 2012 4:26 pm (PDT)



Randy,
Thank you. I created a new column and reentered the symbols. The formula worked for all the ETFs now. It does not work for all of the symbols when I point back to the original column with symbols. What is strange is some of my other formulas that do work work with the original symbol column will not work when pointed to the new symbol column. I can't seem to tell the difference between the two columns.
Jeff

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Not sure what to tell you. BRF works fine here. I tried a list of 41 and
> got 4 "Error" returns -- all 4 were missing the data when I checked the
> page...
>
> On Sat, Mar 17, 2012 at 2:05 PM, smithjhhic <smithjhhic@...> wrote:
>
> >
> > I have a file with 92 ETF symbols. The formula works for some of the
> > symbols, but not others. For example, B2 is "BND" on the sheet and returns
> > data. B3 is "BRF" on the sheet and returns an error. Most of the 92
> > symbols return error. I check the site and find "BRF" and the other
> > problem symbols exits on the site. Can't seem to find out what I'm doing
> > wrong. Any help would be greatly appreciated. I can send a file if that
> > helps.
> >
> > =SUBSTITUTE(RCHGetTableCell("http://www.masterdata.com/ComponentFrames/"&LEFT(B2,1)&"/"&B2&".htm",1,">Current
> > Trend"),"&nbsp","")
> >
> > =SUBSTITUTE(RCHGetTableCell("http://www.masterdata.com/ComponentFrames/"&LEFT(B3,1)&"/"&B3&".htm",1,">Current
> > Trend"),"&nbsp","")
> >
>

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

Re: Returning Errors: Trend Values for http://www.masterdata.com/

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

Sat Mar 17, 2012 4:45 pm (PDT)



Do the ticker symbols have trailing blanks or trailing unprintable
characters of some kind? That would create a bad URL for the add-in
function.

On Sat, Mar 17, 2012 at 4:26 PM, smithjhhic <smithjhhic@yahoo.com> wrote:

>
> Thank you. I created a new column and reentered the symbols. The formula
> worked for all the ETFs now. It does not work for all of the symbols when
> I point back to the original column with symbols. What is strange is some
> of my other formulas that do work work with the original symbol column will
> not work when pointed to the new symbol column. I can't seem to tell the
> difference between the two columns.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (13)
4e.

Re: Returning Errors: Trend Values for http://www.masterdata.com/

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

Sat Mar 17, 2012 5:14 pm (PDT)



Randy,
Yes, you found my error, I had two trailing blanks. That fixes the problem for the formulas where I was getting errors. What is strange is I now get errors with some of my other formulas when I remove the two trailing blanks (example below).

=RCHGetTableCell("http://www.askstockguru.com/cgi-bin/s?s="&C6,1,">Market Phase:")

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Do the ticker symbols have trailing blanks or trailing unprintable
> characters of some kind? That would create a bad URL for the add-in
> function.
>
> On Sat, Mar 17, 2012 at 4:26 PM, smithjhhic <smithjhhic@...> wrote:
>
> >
> > Thank you. I created a new column and reentered the symbols. The formula
> > worked for all the ETFs now. It does not work for all of the symbols when
> > I point back to the original column with symbols. What is strange is some
> > of my other formulas that do work work with the original symbol column will
> > not work when pointed to the new symbol column. I can't seem to tell the
> > difference between the two columns.
> >
>

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

Re: Returning Errors: Trend Values for http://www.masterdata.com/

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

Sat Mar 17, 2012 8:33 pm (PDT)



I also get a number of errors from them, but when I went to the web site,
it said the ETF wasn't followed by them.

But I don't know why you would have gotten data with trailing blanks...

When you're changing things around like this, it sometimes helps to run the
smfForceRecalculation macro, to make sure you have a "fresh" copies of the
web pages after your changes.

On Sat, Mar 17, 2012 at 5:14 PM, smithjhhic <smithjhhic@yahoo.com> wrote:

>
> Yes, you found my error, I had two trailing blanks. That fixes the
> problem for the formulas where I was getting errors. What is strange is I
> now get errors with some of my other formulas when I remove the two
> trailing blanks (example below).
>
> =RCHGetTableCell("http://www.askstockguru.com/cgi-bin/s?s="&C6,1,">Market
> Phase:")
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (13)
5a.

mutual fund total return questions

Posted by: "jhyde.oregon" jhyde@peak.org   jhyde.oregon

Sat Mar 17, 2012 3:00 pm (PDT)



I just discovered your SMF addin today. It's really useful. Thank you for creating it and making it available. I'm interested in mutual fund total return data, and have several questions.

1. SMF supports Morningstar quarterly total returns for 7 years (back to 2005), but the M* ticker/performance page (I'm looking at VFINX) only has quarterly return data for 4 years (back to 2008). Do you get the data from a different page? Is quarterly return data for more than 7 years available?

2. The M* ticker/chart page provides total return (growth) data for user specified start and end dates. Can this be retrieved with SMF? If not, is it a capability you would consider adding in the future?

Best regards,
John

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

Re: mutual fund total return questions

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

Sat Mar 17, 2012 3:07 pm (PDT)



For item (1), the total returns data comes from the print report page:

http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=VFINX

The add-in can't extract data from their normal page because the data isn't
there -- they generate the web page dynamically, so there is no data in the
delivered web page source code for the add-in to extract.

For item (2), I don't think it can be done, as the data is delivered
dynamically. The add-in requires a bookmarkable URL (one that you can enter
in your browser address bar and gets you directly to the desired data.

In general, I just use Yahoo historical quotes for total returns data,
since it has adjusted closing prices. I typically use the
smfPricesByDates() function which allows me to get those quotes for any
specified dates. Then I just compute the percentage return.

On Sat, Mar 17, 2012 at 2:58 PM, jhyde.oregon <jhyde@peak.org> wrote:

> I just discovered your SMF addin today. It's really useful. Thank you for
> creating it and making it available. I'm interested in mutual fund total
> return data, and have several questions.
>
> 1. SMF supports Morningstar quarterly total returns for 7 years (back to
> 2005), but the M* ticker/performance page (I'm looking at VFINX) only has
> quarterly return data for 4 years (back to 2008). Do you get the data from
> a different page? Is quarterly return data for more than 7 years available?
>
> 2. The M* ticker/chart page provides total return (growth) data for user
> specified start and end dates. Can this be retrieved with SMF? If not, is
> it a capability you would consider adding in the future?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
5c.

Re: mutual fund total return questions

Posted by: "jhyde.oregon" jhyde@peak.org   jhyde.oregon

Sat Mar 17, 2012 6:09 pm (PDT)





Thank you, smfPricesByDates() is just what I need. I get the same results as SMF-Template-Morningstar-Total-Return.xls, and it's much more flexible.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> For item (1), the total returns data comes from the print report page:
>
> http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=VFINX
>
> The add-in can't extract data from their normal page because the data isn't
> there -- they generate the web page dynamically, so there is no data in the
> delivered web page source code for the add-in to extract.
>
> For item (2), I don't think it can be done, as the data is delivered
> dynamically. The add-in requires a bookmarkable URL (one that you can enter
> in your browser address bar and gets you directly to the desired data.
>
> In general, I just use Yahoo historical quotes for total returns data,
> since it has adjusted closing prices. I typically use the
> smfPricesByDates() function which allows me to get those quotes for any
> specified dates. Then I just compute the percentage return.
>
> On Sat, Mar 17, 2012 at 2:58 PM, jhyde.oregon <jhyde@...> wrote:
>
> > I just discovered your SMF addin today. It's really useful. Thank you for
> > creating it and making it available. I'm interested in mutual fund total
> > return data, and have several questions.
> >
> > 1. SMF supports Morningstar quarterly total returns for 7 years (back to
> > 2005), but the M* ticker/performance page (I'm looking at VFINX) only has
> > quarterly return data for 4 years (back to 2008). Do you get the data from
> > a different page? Is quarterly return data for more than 7 years available?
> >
> > 2. The M* ticker/chart page provides total return (growth) data for user
> > specified start and end dates. Can this be retrieved with SMF? If not, is
> > it a capability you would consider adding in the future?
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
Recent Activity
Visit Your Group
Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! News

Odd News

You won't believe

it, but it's true

Need traffic?

Drive customers

With search ads

on Yahoo!

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 01.48

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)