11 New Messages
Digest #2956
Messages
Tue Feb 4, 2014 9:56 am (PST) . Posted by:
"Ka Yau Wong" wongkayau
Hi Randy,
Morning.
I would like to download the Hong Kong retail sales history. However, I can't find the "bookmarkable" url for the following website:
http://www.investing.com/economic-calendar/hong-kong-retail-sales-775
Any luck from your side?
Thank you so much!!!
Cheers, L
On Monday, February 3, 2014 12:06 AM, "wongkayau@yahoo.com" <wongkayau@yahoo.com> wrote:
Hi Randy,
I know what happened. My "RCH_Stock_Market_Functions" add-in was old, and I just referred to the combined pdf for reference. Therefore, I don't aware that you have empowered the smf funciton.
Sorry for my absent minded.
I love this smf tool. Thank you so much! Have a good day!
Cheers, L
Morning.
I would like to download the Hong Kong retail sales history. However, I can't find the "bookmarkable&
http://www.investin
Any luck from your side?
Thank you so much!!!
Cheers, L
On Monday, February 3, 2014 12:06 AM, "wongkayau@yahoo.
Hi Randy,
I know what happened. My "RCH_Stock_
Sorry for my absent minded.
I love this smf tool. Thank you so much! Have a good day!
Cheers, L
Tue Feb 4, 2014 1:16 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I didn't find anything usable. Sorry.
On Tue, Feb 4, 2014 at 10:56 AM, Ka Yau Wong <wongkayau@yahoo.com> wrote:
>
> I would like to download the Hong Kong retail sales history. However, I
> can't find the "bookmarkable" url for the following website:
> http://www.investing.com/economic-calendar/hong-kong-retail-sales-775
> Any luck from your side?
>
>
On Tue, Feb 4, 2014 at 10:56 AM, Ka Yau Wong <wongkayau@yahoo.
>
> I would like to download the Hong Kong retail sales history. However, I
> can't find the "bookmarkable&
> http://www.investin
> Any luck from your side?
>
>
Tue Feb 4, 2014 9:10 pm (PST) . Posted by:
"Ka Yau Wong" wongkayau
no problem. thank you so much!
cheers, L
On Wednesday, February 5, 2014 5:17 AM, Randy Harmelink <rharmelink@gmail.com> wrote:
I didn't find anything usable. Sorry.
On Tue, Feb 4, 2014 at 10:56 AM, Ka Yau Wong <wongkayau@yahoo.com> wrote:
>
>I would like to download the Hong Kong retail sales history. However, I can't find the "bookmarkable" url for the following website:
>http://www.investing.com/economic-calendar/hong-kong-retail-sales-775
>Any luck from your side?
>
cheers, L
On Wednesday, February 5, 2014 5:17 AM, Randy Harmelink <rharmelink@gmail.
I didn't find anything usable. Sorry.
On Tue, Feb 4, 2014 at 10:56 AM, Ka Yau Wong <wongkayau@yahoo.
>
>I would like to download the Hong Kong retail sales history. However, I can't find the "bookmarkable&
>http://www.investin
>Any luck from your side?
>
Tue Feb 4, 2014 10:37 am (PST) . Posted by:
sihlai
I have a spread that retrieves hundreds of quotes from Yahoo after market close. Ever since start using the new version of addin, Excel has gone not responding each time I tried to run the spread sheet. I wondered if there was any particular stock symbol that was the problem, so I went into the spread sheet to update each quote one by one. To my surprise they all retrieved okay individually, so I am not clear on the reason the spread sheet crashes when the quotes are obtained collectively. Any idea or suggestion?
Tue Feb 4, 2014 1:48 pm (PST) . Posted by:
"Kermit W. Prather" kermitpra
I have noticed the same results using dividata.com, dividend.com and yahoo. I'm retrieving only dividend yield data from all 3 sites for 149 stocks
I am still trying to determine the problem. I am going to reduce the inquiries to less than 20 symbols and increase by 10 to see where I have error results start showing up.
I did have most all my work in one workbook with multiple worksheets. I am starting to create multiple workbooks with each doing one or two functions. I think Randy has mentioned before about putting too much in workbook. In some cases I have written macros to control calculating a worksheet until I need the information. It is not that difficult to do. I put in Cell A1 a control field and assign values for example, if I am pulling Yield and dividends I'll assign each a code like Y= yield, D= dividend I then code an IF statement in each cells that pull yield and dividend information. Here is a quick if statement to give you the idea. I decided I would do it this way because it was taking a long time at initial load for the workbook to pull all the data. Doing it this way I pull the data only when I am ready to use it. I use NO in cell A1 to do nothing. I try to remember to always set A1 to NO before closing the workbook. Problem is sometimes I forget and it pulls whatever data I last pull before closing the workbook but sat least it does not pull all information.
=IF(A1="D",RCHGetTableCell("http://dividata.com/stock/"&B3,1,"Dividend Yield:"), IF(A1="D",add dividend info statement," "))
If I come to any solid conclusion I'll post it here. Right now, it is too early for me to ask for help. Randy has enough to do without my asking incomplete question. Especially, since I am not sure what the questions are yet.
Kermit
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of sihlai@yahoo.ca
Sent: Tuesday, February 04, 2014 1:37 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] strange crash problem
I have a spread that retrieves hundreds of quotes from Yahoo after market close. Ever since start using the new version of addin, Excel has gone not responding each time I tried to run the spread sheet. I wondered if there was any particular stock symbol that was the problem, so I went into the spread sheet to update each quote one by one. To my surprise they all retrieved okay individually, so I am not clear on the reason the spread sheet crashes when the quotes are obtained collectively. Any idea or suggestion?
I am still trying to determine the problem. I am going to reduce the inquiries to less than 20 symbols and increase by 10 to see where I have error results start showing up.
I did have most all my work in one workbook with multiple worksheets. I am starting to create multiple workbooks with each doing one or two functions. I think Randy has mentioned before about putting too much in workbook. In some cases I have written macros to control calculating a worksheet until I need the information. It is not that difficult to do. I put in Cell A1 a control field and assign values for example, if I am pulling Yield and dividends I'll assign each a code like Y= yield, D= dividend I then code an IF statement in each cells that pull yield and dividend information. Here is a quick if statement to give you the idea. I decided I would do it this way because it was taking a long time at initial load for the workbook to pull all the data. Doing it this way I pull the data only when I am ready to use it. I use NO in cell A1 to do nothing. I try to remember to always set A1 to NO before closing the workbook. Problem is sometimes I forget and it pulls whatever data I last pull before closing the workbook but sat least it does not pull all information.
=IF(A1="
If I come to any solid conclusion I'll post it here. Right now, it is too early for me to ask for help. Randy has enough to do without my asking incomplete question. Especially, since I am not sure what the questions are yet.
Kermit
From: smf_addin@yahoogrou
Sent: Tuesday, February 04, 2014 1:37 PM
To: smf_addin@yahoogrou
Subject: [smf_addin] strange crash problem
I have a spread that retrieves hundreds of quotes from Yahoo after market close. Ever since start using the new version of addin, Excel has gone not responding each time I tried to run the spread sheet. I wondered if there was any particular stock symbol that was the problem, so I went into the spread sheet to update each quote one by one. To my surprise they all retrieved okay individually, so I am not clear on the reason the spread sheet crashes when the quotes are obtained collectively. Any idea or suggestion?
Tue Feb 4, 2014 2:34 pm (PST) . Posted by:
"Kermit W. Prather" kermitpra
Randy, I have a question about the "error" result for the formula
=IF(A1="D",RCHGetTableCell("http://dividata.com/stock/"&B3,1,"Dividend Yield:"), IF(A1="D",add dividend info statement," "))
As expected I get an error for the above formula if there is no symbol in cell B3.
My question is do you detect the error or do you execute the query and get the error after parsing the response. Other words do you access the internet?
Kermit
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Kermit W. Prather
Sent: Tuesday, February 04, 2014 4:49 PM
To: smf_addin@yahoogroups.com
Subject: RE: [smf_addin] strange crash problem
I have noticed the same results using dividata.com, dividend.com and yahoo. I'm retrieving only dividend yield data from all 3 sites for 149 stocks
I am still trying to determine the problem. I am going to reduce the inquiries to less than 20 symbols and increase by 10 to see where I have error results start showing up.
I did have most all my work in one workbook with multiple worksheets. I am starting to create multiple workbooks with each doing one or two functions. I think Randy has mentioned before about putting too much in workbook. In some cases I have written macros to control calculating a worksheet until I need the information. It is not that difficult to do. I put in Cell A1 a control field and assign values for example, if I am pulling Yield and dividends I'll assign each a code like Y= yield, D= dividend I then code an IF statement in each cells that pull yield and dividend information. Here is a quick if statement to give you the idea. I decided I would do it this way because it was taking a long time at initial load for the workbook to pull all the data. Doing it this way I pull the data only when I am ready to use it. I use NO in cell A1 to do nothing. I try to remember to always set A1 to NO before closing the workbook. Problem is sometimes I forget and it pulls whatever data I last pull before closing the workbook but sat least it does not pull all information.
=IF(A1="D",RCHGetTableCell("http://dividata.com/stock/"&B3,1,"Dividend Yield:"), IF(A1="D",add dividend info statement," "))
If I come to any solid conclusion I'll post it here. Right now, it is too early for me to ask for help. Randy has enough to do without my asking incomplete question. Especially, since I am not sure what the questions are yet.
Kermit
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of sihlai@yahoo.ca
Sent: Tuesday, February 04, 2014 1:37 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] strange crash problem
I have a spread that retrieves hundreds of quotes from Yahoo after market close. Ever since start using the new version of addin, Excel has gone not responding each time I tried to run the spread sheet. I wondered if there was any particular stock symbol that was the problem, so I went into the spread sheet to update each quote one by one. To my surprise they all retrieved okay individually, so I am not clear on the reason the spread sheet crashes when the quotes are obtained collectively. Any idea or suggestion?
=IF(A1="
As expected I get an error for the above formula if there is no symbol in cell B3.
My question is do you detect the error or do you execute the query and get the error after parsing the response. Other words do you access the internet?
Kermit
From: smf_addin@yahoogrou
Sent: Tuesday, February 04, 2014 4:49 PM
To: smf_addin@yahoogrou
Subject: RE: [smf_addin] strange crash problem
I have noticed the same results using dividata.com, dividend.com and yahoo. I'm retrieving only dividend yield data from all 3 sites for 149 stocks
I am still trying to determine the problem. I am going to reduce the inquiries to less than 20 symbols and increase by 10 to see where I have error results start showing up.
I did have most all my work in one workbook with multiple worksheets. I am starting to create multiple workbooks with each doing one or two functions. I think Randy has mentioned before about putting too much in workbook. In some cases I have written macros to control calculating a worksheet until I need the information. It is not that difficult to do. I put in Cell A1 a control field and assign values for example, if I am pulling Yield and dividends I'll assign each a code like Y= yield, D= dividend I then code an IF statement in each cells that pull yield and dividend information. Here is a quick if statement to give you the idea. I decided I would do it this way because it was taking a long time at initial load for the workbook to pull all the data. Doing it this way I pull the data only when I am ready to use it. I use NO in cell A1 to do nothing. I try to remember to always set A1 to NO before closing the workbook. Problem is sometimes I forget and it pulls whatever data I last pull before closing the workbook but sat least it does not pull all information.
=IF(A1="
If I come to any solid conclusion I'll post it here. Right now, it is too early for me to ask for help. Randy has enough to do without my asking incomplete question. Especially, since I am not sure what the questions are yet.
Kermit
From: smf_addin@yahoogrou
Sent: Tuesday, February 04, 2014 1:37 PM
To: smf_addin@yahoogrou
Subject: [smf_addin] strange crash problem
I have a spread that retrieves hundreds of quotes from Yahoo after market close. Ever since start using the new version of addin, Excel has gone not responding each time I tried to run the spread sheet. I wondered if there was any particular stock symbol that was the problem, so I went into the spread sheet to update each quote one by one. To my surprise they all retrieved okay individually, so I am not clear on the reason the spread sheet crashes when the quotes are obtained collectively. Any idea or suggestion?
Tue Feb 4, 2014 3:42 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
If you're getting hundreds of quotes with individual formulas, I'd
recommend array-entering RCHGetYahooQuotes() over a range. You can get
multiple data items on up to 200 stocks, in a single Internet access. I
can speed up retrieval significantly.
On Tue, Feb 4, 2014 at 11:37 AM, <sihlai@yahoo.ca> wrote:
>
> I have a spread that retrieves hundreds of quotes from Yahoo after market
> close. Ever since start using the new version of addin, Excel has gone not
> responding each time I tried to run the spread sheet. I wondered if there
> was any particular stock symbol that was the problem, so I went into the
> spread sheet to update each quote one by one. To my surprise they all
> retrieved okay individually, so I am not clear on the reason the spread
> sheet crashes when the quotes are obtained collectively. Any idea or
> suggestion?
>
recommend array-entering RCHGetYahooQuotes(
multiple data items on up to 200 stocks, in a single Internet access. I
can speed up retrieval significantly.
On Tue, Feb 4, 2014 at 11:37 AM, <sihlai@yahoo.
>
> I have a spread that retrieves hundreds of quotes from Yahoo after market
> close. Ever since start using the new version of addin, Excel has gone not
> responding each time I tried to run the spread sheet. I wondered if there
> was any particular stock symbol that was the problem, so I went into the
> spread sheet to update each quote one by one. To my surprise they all
> retrieved okay individually, so I am not clear on the reason the spread
> sheet crashes when the quotes are obtained collectively. Any idea or
> suggestion?
>
Tue Feb 4, 2014 3:46 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
One advantage to using the smfUpdateDownloadTable macro to grab a table of
such data is that you can see where the add-in has issues. It also gives
you control over which rows you want to update. And it leaves values behind
for those things you only need to grab occasionally.
For example, when I had one data retrieval come to a grinding halt, I found
out it stopped each time for the retrieval of the Zacks Rank. After
checking it out with my browser, I found out their web site was down. So
each of those formula had to "time out" before the add-in could progress to
the next one.
On Tue, Feb 4, 2014 at 2:48 PM, Kermit W. Prather
<kermitp@tampabay.rr.com>wrote:
>
> I have noticed the same results using dividata.com, dividend.com and
> yahoo. I'm retrieving only dividend yield data from all 3 sites for 149
> stocks
>
>
>
> I am still trying to determine the problem. I am going to reduce the
> inquiries to less than 20 symbols and increase by 10 to see where I have
> error results start showing up.
>
>
>
> I did have most all my work in one workbook with multiple worksheets. I
> am starting to create multiple workbooks with each doing one or two
> functions. I think Randy has mentioned before about putting too much in
> workbook. In some cases I have written macros to control calculating a
> worksheet until I need the information. It is not that difficult to do.
> I put in Cell A1 a control field and assign values for example, if I am
> pulling Yield and dividends I'll assign each a code like Y= yield, D=
> dividend I then code an IF statement in each cells that pull yield and
> dividend information. Here is a quick if statement to give you the idea. I
> decided I would do it this way because it was taking a long time at initial
> load for the workbook to pull all the data. Doing it this way I pull the
> data only when I am ready to use it. I use NO in cell A1 to do nothing. I
> try to remember to always set A1 to NO before closing the workbook. Problem
> is sometimes I forget and it pulls whatever data I last pull before closing
> the workbook but sat least it does not pull all information.
>
> =IF(A1="D",RCHGetTableCell("http://dividata.com/stock/"&B3,1,"Dividend
> Yield:"), IF(A1="D",add dividend info statement," "))
>
>
>
> If I come to any solid conclusion I'll post it here. Right now, it is too
> early for me to ask for help. Randy has enough to do without my asking
> incomplete question. Especially, since I am not sure what the questions are
> yet.
>
>
>
such data is that you can see where the add-in has issues. It also gives
you control over which rows you want to update. And it leaves values behind
for those things you only need to grab occasionally.
For example, when I had one data retrieval come to a grinding halt, I found
out it stopped each time for the retrieval of the Zacks Rank. After
checking it out with my browser, I found out their web site was down. So
each of those formula had to "time out" before the add-in could progress to
the next one.
On Tue, Feb 4, 2014 at 2:48 PM, Kermit W. Prather
<kermitp@tampabay.
>
> I have noticed the same results using dividata.com, dividend.com and
> yahoo. I'm retrieving only dividend yield data from all 3 sites for 149
> stocks
>
>
>
> I am still trying to determine the problem. I am going to reduce the
> inquiries to less than 20 symbols and increase by 10 to see where I have
> error results start showing up.
>
>
>
> I did have most all my work in one workbook with multiple worksheets. I
> am starting to create multiple workbooks with each doing one or two
> functions. I think Randy has mentioned before about putting too much in
> workbook. In some cases I have written macros to control calculating a
> worksheet until I need the information. It is not that difficult to do.
> I put in Cell A1 a control field and assign values for example, if I am
> pulling Yield and dividends I'll assign each a code like Y= yield, D=
> dividend I then code an IF statement in each cells that pull yield and
> dividend information. Here is a quick if statement to give you the idea. I
> decided I would do it this way because it was taking a long time at initial
> load for the workbook to pull all the data. Doing it this way I pull the
> data only when I am ready to use it. I use NO in cell A1 to do nothing. I
> try to remember to always set A1 to NO before closing the workbook. Problem
> is sometimes I forget and it pulls whatever data I last pull before closing
> the workbook but sat least it does not pull all information.
>
> =IF(A1="
> Yield:"
>
>
>
> If I come to any solid conclusion I'll post it here. Right now, it is too
> early for me to ask for help. Randy has enough to do without my asking
> incomplete question. Especially, since I am not sure what the questions are
> yet.
>
>
>
Tue Feb 4, 2014 3:48 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
It will access the Internet with whatever is in cell B3. That's why I will
sometimes add a condition to such a formula to make sure cell B3 has a
length greater than 0.
On Tue, Feb 4, 2014 at 3:34 PM, Kermit W. Prather
<kermitp@tampabay.rr.com>wrote:
>
> Randy, I have a question about the "error" result for the formula
>
>
>
> =IF(A1="D",RCHGetTableCell("http://dividata.com/stock/"&B3,1,"Dividend
> Yield:"), IF(A1="D",add dividend info statement," "))
>
>
>
> As expected I get an error for the above formula if there is no symbol in
> cell B3.
>
>
>
> My question is do you detect the error or do you execute the query and get
> the error after parsing the response. Other words do you access the
> internet?
>
>
>
sometimes add a condition to such a formula to make sure cell B3 has a
length greater than 0.
On Tue, Feb 4, 2014 at 3:34 PM, Kermit W. Prather
<kermitp@tampabay.
>
> Randy, I have a question about the "error" result for the formula
>
>
>
> =IF(A1="
> Yield:"
>
>
>
> As expected I get an error for the above formula if there is no symbol in
> cell B3.
>
>
>
> My question is do you detect the error or do you execute the query and get
> the error after parsing the response. Other words do you access the
> internet?
>
>
>
Tue Feb 4, 2014 3:44 pm (PST) . Posted by:
e_bartsch
Randy & Mike:
Thanks for the tips. And, Randy, you are right--I meant to type Element "887" for the Fair Value Estimate (my typo).
In any event, after (a) I launched the spreadsheet, (b) waited for the calculations/web pulls to finish, (c) went to Data/From Web and pulled up the Morningtar website in the dialog screen and logged in using my M* credentials, (d) closed the spreadsheet, (e) re-launched the spreadsheet, and (f) tried the function using the function =RCHGetElementNumber(MMM,887), it worked! So, thanks for the help. I will see if it sticks when I restart the computer tomorrow.
With respect to the OTM1/OTM2, here is what I meant.
In the old days, there was only one options expiration per month. So, OTM1 always returned a value related to that month's expiration date (the fourth saturday of the month) equal to the first option strike price immediately out of the money from the then selling price. Likewise, OTM2 always returned a value related to that month's expriation date and the second option strike price immediately out of the money from the then selling price.
Each could be detrmined with this function:
=smfGetOptionQuotes(D7:D125,"zsba",1)
Where D7:D125 contains the short leg of the spred (data like this):
EXPD 2 2014 OTM1 Put
and the funcion (=smfGetOptionQuotes(E7:E125, "zsba",1), where E:7:E125 contains the long leg of the spread (using data like this):
EXBD 2 2014 OTM2 Put
Each used to return the OTM1 value for the 2/22 expiration date and the OTM2 value for the 2/22 expriation date. My problem is, OTM1 and OTM2 now don't always uniformly report the same expiration date. I'd like to automate the expiration date so that I know I'm always getting the fourth Saturday of the month by using the OTM1 & OTM2 convention.
Is that possible?
Thanks for all the help.
Regards,
Eric
Thanks for the tips. And, Randy, you are right--I meant to type Element "887" for the Fair Value Estimate (my typo).
In any event, after (a) I launched the spreadsheet, (b) waited for the calculations/
With respect to the OTM1/OTM2, here is what I meant.
In the old days, there was only one options expiration per month. So, OTM1 always returned a value related to that month's expiration date (the fourth saturday of the month) equal to the first option strike price immediately out of the money from the then selling price. Likewise, OTM2 always returned a value related to that month's expriation date and the second option strike price immediately out of the money from the then selling price.
Each could be detrmined with this function:
=smfGetOptionQuotes
Where D7:D125 contains the short leg of the spred (data like this):
EXPD 2 2014 OTM1 Put
and the funcion (=smfGetOptionQuote
EXBD 2 2014 OTM2 Put
Each used to return the OTM1 value for the 2/22 expiration date and the OTM2 value for the 2/22 expriation date. My problem is, OTM1 and OTM2 now don't always uniformly report the same expiration date. I'd like to automate the expiration date so that I know I'm always getting the fourth Saturday of the month by using the OTM1 & OTM2 convention.
Is that possible?
Thanks for all the help.
Regards,
Eric
Tue Feb 4, 2014 4:17 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
On Tue, Feb 4, 2014 at 4:44 PM, <eabartsch@comcast.net> wrote:
> In any event, after (a) I launched the spreadsheet, (b) waited for the
> calculations/web pulls to finish, (c) went to Data/From Web and pulled up
> the Morningtar website in the dialog screen and logged in using my M*
> credentials, (d) closed the spreadsheet, (e) re-launched the spreadsheet,
> and (f) tried the function using the function
> =RCHGetElementNumber(MMM,887), it worked! So, thanks for the help. I will
> see if it sticks when I restart the computer tomorrow.
>
MorningStar can be finicky as far as "sticking";. They have two issues. The
first is being logged in. That does seem to stick for a while. The second
is their daily redirection on the first visit. That isn't sticky and seems
to need to be reset each day.
If I know I'm going to access MorningStar data for the first time in a day,
I quickly use the alt+d+d+w keyboard shortcut and visit their website. It's
one reason I tend to stay away from MorningStar. I forget. See the "Error"
values. THEN visit the website, and run smfForceRecalculation to do it all
over again.
> With respect to the OTM1/OTM2, here is what I meant.
>
> In the old days, there was only one options expiration per month. So,
> OTM1 always returned a value related to that month's expiration date (the
> fourth saturday of the month) equal to the first option strike price
> immediately out of the money from the then selling price. Likewise, OTM2
> always returned a value related to that month's expriation date and the
> second option strike price immediately out of the money from the then
> selling price.
>
> Each could be detrmined with this function:
>
> =smfGetOptionQuotes(D7:D125,"zsba",1)
>
> Where D7:D125 contains the short leg of the spred (data like this):
> EXPD 2 2014 OTM1 Put
>
> and the funcion (=smfGetOptionQuotes(E7:E125, "zsba",1), where E:7:E125
> contains the long leg of the spread (using data like this):
>
> EXBD 2 2014 OTM2 Put
>
> Each used to return the OTM1 value for the 2/22 expiration date and the
> OTM2 value for the 2/22 expriation date. My problem is, OTM1 and OTM2 now
> don't always uniformly report the same expiration date. I'd like to
> automate the expiration date so that I know I'm always getting the fourth
> Saturday of the month by using the OTM1 & OTM2 convention.
>
> Is that possible?
>
As long as Yahoo combines multiple expirations on the same web page, the
OTMx/ITMx are going to be unreliable. That's because it uses the shading
change on the web page to determine the ITM/OTM line, and goes up or down a
number of lines based on that. So if they mix expiration dates on the page,
the number of lines isn't reliable.
Here's what I would do instead. This formula:
=smfGetOptionStrikes("SPY";,"2/22/2014","P","Y",1,4,1)
...returns a 4-row by 1-column array. The four rows will be the first two
strike prices below the equity price and the first two strike prices above
the equity price (not necessarily OTM and ITM -- depends on whether it's a
call or a put). But because of that fifth parameter value of 1, it will
return usable ticker symbols instead of the actual strike prices. For
example:
SPY 2/22 2014 $174.00 Put
SPY 2/22 2014 $175.00 Put
SPY 2/22 2014 $176.00 Put
SPY 2/22 2014 $177.00 Put
If you don't want to hard-code the exact date in the function, you could
use something like to get the February monthly expiration date:
=smfGetOptionExpiry(2014,2,"M")
Now, if you just want a put's OTM1 item from that array:
=INDEX(smfGetOptionStrikes("SPY","2/22/2014","P","Y",1,4,1),2,1)
Or, a put's OTM2 item:
=INDEX(smfGetOptionStrikes("SPY","2/22/2014","P","Y",1,4,1),1,1)
> In any event, after (a) I launched the spreadsheet, (b) waited for the
> calculations/
> the Morningtar website in the dialog screen and logged in using my M*
> credentials, (d) closed the spreadsheet, (e) re-launched the spreadsheet,
> and (f) tried the function using the function
> =RCHGetElementNumbe
> see if it sticks when I restart the computer tomorrow.
>
MorningStar can be finicky as far as "sticking"
first is being logged in. That does seem to stick for a while. The second
is their daily redirection on the first visit. That isn't sticky and seems
to need to be reset each day.
If I know I'm going to access MorningStar data for the first time in a day,
I quickly use the alt+d+d+w keyboard shortcut and visit their website. It's
one reason I tend to stay away from MorningStar. I forget. See the "Error"
values. THEN visit the website, and run smfForceRecalculati
over again.
> With respect to the OTM1/OTM2, here is what I meant.
>
> In the old days, there was only one options expiration per month. So,
> OTM1 always returned a value related to that month's expiration date (the
> fourth saturday of the month) equal to the first option strike price
> immediately out of the money from the then selling price. Likewise, OTM2
> always returned a value related to that month's expriation date and the
> second option strike price immediately out of the money from the then
> selling price.
>
> Each could be detrmined with this function:
>
> =smfGetOptionQuotes
>
> Where D7:D125 contains the short leg of the spred (data like this):
> EXPD 2 2014 OTM1 Put
>
> and the funcion (=smfGetOptionQuote
> contains the long leg of the spread (using data like this):
>
> EXBD 2 2014 OTM2 Put
>
> Each used to return the OTM1 value for the 2/22 expiration date and the
> OTM2 value for the 2/22 expriation date. My problem is, OTM1 and OTM2 now
> don't always uniformly report the same expiration date. I'd like to
> automate the expiration date so that I know I'm always getting the fourth
> Saturday of the month by using the OTM1 & OTM2 convention.
>
> Is that possible?
>
As long as Yahoo combines multiple expirations on the same web page, the
OTMx/ITMx are going to be unreliable. That's because it uses the shading
change on the web page to determine the ITM/OTM line, and goes up or down a
number of lines based on that. So if they mix expiration dates on the page,
the number of lines isn't reliable.
Here's what I would do instead. This formula:
=smfGetOptionStrike
...returns a 4-row by 1-column array. The four rows will be the first two
strike prices below the equity price and the first two strike prices above
the equity price (not necessarily OTM and ITM -- depends on whether it's a
call or a put). But because of that fifth parameter value of 1, it will
return usable ticker symbols instead of the actual strike prices. For
example:
SPY 2/22 2014 $174.00 Put
SPY 2/22 2014 $175.00 Put
SPY 2/22 2014 $176.00 Put
SPY 2/22 2014 $177.00 Put
If you don't want to hard-code the exact date in the function, you could
use something like to get the February monthly expiration date:
=smfGetOptionExpiry
Now, if you just want a put's OTM1 item from that array:
=INDEX(smfGetOption
Or, a put's OTM2 item:
=INDEX(smfGetOption
Tidak ada komentar:
Posting Komentar