yup, something is wrong there.  I need to double check my math. Will post an
updated version when fixed.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I don't think your aggregates are working properly.  For example, in cell
> G8, you have:
>
>
=(SUM(($E$11:$E$7010=$C8)*(IF(G$11:G$7010<>"",G$11:G$7010,0))*(IF($G$11:$G$7010<\
>"",$G$11:$G$7010,0))))/(SUM(($E$11:$E$7010=$C8)*(IF($G$11:$G$7010<>"",$G$11:$G$\
7010,0))))
>
> Don't you want:
>
>
=SUMPRODUCT(G$10:G$7009,--($E$10:$E$7009=$C8))/SUMPRODUCT(----(G$10:G$7009<>""),\
--($E$10:$E$7009=$C8))
>
> I don't understand why you multiply these two terms in your numerator:
>
> (IF(G$11:G$7010<>"",G$11:G$7010,0))
> (IF($G$11:$G$7010<>"",$G$11:$G$7010,0))
>
> Similarly for G6 and G7:
>
> G6: =SUMPRODUCT(G$10:G$7009)/SUMPRODUCT(--(G$10:G$7009<>""))
> G7:
>
=SUMPRODUCT(G$10:G$7009,--($D$10:$D$7009=$C7))/SUMPRODUCT(--(G$10:G$7009<>""),--\
($D$10:$D$7009=$C7))
>
> Are am I misunderstanding their use?
>
> On Tue, Dec 22, 2009 at 5:35 PM, bobsledproductions <JPeters3515@...>wrote:
>
> >
> > I didn't know that they could be combined into one call, thanks for the
> > heads up......you are the master.  Thanks.
> >
> > I need all the data for the aggregates, if they are of any value.
> >
>

FWIW...

The closest/best I have found is
http://www.crimsonmind.com/options/HistoryData.aspx?d=12/18/2009

Merry Christmas group and happy trading in 2010!

--- In smf_addin@yahoogroups.com, "WildKutie72" <presentsigi@...> wrote:
>
> I've been googling some, and it seems to be only included in packages for
$$...  But it does mean however the data should be possible to retrieve with
excel, see screenshot on the following link, scroll untill below:
>
> http://www.ozgrid.com/Services/excel-options-download.htm
>
> Ozrid has a lot of awesome stuff too btw but no free open interests... And my
code is not yet advanced in this way I'll be able to fetch it for free myself
>
> --- In smf_addin@yahoogroups.com, "SundermanJ" <josephwsunderman@> wrote:
> >
> > All -
> >
> > Does anyone have a source for getting open interest for an individual
option?  For example on the GE 17.50 call, I want to be able to view the day-to
day-open interest changes for a week or two time frame.
> >
> > All suggestions are welcomed!
> >
> > Thanks!
> >
>

Hi folks,

1. I'm trying to setup the Expense Ratio column for various ETFs.  If I'm not
mistaken, the proper function is "=RCHGetElementNumber("XLF",5079).  This gives
me an Error, not the Total Expense Ratio of 0.22% shown on XLK's Profile page on
Yahoo!.

2. Likewise, what's the best way to find the 2006, 2007, 2008 performance for a
fund in terms of the stock prices.  What's the easiest function to input?

Any thoughts?

I didn't know that they could be combined into one call, thanks for the heads
up......you are the master.  Thanks.

