15 New Messages
Digest #2922
Messages
Fri Jan 10, 2014 1:32 am (PST) . Posted by:
63b31a65c9c738e4af34541b0710492f
I wonder how the output of smfGetOptionStrikes could be transposed to show available strikes in a row instead of a column? Is there an easy way or would it require significant code changes? A simple Application.Transpose (vData) at the end of the function is obviously not sufficient...
Any suggestions?
Any suggestions?
Fri Jan 10, 2014 5:54 am (PST) . Posted by:
"Randy Harmelink" rharmelink
If you want to transpose what is returned, you'll need to use the pRows and
pCols parameters, because otherwise the size of the array to return is
determined by the range you enter the function into. And the number of rows
determines which strike prices are returned. So, if I want the first two
ITM and first two OTM strike prices, something like:
=TRANSPOSE(smfGetOptionStrikes("MMM","01/18/2014",,,,4,1))
On Fri, Jan 10, 2014 at 2:32 AM, <m.heritsch@gmail.com> wrote:
> I wonder how the output of smfGetOptionStrikes could be transposed to
> show available strikes in a row instead of a column? Is there an easy way
> or would it require significant code changes? A simple
> Application.Transpose (vData) at the end of the function is obviously not
> sufficient...
>
> Any suggestions?
>
pCols parameters, because otherwise the size of the array to return is
determined by the range you enter the function into. And the number of rows
determines which strike prices are returned. So, if I want the first two
ITM and first two OTM strike prices, something like:
=TRANSPOSE(smfGetOp
On Fri, Jan 10, 2014 at 2:32 AM, <m.heritsch@gmail.
> I wonder how the output of smfGetOptionStrikes could be transposed to
> show available strikes in a row instead of a column? Is there an easy way
> or would it require significant code changes? A simple
> Application.
> sufficient..
>
> Any suggestions?
>
Fri Jan 10, 2014 8:28 am (PST) . Posted by:
63b31a65c9c738e4af34541b0710492f
Thanks Randy, that seems to work. I have created my own function like this:
smfGetOptionStrikesTransposed = Application.Transpose(smfGetOptionStrikes(pTicker, pExpiry, pPutCall, pSource, pSymbols, 2, 1, pType))
However, as I am doing this for several stocks (say 10 stocks, one per row, and 2 strikes, first ITM and first OTM, one per column) this somehow is getting slower and slower as I am adding more rows - somehow as if the small (1 row, 2 columns) arrays are somehow "linked" and each time I'm adding another row, the previous rows are re-calculated and sometimes even messed-up ("None" appearing in the first column).
I am subsequently adding the following array function after selecting a 1 row 2 columns area:
=smfGetOptionStrikesTransposed($A3;$N$1;"C";"Y")
(ticker symbols are in column A and expiry is in cell N1)
Any idea what's happening?
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote:
If you want to transpose what is returned, you'll need to use the pRows and pCols parameters, because otherwise the size of the array to return is determined by the range you enter the function into. And the number of rows determines which strike prices are returned. So, if I want the first two ITM and first two OTM strike prices, something like:
=TRANSPOSE(smfGetOptionStrikes("MMM","01/18/2014",,,,4,1))
On Fri, Jan 10, 2014 at 2:32 AM, <m.heritsch@... mailto:m.heritsch@...> wrote:
I wonder how the output of smfGetOptionStrikes could be transposed to show available strikes in a row instead of a column? Is there an easy way or would it require significant code changes? A simple Application.Transpose (vData) at the end of the function is obviously not sufficient...
Any suggestions?
smfGetOptionStrikes
However, as I am doing this for several stocks (say 10 stocks, one per row, and 2 strikes, first ITM and first OTM, one per column) this somehow is getting slower and slower as I am adding more rows - somehow as if the small (1 row, 2 columns) arrays are somehow "linked" and each time I'm adding another row, the previous rows are re-calculated and sometimes even messed-up ("None" appearing in the first column).
I am subsequently adding the following array function after selecting a 1 row 2 columns area:
=smfGetOptionStrike
(ticker symbols are in column A and expiry is in cell N1)
Any idea what's happening?
---In smf_addin@yahoogrou
If you want to transpose what is returned, you'll need to use the pRows and pCols parameters, because otherwise the size of the array to return is determined by the range you enter the function into. And the number of rows determines which strike prices are returned. So, if I want the first two ITM and first two OTM strike prices, something like:
=TRANSPOSE(smfGetOp
On Fri, Jan 10, 2014 at 2:32 AM, <m.heritsch@
I wonder how the output of smfGetOptionStrikes could be transposed to show available strikes in a row instead of a column? Is there an easy way or would it require significant code changes? A simple Application.
Any suggestions?
Fri Jan 10, 2014 9:22 am (PST) . Posted by:
"Randy Harmelink" rharmelink
"None" is what gets returned if the add-in doesn't find anything.
Are you using Yahoo? Someone earlier today mentioned having issues with
slower response and empty data getting returned from Yahoo for
RCHGetYahooQuotes(). Maybe they are having server issues today...
On Fri, Jan 10, 2014 at 9:28 AM, <m.heritsch@gmail.com> wrote:
> Thanks Randy, that seems to work. I have created my own function like
> this:
>
> smfGetOptionStrikesTransposed =
> Application.Transpose(smfGetOptionStrikes(pTicker, pExpiry, pPutCall,
> pSource, pSymbols, 2, 1, pType))
>
> However, as I am doing this for several stocks (say 10 stocks, one per
> row, and 2 strikes, first ITM and first OTM, one per column) this somehow
> is getting slower and slower as I am adding more rows - somehow as if the
> small (1 row, 2 columns) arrays are somehow "linked" and each time I'm
> adding another row, the previous rows are re-calculated and sometimes even
> messed-up ("None" appearing in the first column).
>
> I am subsequently adding the following array function after selecting a 1
> row 2 columns area:
>
> =smfGetOptionStrikesTransposed($A3;$N$1;"C";"Y")
>
> (ticker symbols are in column A and expiry is in cell N1)
> Any idea what's happening?
>
Are you using Yahoo? Someone earlier today mentioned having issues with
slower response and empty data getting returned from Yahoo for
RCHGetYahooQuotes(
On Fri, Jan 10, 2014 at 9:28 AM, <m.heritsch@gmail.
> Thanks Randy, that seems to work. I have created my own function like
> this:
>
> smfGetOptionStrikes
> Application.
> pSource, pSymbols, 2, 1, pType))
>
> However, as I am doing this for several stocks (say 10 stocks, one per
> row, and 2 strikes, first ITM and first OTM, one per column) this somehow
> is getting slower and slower as I am adding more rows - somehow as if the
> small (1 row, 2 columns) arrays are somehow "linked" and each time I'm
> adding another row, the previous rows are re-calculated and sometimes even
> messed-up ("None" appearing in the first column).
>
> I am subsequently adding the following array function after selecting a 1
> row 2 columns area:
>
> =smfGetOptionStrike
>
> (ticker symbols are in column A and expiry is in cell N1)
> Any idea what's happening?
>
Fri Jan 10, 2014 9:52 am (PST) . Posted by:
63b31a65c9c738e4af34541b0710492f
yes, I'm using Yahoo. But why is an array changing form showing correct values to "None" while I am adding another array elsewhere?? This happens even if I am not dragging the array formula but entering it separately in another area of the sheet, not even close to the previous one... ? The only thing these two arrays have in common is the fact they are using the same transposed function. Unfortunately I don't know much about how Excel manages array formulas - maybe it always recalculates all instances of arrays where the same array function is used as soon as one instance is changed?
Fri Jan 10, 2014 10:10 am (PST) . Posted by:
"Randy Harmelink" rharmelink
I'm not aware of transpose making formulas volatile. They should be
non-volatile, unless the parameters are being changed somehow?
I just tried a bunch of transposed formulas, using the formula I gave you
earlier. No issues. No "None" values.
They don't appear to be recalculating as I enter each additional formula.
Did you make your VBA function volatile?
On Fri, Jan 10, 2014 at 10:52 AM, <m.heritsch@gmail.com> wrote:
> yes, I'm using Yahoo. But why is an array changing form showing correct
> values to "None" while I am adding another array elsewhere?? This happens
> even if I am not dragging the array formula but entering it separately in
> another area of the sheet, not even close to the previous one... ? The only
> thing these two arrays have in common is the fact they are using the same
> transposed function. Unfortunately I don't know much about how Excel
> manages array formulas - maybe it always recalculates all instances of
> arrays where the same array function is used as soon as one instance is
> changed?
>
non-volatile, unless the parameters are being changed somehow?
I just tried a bunch of transposed formulas, using the formula I gave you
earlier. No issues. No "None" values.
They don't appear to be recalculating as I enter each additional formula.
Did you make your VBA function volatile?
On Fri, Jan 10, 2014 at 10:52 AM, <m.heritsch@gmail.
> yes, I'm using Yahoo. But why is an array changing form showing correct
> values to "None" while I am adding another array elsewhere?? This happens
> even if I am not dragging the array formula but entering it separately in
> another area of the sheet, not even close to the previous one... ? The only
> thing these two arrays have in common is the fact they are using the same
> transposed function. Unfortunately I don't know much about how Excel
> manages array formulas - maybe it always recalculates all instances of
> arrays where the same array function is used as soon as one instance is
> changed?
>
Fri Jan 10, 2014 6:41 am (PST) . Posted by:
wdcorpening
Okay, I am just an occasional visitor here. I use the calls basically to get up to date stock data from Yahoo! into my various spreadsheets. I have noticed recently that spreadsheets that have worked flawlessly for many, many months, now fail to pull over all the requested stock data. If there are say 30 stock prices to acquire, 3-5 may be blank. The macro call is still there in the cell, it just failed to fill on startup. I generally use the call: "=RCHGetYahooQ
So does anyone know what is going on? I also notice that it seems to take more time to load the data than it did say 6 months ago.
Fri Jan 10, 2014 7:58 am (PST) . Posted by:
"Randy Harmelink" rharmelink
That can happen when Yahoo gets slow, for whatever reason. They're in the
midst of a "beautification" project of My Yahoo web pages, so that might be
taking a lot of server time. And, alas, their "beautification" of the
portfolio display in My Yahoo has removed features. Bah.
In any case, I'd suggest array-entering the formula and getting all the
prices with one Internet access.
On Fri, Jan 10, 2014 at 7:41 AM, <wdcorp2775@comcast.net> wrote:
> Okay, I am just an occasional visitor here. I use the calls basically
> to get up to date stock data from Yahoo! into my various spreadsheets. I
> have noticed recently that spreadsheets that have worked flawlessly for
> many, many months, now fail to pull over all the requested stock data. If
> there are say 30 stock prices to acquire, 3-5 may be blank. The macro call
> is still there in the cell, it just failed to fill on startup. I generally
> use the call: "=RCHGetYahooQuotes(E48,NewPrice)"
>
> So does anyone know what is going on? I also notice that it seems to take
> more time to load the data than it did say 6 months ago.
>
midst of a "beautificatio
taking a lot of server time. And, alas, their "beautificatio
portfolio display in My Yahoo has removed features. Bah.
In any case, I'd suggest array-entering the formula and getting all the
prices with one Internet access.
On Fri, Jan 10, 2014 at 7:41 AM, <wdcorp2775@comcast.
> Okay, I am just an occasional visitor here. I use the calls basically
> to get up to date stock data from Yahoo! into my various spreadsheets. I
> have noticed recently that spreadsheets that have worked flawlessly for
> many, many months, now fail to pull over all the requested stock data. If
> there are say 30 stock prices to acquire, 3-5 may be blank. The macro call
> is still there in the cell, it just failed to fill on startup. I generally
> use the call: "=RCHGetYahooQ
>
> So does anyone know what is going on? I also notice that it seems to take
> more time to load the data than it did say 6 months ago.
>
Fri Jan 10, 2014 12:18 pm (PST) . Posted by:
Buck_69_69
Hi.. i'm using this
=RCHGetHTMLTable("http://emma.msrb.org/SecurityView/SecurityDetailsTrades.aspx?cusip=251129R61","Trade http://emma.msrb.org/SecurityView/SecurityDetailsTrades.aspx?cusip=251129R61%22,%22TradeDate/",-1,"",1)
but all i get back is tradedate, how do i get back the first row of the trading activity
=RCHGetHTMLTable(
but all i get back is tradedate, how do i get back the first row of the trading activity
Fri Jan 10, 2014 12:41 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
You need to array-enter the formula over a range. If you just enter it into
a single cell, you just get the first row and first column of returned data.
On Fri, Jan 10, 2014 at 12:51 PM, <buck_69_69@yahoo.com> wrote:
>
> Hi.. i'm using this
>
> =RCHGetHTMLTable("
> http://emma.msrb.org/SecurityView/SecurityDetailsTrades.aspx?cusip=251129R61","Trade
> Date/",-1,"",1)
>
> but all i get back is tradedate, how do i get back the first row of the
> trading activity
>
a single cell, you just get the first row and first column of returned data.
On Fri, Jan 10, 2014 at 12:51 PM, <buck_69_69@yahoo.
>
> Hi.. i'm using this
>
> =RCHGetHTMLTable(
> http://emma.
> Date/",
>
> but all i get back is tradedate, how do i get back the first row of the
> trading activity
>
Fri Jan 10, 2014 1:21 pm (PST) . Posted by:
Buck_69_69
Hi Randy
if i can even get the first column of data returned that'll be a start.. but all i get back is tradedate
if i can even get the first column of data returned that'll be a start.. but all i get back is tradedate
Fri Jan 10, 2014 12:37 pm (PST) . Posted by:
"PeteA" option2z
While this isn't exactly on topic, it IS relevant to those who access YAHOO
data with SMF.
In my investing system (which heavily uses SMF), one of the utilities
(non-SMF) is a server-side updater (for example
<http://www.ttp-s.com/php/BBTdata.php> ) that runs every 15 minutes during
market hours. It updates the current prices for about 40 stocks, and also
recalculates a 2 day RSI. This is a fair amount of updating going on in each
run.
It failed so often when accessing YAHOO data, that I had to build in a retry
loop, which waits a few seconds (I've found setting it to two or three works
best) after a failure and tries again.and again..
The utility reports out when it has to do retries to get data, but only when
it has to try more than 1 time. Here's a snip of what it typically reports.
(GT 1 only) 2 Tries: Yahoo Single Price. Retry in: 2 secs for RYN
(GT 1 only) 2 Tries: Yahoo Single Price. Retry in: 2 secs for TRP
(GT 1 only) 2 Tries: Yahoo Single Price. Retry in: 2 secs for OLN
This is fairly typical 3 of 40 took more than 1 retry. I'm sharing this
because I suspect I'm not the only one with access failures from YAHOO.
BTW, the retries occasionally go up to 5 or so (my utility cancels itself in
any stock passes 7, and that has not happened).
Similar things happen when accessing historical price data as well.with a
similar loop.
Pete A
data with SMF.
In my investing system (which heavily uses SMF), one of the utilities
(non-SMF) is a server-side updater (for example
<http://www.ttp-
market hours. It updates the current prices for about 40 stocks, and also
recalculates a 2 day RSI. This is a fair amount of updating going on in each
run.
It failed so often when accessing YAHOO data, that I had to build in a retry
loop, which waits a few seconds (I've found setting it to two or three works
best) after a failure and tries again.and again..
The utility reports out when it has to do retries to get data, but only when
it has to try more than 1 time. Here's a snip of what it typically reports.
(GT 1 only) 2 Tries: Yahoo Single Price. Retry in: 2 secs for RYN
(GT 1 only) 2 Tries: Yahoo Single Price. Retry in: 2 secs for TRP
(GT 1 only) 2 Tries: Yahoo Single Price. Retry in: 2 secs for OLN
This is fairly typical 3 of 40 took more than 1 retry. I'm sharing this
because I suspect I'm not the only one with access failures from YAHOO.
BTW, the retries occasionally go up to 5 or so (my utility cancels itself in
any stock passes 7, and that has not happened).
Similar things happen when accessing historical price data as well.with a
similar loop.
Pete A
Fri Jan 10, 2014 1:14 pm (PST) . Posted by:
mrothaus
No question that YAHOO is failing a lot lately. Basically all I use is this formula to get the latest stock price:
=RCHGetYahooQuotes(<datacell>, "l1")
a few of the cells don't update when I open the spreadsheet, and it takes longer than usual for the spreadsheet to open.
How do I implement a retry like you suggest?
=RCHGetYahooQuotes(
a few of the cells don't update when I open the spreadsheet, and it takes longer than usual for the spreadsheet to open.
How do I implement a retry like you suggest?
Fri Jan 10, 2014 1:17 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
You'd need to write your own VBA routine to do retries...
Easiest solution is to do one array-entered formula to get all of your
prices in one Internet access. It's faster, and less likely to have a
failure.
On Fri, Jan 10, 2014 at 1:53 PM, <MRothaus@yahoo.com> wrote:
>
> No question that YAHOO is failing a lot lately. Basically all I use is
> this formula to get the latest stock price:
>
> =RCHGetYahooQuotes(<datacell>, "l1")
>
> a few of the cells don't update when I open the spreadsheet, and it takes
> longer than usual for the spreadsheet to open.
>
> How do I implement a retry like you suggest?
>
Easiest solution is to do one array-entered formula to get all of your
prices in one Internet access. It's faster, and less likely to have a
failure.
On Fri, Jan 10, 2014 at 1:53 PM, <MRothaus@yahoo.
>
> No question that YAHOO is failing a lot lately. Basically all I use is
> this formula to get the latest stock price:
>
> =RCHGetYahooQuotes(
>
> a few of the cells don't update when I open the spreadsheet, and it takes
> longer than usual for the spreadsheet to open.
>
> How do I implement a retry like you suggest?
>
Fri Jan 10, 2014 1:24 pm (PST) . Posted by:
mikemcq802
As Randy said, entering them as an array formula will be much faster.
Further, you can use the smfforcerecalculation macro described here:
http://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/7625
That way if the prices are wrong you can more easily try again without having to close and re-open the workbook.
Further, you can use the smfforcerecalculati
http://groups.
That way if the prices are wrong you can more easily try again without having to close and re-open the workbook.
Tidak ada komentar:
Posting Komentar