Kamis, 13 Desember 2012

[smf_addin] Digest Number 2432

9 New Messages

Digest #2432

Messages

Wed Dec 12, 2012 6:51 am (PST) . Posted by:

"bob_cutillo" bob_cutillo

I have updated the smf-elements-6.txt file. I can get an individual cell to work hardcoding the url bug don't know how to do it using smfUpdateDownloadTable.

1 cell I can use =RCHGetTableCell("http://www.zacks.com/stock/quote/MMM/detailed-estimate",1,"Next Earnings") which returns 01-24-2013.

How can I code this column for smfUpdateDownloadTable where ticker is in column B?
I tried RCHGetTableCell("http://www.zacks.com/stock/quote/t=~~~~~"&"/detailed-estimate",1,"Next Earnings")

How can I get this to work? Thank you.

Wed Dec 12, 2012 8:09 am (PST) . Posted by:

"Randy Harmelink" rharmelink

Try:

RCHGetTableCell("http://www.zacks.com/stock/quote/~~~~~/detailed-estimate",1,"Next
Earnings")

You just need to replace the ticker symbol in the original formula, without
cell references, with five tilda characters. You can't use cell references
in formulas for the smfUpdateDownloadTable process.

On Wed, Dec 12, 2012 at 7:51 AM, bob_cutillo <jbkbob@gmail.com> wrote:

> I have updated the smf-elements-6.txt file. I can get an individual cell
> to work hardcoding the url bug don't know how to do it using
> smfUpdateDownloadTable.
>
> 1 cell I can use =RCHGetTableCell("
> http://www.zacks.com/stock/quote/MMM/detailed-estimate",1,"Next
> Earnings") which returns 01-24-2013.
>
> How can I code this column for smfUpdateDownloadTable where ticker is in
> column B?
> I tried RCHGetTableCell("http://www.zacks.com/stock/quote/t=~~~~~"&"/detailed-estimate",1,"Next
> Earnings")
>
> How can I get this to work? Thank you.
>

Wed Dec 12, 2012 7:11 am (PST) . Posted by:

"lexstar" lexstar

Hello. Anyway have a way to help me out? My Excel 2010 workbook crashes everytime the Protected View shows up with "Enable Editing" and I clicked on it.

I did some search online and someone came up with this code, but where do you put in order to make it work?

............One additional note that I just stumbled on was that the code will error if the workbook isn't in "ProtectedView". So to avoid this the following code will only enable editing if the workbook has "ProtectedView" enabled.

Otherwise the enable editing is skipped and everyone is happy.

If Application.ProtectedViewWindows.Count > 0 Then
Application.ActiveProtectedViewWindow.Edit
End If

Any help is much appreciated.

Wed Dec 12, 2012 7:49 am (PST) . Posted by:

"lexstar" lexstar

And, if I'm not having this problem, I'm getting this other problem:

"Microsoft Visual Basic for Applications.... Can't find project or library".

Man, I'm totally screwed today... Any help would be greatly appreciated. I've looked online, and tried some of the stuffs they said to try...but none has worked so far.

Frustrating.

--- In smf_addin@yahoogroups.com, "lexstar" <lexstar@...> wrote:
>
> Hello. Anyway have a way to help me out? My Excel 2010 workbook crashes everytime the Protected View shows up with "Enable Editing" and I clicked on it.
>
> I did some search online and someone came up with this code, but where do you put in order to make it work?
>
> ............One additional note that I just stumbled on was that the code will error if the workbook isn't in "ProtectedView". So to avoid this the following code will only enable editing if the workbook has "ProtectedView" enabled.
>
> Otherwise the enable editing is skipped and everyone is happy.
>
> If Application.ProtectedViewWindows.Count > 0 Then
> Application.ActiveProtectedViewWindow.Edit
> End If
>
>
> Any help is much appreciated.
>

Wed Dec 12, 2012 8:11 am (PST) . Posted by:

"Randy Harmelink" rharmelink

I can't help. I know nothing about that feature.

On Wed, Dec 12, 2012 at 8:11 AM, lexstar <lexstar@yahoo.com> wrote:

> Hello. Anyway have a way to help me out? My Excel 2010 workbook crashes
> everytime the Protected View shows up with "Enable Editing" and I clicked
> on it.
>
> I did some search online and someone came up with this code, but where do
> you put in order to make it work?
>
> ............One additional note that I just stumbled on was that the code
> will error if the workbook isn't in "ProtectedView". So to avoid this the
> following code will only enable editing if the workbook has "ProtectedView"
> enabled.
>
> Otherwise the enable editing is skipped and everyone is happy.
>
> If Application.ProtectedViewWindows.Count > 0 Then
> Application.ActiveProtectedViewWindow.Edit
> End If
>
> Any help is much appreciated.
>

Wed Dec 12, 2012 8:07 pm (PST) . Posted by:

"andrewhills97" andrewhills97

Hi Randy, I have read Links 2.2 and played around with 'smfGetOptionQuotes' but am struggling.
I am trying to populate a spreadsheet with Call Option Prices with the following row and column inputs:
- Down column A, there are 10 ITM and 10 OTM Exercise Prices,
- Across Row 7, next 10 expiration date
- Then within these ranges, the Call Option prices.

Re: down column A (ie Exercise prices), i enter "=smfGetOptionStrikes($B$2,$B$5,"Call",$B$3,,H33,1)". B2 is ticker; B5 is expiry date; B3 is data source; H33 is a number which decreases by 2; I dont know what the 1 does. This does not retrieve OTM Exercise Price - any help would be great here.

Re: Across Row 7, i enter "=smfGetOptionExpirations($B$2,$B$3)". B2 and B3 as above, but this only retrieves the first expiration date....

Re: filling this table, i have attempted this yet but i was going to try "=smfGetOptionQuotes($C24,D$22,,$B$3)". D22 is "l", ie last price; B3 as above; C24 is the Ticker description. How do I create Ticker from results down columnA and Across row 7?

Thanks, any help would be great.

Wed Dec 12, 2012 8:48 pm (PST) . Posted by:

"Randy H" rharmelink

On Wed, Dec 12, 2012 at 9:07 PM, andrewhills97 <andrewhills97@yahoo.com
<mailto:andrewhills97@yahoo.com> > wrote:
Hi Randy, I have read Links 2.2 and played around with
'smfGetOptionQuotes' but am struggling.
I am trying to populate a spreadsheet with Call Option Prices with the
following row and column inputs:
- Down column A, there are 10 ITM and 10 OTM Exercise Prices,
- Across Row 7, next 10 expiration date
- Then within these ranges, the Call Option prices.

Re: down column A (ie Exercise prices), i enter
"=smfGetOptionStrikes($B$2,$B$5,"Call",$B$3,,H33,1)". B2 is ticker; B5
is expiry date; B3 is data source; H33 is a number which decreases by
2; I dont know what the 1 does. This does not retrieve OTM Exercise
Price - any help would be great here.

I don't know what your H33 is being used for. That parameter should be
either 0 or 1, depending on whether you want the function to create
ticker symbols that smfGetOptionQuotes() can use. It looks like you want
to drop the H33 reference and just use a 1 there, with no later
parameters. For example:

=smfGetOptionStrikes(Ticker,Expiry_Date,"Call",Data_Source,1)

However, if you need to vary the expiration dates, you don't want the
"1" parameter. Drop it to just get the strike prices.

Re: Across Row 7, i enter "=smfGetOptionExpirations($B$2,$B$3)". B2 and
B3 as above, but this only retrieves the first expiration date....

Unfortunately, the function produces ROWS of output. If you want to
spread it across columns, you need to retrieve it in a column and then
transpose it:

Re: filling this table, i have attempted this yet but i was going to try
"=smfGetOptionQuotes($C24,D$22,,$B$3)". D22 is "l", ie last price; B3
as above; C24 is the Ticker description. How do I create Ticker from
results down columnA and Across row 7?

Since you want to vary the expiration date and strike prices for each
cross-section in the table, you'll need to create the "options ticker
symbol" for each one. It would be something like:

=smfGetOptionQuotes(Ticker&TEXT(D$22," mm/dd yyyy ")&$C24&"
Call","l",0,Data_Source)

Where column C contains ONLY strike prices, instead of actual "option
ticker symbols", because you want to vary the strike price AND the
expiration date. What you did earlier with the smfGetOptionStrikes()
function was generating "option ticker symbols" for a specific
expiration date. That's not what you want if you're filling in a table
that varies that expiration date. You just want to give that function
the earliest expiration date, because it's most likely to have the full
range of available strike prices.

Thu Dec 13, 2012 12:46 am (PST) . Posted by:

"andrewhills97" andrewhills97


thanks very much

--- In smf_addin@yahoogroups.com, "Randy H" <rharmelink@...> wrote:
>
> On Wed, Dec 12, 2012 at 9:07 PM, andrewhills97 <andrewhills97@...
> <mailto:andrewhills97@...> > wrote:
> Hi Randy, I have read Links 2.2 and played around with
> 'smfGetOptionQuotes' but am struggling.
> I am trying to populate a spreadsheet with Call Option Prices with the
> following row and column inputs:
> - Down column A, there are 10 ITM and 10 OTM Exercise Prices,
> - Across Row 7, next 10 expiration date
> - Then within these ranges, the Call Option prices.
>
> Re: down column A (ie Exercise prices), i enter
> "=smfGetOptionStrikes($B$2,$B$5,"Call",$B$3,,H33,1)". B2 is ticker; B5
> is expiry date; B3 is data source; H33 is a number which decreases by
> 2; I dont know what the 1 does. This does not retrieve OTM Exercise
> Price - any help would be great here.
>
> I don't know what your H33 is being used for. That parameter should be
> either 0 or 1, depending on whether you want the function to create
> ticker symbols that smfGetOptionQuotes() can use. It looks like you want
> to drop the H33 reference and just use a 1 there, with no later
> parameters. For example:
>
> =smfGetOptionStrikes(Ticker,Expiry_Date,"Call",Data_Source,1)
>
> However, if you need to vary the expiration dates, you don't want the
> "1" parameter. Drop it to just get the strike prices.
>
> Re: Across Row 7, i enter "=smfGetOptionExpirations($B$2,$B$3)". B2 and
> B3 as above, but this only retrieves the first expiration date....
>
> Unfortunately, the function produces ROWS of output. If you want to
> spread it across columns, you need to retrieve it in a column and then
> transpose it:
>
> Re: filling this table, i have attempted this yet but i was going to try
> "=smfGetOptionQuotes($C24,D$22,,$B$3)". D22 is "l", ie last price; B3
> as above; C24 is the Ticker description. How do I create Ticker from
> results down columnA and Across row 7?
>
> Since you want to vary the expiration date and strike prices for each
> cross-section in the table, you'll need to create the "options ticker
> symbol" for each one. It would be something like:
>
> =smfGetOptionQuotes(Ticker&TEXT(D$22," mm/dd yyyy ")&$C24&"
> Call","l",0,Data_Source)
>
> Where column C contains ONLY strike prices, instead of actual "option
> ticker symbols", because you want to vary the strike price AND the
> expiration date. What you did earlier with the smfGetOptionStrikes()
> function was generating "option ticker symbols" for a specific
> expiration date. That's not what you want if you're filling in a table
> that varies that expiration date. You just want to give that function
> the earliest expiration date, because it's most likely to have the full
> range of available strike prices.
>

Thu Dec 13, 2012 2:16 am (PST) . Posted by:

"malramsay" malramsay

Hi all,

Does anyone know somewhere to get the 120 day moving average of a US stock? I'd rather not have to retrieve the stock history and calculate it myself as I'm dealing with a large number of stocks.

Thanks for your help,
Malcolm

Tidak ada komentar:

Posting Komentar