I need all the data for the aggregates, if they are of any value.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Since it could be just one Internet access, I don't see it as that much of a
> problem.
>
> I don't know why you used the 6 different URL's though.  You can get all the
> data with one formula:
>
> =smfGetCSVFile("
>
http://finviz.com/export.ashx?v=151&c=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,\
18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44\
,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68
> ")
>
> That's the "Custom" tab with all 68 fields requested.  You can also do
> selections easily there.  For example, to select the "Basic Materials"
> sector, you'd just add something like:
>
> &f=sec_basicmaterials
>
> Or, to select a set of tickers:
>
> &t=MMM,IBM,SPY
>
> It's a very flexible tool.
>
> In general, though, I still think you'd be better off using the screener
> online.  Once you have a screen defined, you can always use the
> smfGetCSVFile() function to export the results any time you want.  I do it
> with the Zacks screener as well.
>
> On Tue, Dec 22, 2009 at 12:59 PM, bobsledproductions
> <JPeters3515@...>wrote:
>
> > Just uploaded a stock screener workbook that pulls data from finviz.com,
> > then allows you to filter (screen) on it from within Excel.  It also allows
> > you to calculate market cap weighted aggregates at the industry and sector
> > levels as well.  (Yahoo's aggregates provided in the Industry Overview
> > workbook were for far fewer data items.)
> >
> > The workbook is quite large, and has caused Excel to freeze a bit (the
> > aggregate calculations are quite expensive), but hopefully something of
> > interest to the group.  Again, an SMF Lite implementation.
> >
> > Randy, please let me know if this violates the "spirit" of your addin, as I
> > am downloading virtually everything from the finviz screener engine.  If so,
> > let me know, and I'll remove the workbook.
> >
>

Many thanks for helping us do this.
Now working AOK.

Paul

I've been googling some, and it seems to be only included in packages for $$... 
But it does mean however the data should be possible to retrieve with excel, see
screenshot on the following link, scroll untill below:

http://www.ozgrid.com/Services/excel-options-download.htm

Ozrid has a lot of awesome stuff too btw but no free open interests... And my
code is not yet advanced in this way I'll be able to fetch it for free myself

--- In smf_addin@yahoogroups.com, "SundermanJ" <josephwsunderman@...> wrote:
>
> All -
>
> Does anyone have a source for getting open interest for an individual option? 
For example on the GE 17.50 call, I want to be able to view the day-to day-open
interest changes for a week or two time frame.
>
> All suggestions are welcomed!
>
> Thanks!
>

Many great spreadsheets!, but nothing with open interest.

Thanks

--- In smf_addin@yahoogroups.com, "donald" <donalddon5@...> wrote:
>
> You will find one here that will give you that information.
> http://www.gummy-stuff.org/Excel/
>
> --- In smf_addin@yahoogroups.com, "SundermanJ" <josephwsunderman@> wrote:
> >
> > Thanks Sigi, but this spreadsheet calculates the option premium and
strategy, but not the open interest.  Open interest is simply the number of
contracts outstanding on a given option.  Thus, it is not a calculation, but
rather a data figure from the exchanges that some vendors may have.
> >
> > --- In smf_addin@yahoogroups.com, "WildKutie72" <presentsigi@> wrote:
> > >
> > > http://www.optiontradingtips.com/pricing/free-spreadsheet.html
> > > And other stuff - I think it might be helpful, I am not into Options
though (just read what it is, a beginner but don't know what open interest is),
but the site looks quite good... Maybe you'll fetch something there.
> > >
> > > Regards,
> > >
> > > Sigi
> > >
> > >
> > > --- In smf_addin@yahoogroups.com, "SundermanJ" <josephwsunderman@> wrote:
> > > >
> > > > All -
> > > >
> > > > Does anyone have a source for getting open interest for an individual
option?  For example on the GE 17.50 call, I want to be able to view the day-to
day-open interest changes for a week or two time frame.
> > >
> > > >
> > > > All suggestions are welcomed!
> > > >
> > > > Thanks!
> > > >
> > >
> >
>

You will find one here that will give you that information.
http://www.gummy-stuff.org/Excel/

--- In smf_addin@yahoogroups.com, "SundermanJ" <josephwsunderman@...> wrote:
>
> Thanks Sigi, but this spreadsheet calculates the option premium and strategy,
but not the open interest.  Open interest is simply the number of contracts
outstanding on a given option.  Thus, it is not a calculation, but rather a data
figure from the exchanges that some vendors may have.
>
> --- In smf_addin@yahoogroups.com, "WildKutie72" <presentsigi@> wrote:
> >
> > http://www.optiontradingtips.com/pricing/free-spreadsheet.html
> > And other stuff - I think it might be helpful, I am not into Options though
(just read what it is, a beginner but don't know what open interest is), but the
site looks quite good... Maybe you'll fetch something there.
> >
> > Regards,
> >
> > Sigi
> >
> >
> > --- In smf_addin@yahoogroups.com, "SundermanJ" <josephwsunderman@> wrote:
> > >
> > > All -
> > >
> > > Does anyone have a source for getting open interest for an individual
option?  For example on the GE 17.50 call, I want to be able to view the day-to
day-open interest changes for a week or two time frame.
> >
> > >
> > > All suggestions are welcomed!
> > >
> > > Thanks!
> > >
> >
>

I'd like to use the =RCHGetTableCell function to extract the first
Out-of-the-Money option symbol for a particualr stock from the stock's option
chain.  For example, today 12/22/09, AAPL closed at 200.36.  The first out of
the money option, according to the Yahoo Finance table
http://finance.yahoo.com/q/op?s=AAPL, is the Jan10 $210 call, symbol AJLAV.X.  I
want to put that symbol (not the value) in Excel.  I'm having trouble figuring
out the documentation for RCHGetTable to make this happen.  A complication is
that the option chains for various stocks place this Out-of-the-Money option in
different locatons, depending on how many options are offered, their strike
interval, etc.
Perhaps there is a way to use the stock's closing price as a starding point for
finding the next available option, (>200.36) then jump to the symbol for that
value.
Can you walk me through this?
Thanks

Thanks Sigi, but this spreadsheet calculates the option premium and strategy,
but not the open interest.  Open interest is simply the number of contracts
outstanding on a given option.  Thus, it is not a calculation, but rather a data
figure from the exchanges that some vendors may have.

--- In smf_addin@yahoogroups.com, "WildKutie72" <presentsigi@...> wrote:
>
> http://www.optiontradingtips.com/pricing/free-spreadsheet.html
> And other stuff - I think it might be helpful, I am not into Options though
(just read what it is, a beginner but don't know what open interest is), but the
site looks quite good... Maybe you'll fetch something there.
>
> Regards,
>
> Sigi
>
>
> --- In smf_addin@yahoogroups.com, "SundermanJ" <josephwsunderman@> wrote:
> >
> > All -
> >
> > Does anyone have a source for getting open interest for an individual
option?  For example on the GE 17.50 call, I want to be able to view the day-to
day-open interest changes for a week or two time frame.
>
> >
> > All suggestions are welcomed!
> >
> > Thanks!
> >
>

http://www.optiontradingtips.com/pricing/free-spreadsheet.html
And other stuff - I think it might be helpful, I am not into Options though
(just read what it is, a beginner but don't know what open interest is), but the
site looks quite good... Maybe you'll fetch something there.

Regards,

Sigi


--- In smf_addin@yahoogroups.com, "SundermanJ" <josephwsunderman@...> wrote:
>
> All -
>
> Does anyone have a source for getting open interest for an individual option? 
For example on the GE 17.50 call, I want to be able to view the day-to day-open
interest changes for a week or two time frame.

>
> All suggestions are welcomed!
>
> Thanks!
>

All -

Does anyone have a source for getting open interest for an individual option? 
For example on the GE 17.50 call, I want to be able to view the day-to day-open
interest changes for a week or two time frame.

All suggestions are welcomed!

Thanks!

Just uploaded a stock screener workbook that pulls data from finviz.com, then
allows you to filter (screen) on it from within Excel.  It also allows you to
calculate market cap weighted aggregates at the industry and sector levels as
well.  (Yahoo's aggregates provided in the Industry Overview workbook were for
far fewer data items.)

The workbook is quite large, and has caused Excel to freeze a bit (the aggregate
calculations are quite expensive), but hopefully something of interest to the
group.  Again, an SMF Lite implementation.

Randy, please let me know if this violates the "spirit" of your addin, as I am
downloading virtually everything from the finviz screener engine.  If so, let me
know, and I'll remove the workbook.

Hello,

This email message is a notification to let you know that
a file has been uploaded to the Files area of the smf_addin
group.

   File        : /Uploads by forum members/Toteboard.net/StockScreener.zip
   Uploaded by : bobsledproductions <JPeters3515@...>
   Description : Excel based stock screener.

You can access this file at the URL:
http://groups.yahoo.com/group/smf_addin/files/Uploads%20by%20forum%20members/Tot\
eboard.net/StockScreener.zip

To learn more about file sharing for your group, please visit:
http://help.yahoo.com/l/us/yahoo/groups/original/members/forms/general.htmlfiles

Regards,

bobsledproductions <JPeters3515@...>

Thank you so much for your prompt reply and support!
My stock analysis powers are now complete. I'm smiling again.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> You need more than 14 days of data to compute the 14-3-1 stochastic numbers
> correctly.  For STO, %K, and %D individually, you just need to specify two
> parameters for the INDEX function since the smfTech() function returns a
> multi-dimensional array:
>
>    - STO:
>   
=INDEX(smfTech(RCHGetYahooHistory("MSFT",,,,,,,,,,1,1,21,6),"STO",14,3,1),21,1)
>
>    - %K:
>   
=INDEX(smfTech(RCHGetYahooHistory("MSFT",,,,,,,,,,1,1,21,6),"STO",14,3,1),21,2)
>
>    - %D:
>   
=INDEX(smfTech(RCHGetYahooHistory("MSFT",,,,,,,,,,1,1,21,6),"STO",14,3,1),21,3)
>
> Your original formula could have gotten all three items at once, but by
> entering it in a single cell, you just got the first element of the 21st
> row.   If you array-enter it over a 3-column by 1-row range, you will get
> all three items from the 21st row of the multi-dimensional array.  With the
> correction for number of days to retrieve:
>
> =INDEX(smftech(RCHGetYahooHistory("MSFT",,,,,,,,,,1,1,21,6),"STO",14,3,1),21)
>
> The BB(20,2) would just be the 20-day SMA +/- twice the 20-day standard
> deviation:
>
>    - Top:
>    =INDEX(smftech(RCHGetYahooHistory("MSFT",,,,,,,,,,1,1,21,6),"SMA",20),21)
>    +2*STDEVP(RCHGetYahooHistory("MSFT",,,,,,,,"A",0,,,20,1))
>
>    - Mid:
>    =INDEX(smftech(RCHGetYahooHistory("MSFT",,,,,,,,,,1,1,21,6),"SMA",20),21)
>
>    - Bot:
>    =INDEX(smftech(RCHGetYahooHistory("MSFT",,,,,,,,,,1,1,21,6),"SMA",20),21)
>    -2*STDEVP(RCHGetYahooHistory("MSFT",,,,,,,,"A",0,,,20,1))
>
> Whew.  Been a while since I looked at that function. :)
>
> Note that each invocation of RCHGetYahooHistory() in the above formulas
> represents a separate Internet access.
>
> On Sun, Dec 20, 2009 at 5:14 PM, john_smithman <john_smithman@...>wrote:
>
> > In an earlier message, you recommended another company's ADD-IN for tech
> > functions. I was unable to load the TA-LIB add-in, so I am learning how to
> > use your Tech tool.
> >
> > Please help me figure out how to get the Stochastics for one day.
> >
> > If the SMA(10) for MSFT is
> > =INDEX(smftech(RCHGetYahooHistory("MSFT",,,,,,,,,,1,1,11,6),"SMA",10),11) =
> > 29.898
> >
> > Then ' The STO 14,3,1 for MSFT is
> >
=INDEX(smftech(RCHGetYahooHistory("MSFT",,,,,,,,,,1,1,14,6),"STO",14,3,1),14)
> > = 65.31609
> >
> > Have I written it out properly?
> >
> > How do I get the %K and %D as well?
> >
> > Also, the full sheet calculations show 72.12616, 92.5, 72.12616 for
> > 18DEC2009 as the STO, %K and %D. This doesn't match the single day function
> > result above.
> >
> > Please help me understand how to use the Tech function to get three results
> > for one day.
> >
> > Also, is it possible to calculate the Bollinger Bands with your function?
> >
>

On Moneycentral.msn.com, OPAL ENERGY (OPA.V) is obtained with CA:OPA.
On Stockcharts.com, they use OPA.V  and CWB.TO (Canadian Western Bank)


--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I have all of the Yahoo ".TO" mappings.  I don't even see Opal Energy on MSN
> (well, OPAFF, but I don't see a pattern to map there) or AdvFN.  The "CA:"
> and "T." are what the Yahoo ".TO" symbols map to, so they're covered.
>
> And there are rules going from Yahoo to MSN for Preferred stocks and
> options.
>
> What I need is examples of Yahoo ticker symbols and what they are on other
> of the RCHGetElementNumber() data sources -- AdvFN, Morningstar, Reuters,
> Earnings.com, Google, BarCharts.com, StockHouse, StockCharts, and Zacks.
> Then I can add a mapping rule for them
>
> On Sun, Dec 20, 2009 at 10:23 PM, john_smithman <john_smithman@...>wrote:
>
> > There are four standards(?) used for Canadian Stocks: CMG.TO (for Toronto
> > Exchange stocks on most Sites); OPA.V (Venture Exchange Stocks); CA:CLM
> > (probably the second most prevalent for Canadian Stocks); and sometimes
> > T.PBG (e.g., on Investools.com).
> >
> > John
> >
>

I know there's an element number that returns a ticker's industry; but is there
an element number that returns the Company's name?

John

Could someone help me with the code for getting IBD scores. Thanks, David

Check Morningstar, the initial request for the quote page is XTSE:VTR, but
subsequent requests to ratios, performance, financials, valuation, etc. are all
symbol=VTR&Country=CAN.

Looks like there is a bug when accessing the Insiders page as it doesn't carry
over the country code.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> All of the Canadian mappings already work fine, as far as I can tell.
> Actually, your chart was incorrect -- I believe Morningstar uses "XTSE:xxxx"
> and Zacks uses "T:xxxx" for Canadian.
>
>    Case: 2 1    Description: Options Canadian Companies    Example: MMM Jan
> 2010 $80 Call VOLTA RESOURCES INC           Actual Yahoo: VMUAP.X VTR.TO
> MSN: .VMUAP CA:VTR    ADvFN: N/A TSE:VTR    Morningstar: N/A XTSE:VTR
> Reuters: N/A VTR.TO    Earnings.com: N/A VTR.TO    Google: N/A TSE:VTR
> Barchart.com: N/A VTR.TO    StockHouse: N/A T.VTR    StockCharts: N/A VTR.TO
>   Zacks: N/A T.VTR           Translated Yahoo: VMUAP.X VTR.TO    MSN: .VMUAP
> CA:VTR    ADvFN: N/A TSE:VTR    Morningstar: N/A XTSE:VTR    Reuters: N/A
> VTR.TO    Earnings.com: N/A VTR.TO    Google: N/A TSE:VTR    Barchart.com:
> N/A VTR.TO    StockHouse: N/A T.VTR    StockCharts: N/A VTR.TO    Zacks: N/A
> T.VTR
> The similarities may have something to do with who is providing data to
> each.
>
> On Mon, Dec 21, 2009 at 2:20 PM, bobsledproductions <JPeters3515@...>wrote:
>
> > Randy,
> >
> > Went thru with one Canadian ticker, and have posted to a ticker translation
> > rules spreadsheet.  As you will see, there are 3-4 rules that need to be
> > applied across the different sites.
> >
> > I put the workbook in the "upoads by forum members" folder, and it is is
> > called Ticker Translation Rules.xls
> >
> > Questions, let me know.
> >
>

Randy,

Went thru with one Canadian ticker, and have posted to a ticker translation
rules spreadsheet.  As you will see, there are 3-4 rules that need to be applied
across the different sites.

I put the workbook in the "upoads by forum members" folder, and it is is called
Ticker Translation Rules.xls

Questions, let me know.


--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I have all of the Yahoo ".TO" mappings.  I don't even see Opal Energy on MSN
> (well, OPAFF, but I don't see a pattern to map there) or AdvFN.  The "CA:"
> and "T." are what the Yahoo ".TO" symbols map to, so they're covered.
>
> And there are rules going from Yahoo to MSN for Preferred stocks and
> options.
>
> What I need is examples of Yahoo ticker symbols and what they are on other
> of the RCHGetElementNumber() data sources -- AdvFN, Morningstar, Reuters,
> Earnings.com, Google, BarCharts.com, StockHouse, StockCharts, and Zacks.
> Then I can add a mapping rule for them
>
> On Sun, Dec 20, 2009 at 10:23 PM, john_smithman <john_smithman@...>wrote:
>
> > There are four standards(?) used for Canadian Stocks: CMG.TO (for Toronto
> > Exchange stocks on most Sites); OPA.V (Venture Exchange Stocks); CA:CLM
> > (probably the second most prevalent for Canadian Stocks); and sometimes
> > T.PBG (e.g., on Investools.com).
> >
> > John
> >
>