I tried to download some data for sp500 using your formulas. But for the row from 9 to 259, the formulas returns error for all cells. but for the remaining, it works well. Can you help me with this problem? Thanks a lot!

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

1b

Re: need help on errors by using rchgetelementnumbers

Mon Jun 3, 2013 8:05 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

The add-in isn't designed to create databases like that. It has a built in
limit of 1000 web pages per "session". I consider it an abuse to grab all
of that data from the free services just because you can. It's also a very
slow way to grab data.

You should use some type of screening product to get your list down to a
reasonable amount and then grab data.

However, you can use RCHGetYahooQuotes() to grab a number of data items on
up to 200 stocks at a time, in one Internet access.

Also, FinViz allows you to download a large CSV file with a number of data
items on all stocks they cover, in one Internet access -- see the LINKS
area of the group for more information.

If you really need a lot of data on stocks, you should look into AAII's
Stock Investor Pro product.

I use portfolio123.com, but it's not cheap. But it does allow back-testing
on "point-in-time" data, which is rare for backtesters, but necessary to
remove survivorship bias.

On Mon, Jun 3, 2013 at 6:40 AM, Xin L <jgr1230@gmail.com> wrote:

> I tried to download some data for sp500 using your formulas. But for the
> row from 9 to 259, the formulas returns error for all cells. but for the
> remaining, it works well. Can you help me with this problem? Thanks a lot!
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

1c

Re: need help on errors by using rchgetelementnumbers

Mon Jun 3, 2013 11:10 am (PDT) . Posted by:

"Xin L" liucyndy

Randy,
Thanks a lot for the prompt reply.
I tried the finviz csv formula, it returns #value! can you let me know where I may be wrong? here is the formula I used
=smfGetCSVFile("http://finviz.com/export.ashx?v=151&T="&sfJoin(A3:A12,",")&"&c="&sfJoin(B1:G1,","))
Thanks a lot!

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> The add-in isn't designed to create databases like that. It has a built in
> limit of 1000 web pages per "session". I consider it an abuse to grab all
> of that data from the free services just because you can. It's also a very
> slow way to grab data.
>
> You should use some type of screening product to get your list down to a
> reasonable amount and then grab data.
>
> However, you can use RCHGetYahooQuotes() to grab a number of data items on
> up to 200 stocks at a time, in one Internet access.
>
> Also, FinViz allows you to download a large CSV file with a number of data
> items on all stocks they cover, in one Internet access -- see the LINKS
> area of the group for more information.
>
> If you really need a lot of data on stocks, you should look into AAII's
> Stock Investor Pro product.
>
> I use portfolio123.com, but it's not cheap. But it does allow back-testing
> on "point-in-time" data, which is rare for backtesters, but necessary to
> remove survivorship bias.
>
> On Mon, Jun 3, 2013 at 6:40 AM, Xin L <jgr1230@...> wrote:
>
> > I tried to download some data for sp500 using your formulas. But for the
> > row from 9 to 259, the formulas returns error for all cells. but for the
> > remaining, it works well. Can you help me with this problem? Thanks a lot!
> >
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

1d

Re: need help on errors by using rchgetelementnumbers

Mon Jun 3, 2013 11:35 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

I just copied and pasted your formula below and it worked fine for me.

I had MMM and IBM in cells A3 and A5, and 1, 2, and 3, in cells B1, C1, and
D1. I'll assume your #VALUE! error has to do with something in the input
cells?

1 2 3 Ticker Company Sector MMM IBM International Business Machines
Corp. Technology IBM MMM 3M Co. Conglomerates

On Mon, Jun 3, 2013 at 11:10 AM, Xin L <jgr1230@gmail.com> wrote:

> I tried the finviz csv formula, it returns #value! can you let me know
> where I may be wrong? here is the formula I used
> =smfGetCSVFile("http://finviz.com/export.ashx?v=151&T=
> "&sfJoin(A3:A12,",")&"&c="&sfJoin(B1:G1,","))
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

2a

Re: Mini Options

Mon Jun 3, 2013 8:44 am (PDT) . Posted by:

"joshi_mandar" joshi_mandar

Randy - Just wondering if mini-options are supported by the tool now.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I haven't made any changes for the min-options...
>
> I haven't even looked at them.
>
> On Tue, Mar 19, 2013 at 2:47 PM, joshi_mandar <joshi_mandar@...>wrote:
>
> > Randy - Do you know if this tool works for mini options? You may or may
> > not know mini-options (10 shares instead of 100 shares) are now available
> > for AAPL and few other company stocks.
> >
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (7) . Top ^

2b

Re: Mini Options

Mon Jun 3, 2013 8:56 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

No. I don't do anything with them, so I'm not familiar with them at all.

If you have the URLs, you can probably get them with RCHGetTableCell()
functions.

On Mon, Jun 3, 2013 at 8:44 AM, joshi_mandar <joshi_mandar@hotmail.com>wrote:

> Randy - Just wondering if mini-options are supported by the tool now.
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (7) . Top ^

2c

Re: Mini Options

Mon Jun 3, 2013 10:55 am (PDT) . Posted by:

"joshi_mandar" joshi_mandar

I usually get option quotes using this formula:
smfGetOptionQuotes("aapl Jan 15 330 call","l",0,"Y")

On finance.yahoo.com, AAPL mini-options are listed with an extra number "7" in the symbol. for example go to: http://finance.yahoo.com/q/op?s=AAPL&m=2015-01. There should be two rows for $330 strike price. one has an extra number "7" in the symbol. That is the mini-option.

Can you tell me how do I alter above formula to get the quote for mini-option instead of regular option?

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> No. I don't do anything with them, so I'm not familiar with them at all.
>
> If you have the URLs, you can probably get them with RCHGetTableCell()
> functions.
>
> On Mon, Jun 3, 2013 at 8:44 AM, joshi_mandar <joshi_mandar@...>wrote:
>
> > Randy - Just wondering if mini-options are supported by the tool now.
> >
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (7) . Top ^

2d

Re: Mini Options

Mon Jun 3, 2013 11:02 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

You'd need to use the RCHGetTableCell() function, such as:

=RCHGetTableCell("http://finance.yahoo.com/q/op?s=AAPL&m=2015-01
",1,"AAPL7150117C00450000")

Is the "7" consistent? If so, that may be fairly easy to get into
smfGetYahooOptionQuote().

On Mon, Jun 3, 2013 at 10:55 AM, joshi_mandar <joshi_mandar@hotmail.com>wrote:

> I usually get option quotes using this formula:
> smfGetOptionQuotes("aapl Jan 15 330 call","l",0,"Y")
>
> On finance.yahoo.com, AAPL mini-options are listed with an extra number
> "7" in the symbol. for example go to:
> http://finance.yahoo.com/q/op?s=AAPL&m=2015-01. There should be two rows
> for $330 strike price. one has an extra number "7" in the symbol. That is
> the mini-option.
>
> Can you tell me how do I alter above formula to get the quote for
> mini-option instead of regular option?
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (7) . Top ^

2e

Re: Mini Options

Mon Jun 3, 2013 11:46 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

I just uploaded a new version of the add-in to the "Works in Progress"
folder. It should allow smfGetOptionQuotes() and smfGetYahooOptionQuote()
to retrieve the mini-options from Yahoo by just appending the "7" to the
ticker symbol. For example:

=smfGetYahooOptionQuote("AAPL7","C","01/17/2015",450,"b")
=smfGetOptionQuotes("AAPL7 01/17 2015 $450.00 Call","b")

On Mon, Jun 3, 2013 at 10:55 AM, joshi_mandar <joshi_mandar@hotmail.com>wrote:

> I usually get option quotes using this formula:
> smfGetOptionQuotes("aapl Jan 15 330 call","l",0,"Y")
>
> On finance.yahoo.com, AAPL mini-options are listed with an extra number
> "7" in the symbol. for example go to:
> http://finance.yahoo.com/q/op?s=AAPL&m=2015-01. There should be two rows
> for $330 strike price. one has an extra number "7" in the symbol. That is
> the mini-option.
>
> Can you tell me how do I alter above formula to get the quote for
> mini-option instead of regular option?
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (7) . Top ^

3 Enigma no more

Mon Jun 3, 2013 9:38 am (PDT) . Posted by:

"ssztaba" ssztaba

The F9 calculate command does NOT update the options quotes, as I had indicated in a previous message. Randy had indicated that it would not, and it does not. The smfForceRecalculation command is needed for options pricing to be updated.

My previous confusion about one workbook opened at 9:30 Eastern showing only "N/A" and not updating during the day, while another identical workbook opened after 9:50 showing correct prices and actually updating during the day has also been clarified:

Neither workbook updates since neither one had the smfForceRecalculation command. The 9:30 opened workbook showed "N/A" because that was the data Yahoo was showing at that time. The after 9:50 workbook showed prices because those were the prices Yahoo was showing at that time. But during the day, neither workbook would update nor would show any data other than what was obtained at the initial opening of the workbooks.

So, how was I fooled into thinking that the option prices were in fact updating using only the F9 calculate command ?? Well, my worksheet of option prices did, and continues to, show prices changing using only the F9 command, but the prices that are changing are the Time Value of the options. The cells containing the Time Value are calculated cells using the strike price and the stock price -- these cells were changing because the stock prices were being updated via the F9 command, and this was causing the Time Value to change. The Time Value cells were changing but the Option prices were not changing since the smfForceRecalculation command was not being used.

So, now I use the quick F9 command when all I want is the stock price info, and I use the smfForceRecalculation when I want to updated Option prices.

Thanks again for all your help

Stan

Reply to sender . Reply to group . Reply via Web Post . All Messages (1) . Top ^

4a

One file corrupts the others

Mon Jun 3, 2013 2:46 pm (PDT) . Posted by:

"steven_rourk" steven_rourk

Hi, I´ve been using addin for some years now with no problem. I've got 4 or 5 spreadsheets with different addin formulas, again working fine for several months. However since last week there is one spreadsheet that when opened, doesn´t work (it doesn't update) and what is worst, it "breaks" the other files which work fine. For example I have files A, B and C. If I just open A and B, they work fine... but once I open file C (the damaged one) the other two files stop working.

I do remember that last week, Excel crashed.... It stopped responding and restarted itself.... but I cannot tell if it crashed because of the addin file (which was opened) or because of another non-addin file I was working with. Since then I´m having the problem.

I tried downloading the addin files again, restarting the computer, restarting the "complements", etc. but no luck.

Any ideas?

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

4b

Re: One file corrupts the others

Mon Jun 3, 2013 6:35 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Have you run any kind of hard disk utility, to check your hard disk? Is the
C file a large one?

Other than that, I'm at a loss to explain it.

On Mon, Jun 3, 2013 at 2:46 PM, steven_rourk <steven_rourk@hotmail.com>wrote:

> Hi, I´ve been using addin for some years now with no problem. I've got 4
> or 5 spreadsheets with different addin formulas, again working fine for
> several months. However since last week there is one spreadsheet that when
> opened, doesn´t work (it doesn't update) and what is worst, it "breaks" the
> other files which work fine. For example I have files A, B and C. If I just
> open A and B, they work fine... but once I open file C (the damaged one)
> the other two files stop working.
>
> I do remember that last week, Excel crashed.... It stopped responding and
> restarted itself.... but I cannot tell if it crashed because of the addin
> file (which was opened) or because of another non-addin file I was working
> with. Since then I´m having the problem.
>
> I tried downloading the addin files again, restarting the computer,
> restarting the "complements", etc. but no luck.
>
> Any ideas?
>