15 New Messages
Digest #2717
Messages
Wed Jul 31, 2013 3:13 am (PDT) . Posted by:
"Lawrence" lawleesh
Still got the same results after I ran the macro.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Hmmm. Sounds like it didn't get the web page from FT.com. Try the
> smfForceRecalculation macro and see if getting a fresh copy of the web page
> from the web site gives you different results.
>
> On Tue, Jul 30, 2013 at 8:26 AM, Lawrence <lawrence.leesh@...> wrote:
>
> > The first formula returned blank, not even Error.
> >
> > The second returned "Error".
> >
>
--- In smf_addin@yahoogrou
>
> Hmmm. Sounds like it didn't get the web page from FT.com. Try the
> smfForceRecalculati
> from the web site gives you different results.
>
> On Tue, Jul 30, 2013 at 8:26 AM, Lawrence <lawrence.
>
> > The first formula returned blank, not even Error.
> >
> > The second returned "Error"
> >
>
Wed Jul 31, 2013 3:33 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Typically what I would do at this point is take what this returns:
=RCHGetWebData("
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=G05:SES&subview=BalanceSheet
")
...and put it into an HTML file on my computer so I could display it and
see what the web page looks like that they are delivering to you (since you
didn't get "Error" on the first one).
Several times in doing this, I've gotten some type of message from the web
site. Either indicating excessive use of the web site, or some other reason
the normal web page wasn't sent.
On Wed, Jul 31, 2013 at 3:13 AM, Lawrence <lawrence.leesh@gmail.com > wrote:
> Still got the same results after I ran the macro.
>
=RCHGetWebData(
http://markets.
")
...and put it into an HTML file on my computer so I could display it and
see what the web page looks like that they are delivering to you (since you
didn't get "Error" on the first one).
Several times in doing this, I've gotten some type of message from the web
site. Either indicating excessive use of the web site, or some other reason
the normal web page wasn't sent.
On Wed, Jul 31, 2013 at 3:13 AM, Lawrence <lawrence.leesh@
> Still got the same results after I ran the macro.
>
Wed Jul 31, 2013 7:09 am (PDT) . Posted by:
"Lawrence" lawleesh
Thanks. Does that mean there is no other options?
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Typically what I would do at this point is take what this returns:
>
> =RCHGetWebData("
> http://markets.ft.com/research/Markets/Tearsheets/Financials?s=G05:SES&subview=BalanceSheet
> ")
>
> ...and put it into an HTML file on my computer so I could display it and
> see what the web page looks like that they are delivering to you (since you
> didn't get "Error" on the first one).
>
> Several times in doing this, I've gotten some type of message from the web
> site. Either indicating excessive use of the web site, or some other reason
> the normal web page wasn't sent.
>
> On Wed, Jul 31, 2013 at 3:13 AM, Lawrence <lawrence.leesh@...> wrote:
>
> > Still got the same results after I ran the macro.
> >
>
--- In smf_addin@yahoogrou
>
> Typically what I would do at this point is take what this returns:
>
> =RCHGetWebData(
> http://markets.
> ")
>
> ...and put it into an HTML file on my computer so I could display it and
> see what the web page looks like that they are delivering to you (since you
> didn't get "Error" on the first one).
>
> Several times in doing this, I've gotten some type of message from the web
> site. Either indicating excessive use of the web site, or some other reason
> the normal web page wasn't sent.
>
> On Wed, Jul 31, 2013 at 3:13 AM, Lawrence <lawrence.
>
> > Still got the same results after I ran the macro.
> >
>
Wed Jul 31, 2013 7:54 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
It depends on what that HTML code tells us...
In one instance, we found out that it put a "speed bump" in if 10 requests
are done in a short period of time. The message was something like, "We
have detected a number of requests over a short period of time and assume
it is not a person making the requests, but an automated process."
But since yours is working on one machine and not the other, I have no idea
what might be on that web page.
On Wed, Jul 31, 2013 at 7:09 AM, Lawrence <lawrence.leesh@gmail.com > wrote:
> Thanks. Does that mean there is no other options?
>
> --- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
> >
> > Typically what I would do at this point is take what this returns:
> >
> > =RCHGetWebData("
> >
> http://markets.ft.com/research/Markets/Tearsheets/Financials?s=G05:SES&subview=BalanceSheet
> > ")
> >
> > ...and put it into an HTML file on my computer so I could display it and
> > see what the web page looks like that they are delivering to you (since
> you
> > didn't get "Error" on the first one).
> >
> > Several times in doing this, I've gotten some type of message from the
> web
> > site. Either indicating excessive use of the web site, or some other
> reason
> > the normal web page wasn't sent.
>
In one instance, we found out that it put a "speed bump" in if 10 requests
are done in a short period of time. The message was something like, "We
have detected a number of requests over a short period of time and assume
it is not a person making the requests, but an automated process."
But since yours is working on one machine and not the other, I have no idea
what might be on that web page.
On Wed, Jul 31, 2013 at 7:09 AM, Lawrence <lawrence.leesh@
> Thanks. Does that mean there is no other options?
>
> --- In smf_addin@yahoogrou
> >
> > Typically what I would do at this point is take what this returns:
> >
> > =RCHGetWebData(
> >
> http://markets.
> > ")
> >
> > ...and put it into an HTML file on my computer so I could display it and
> > see what the web page looks like that they are delivering to you (since
> you
> > didn't get "Error" on the first one).
> >
> > Several times in doing this, I've gotten some type of message from the
> web
> > site. Either indicating excessive use of the web site, or some other
> reason
> > the normal web page wasn't sent.
>
Wed Jul 31, 2013 5:28 am (PDT) . Posted by:
"nautilustech" nautilustech
>
> Also, Yahoo can be unreliable with the OTMn and ITMn parameters, when Yahoo carries multiple expiration dates on the same web page. I would suggest using the smfGetOptionStrikes() function instead.
OK, thanks. I've changed to this function:
=smfGetOptionStrikes("BG",DATE(2013,8,17),"C","OX")
which works fine. However, this appears to return the OTM1 strike only. I can't determine how to retrieve other strike prices.
Again, thanks for the help.
> Also, Yahoo can be unreliable with the OTMn and ITMn parameters, when Yahoo carries multiple expiration dates on the same web page. I would suggest using the smfGetOptionStrikes
OK, thanks. I've changed to this function:
=smfGetOptionStrike
which works fine. However, this appears to return the OTM1 strike only. I can't determine how to retrieve other strike prices.
Again, thanks for the help.
Wed Jul 31, 2013 7:51 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
It returns an equal number of ITM and OTM prices, so if you just enter it
into one cell, you'd get the OTM price. If you array-enter it over 2 rows,
you'd get the first ITM and first OTM. If you want the first four ITM and
first four OTM prices, you'd array-enter it over 8 rows.
So, if you just want the first ITM, you could do:
=smfGetOptionStrikes("BG",DATE(2013,8,17),"C","OX",,2,1)
The "2,1" at the end says to return an array of 2-rows by 1 column, so the
first row would be the ITM price and the second row would be the OTM price,
but if you just enter it into a single cell, all you'd see is the first row
-- which is the ITM price.
On Wed, Jul 31, 2013 at 5:28 AM, nautilustech <nautilustech@yahoo.com >wrote:
>
> OK, thanks. I've changed to this function:
>
> =smfGetOptionStrikes("BG",DATE(2013,8,17),"C","OX")
>
> which works fine. However, this appears to return the OTM1 strike
> only. I can't determine how to retrieve other strike prices.
>
into one cell, you'd get the OTM price. If you array-enter it over 2 rows,
you'd get the first ITM and first OTM. If you want the first four ITM and
first four OTM prices, you'd array-enter it over 8 rows.
So, if you just want the first ITM, you could do:
=smfGetOptionStrike
The "2,1" at the end says to return an array of 2-rows by 1 column, so the
first row would be the ITM price and the second row would be the OTM price,
but if you just enter it into a single cell, all you'd see is the first row
-- which is the ITM price.
On Wed, Jul 31, 2013 at 5:28 AM, nautilustech <nautilustech@
>
> OK, thanks. I've changed to this function:
>
> =smfGetOptionStrike
>
> which works fine. However, this appears to return the OTM1 strike
> only. I can't determine how to retrieve other strike prices.
>
Wed Jul 31, 2013 8:50 am (PDT) . Posted by:
"Craig" c1sander
I have a long list of about 1000 different symbols. I am trying to use RCHGetYahooQuotes to fetch data (l1 for example) and am doing this using 200 element arrays stacked on top of each other. (I've tried 10 100 element arrays and have the same problem.) This works fine for the top two groups, then I start getting blank cells. For this error (blank cells)to occur, I need to use different symbols. If I use the same group of 200 symbols, repeated on top of each other in 5 groups, I get no blank cells.
Any suggestions?
Thanks,
Craig
Any suggestions?
Thanks,
Craig
Wed Jul 31, 2013 12:06 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Sorry, but I have absolutely no experience with getting that much data at
one time. I usually only get about 10-20 quotes. From time to time, I'll
get quotes of the S&P 500, but have had no issues there with three
RCHGetYahooQuotes() functions. But it's usually after hours too, which
probably makes a difference.
Can you stagger the update? For example, instead of using
smfForceRecalculation or the NOW() trick to trigger the update of the
quotes, use a cell reference for the NOW() value, but put a value of 1 or 2
or 3 in that cell. Then, that set of 200 quotes will only update when that
cell value changes. You could have one cell for each set of 200 quotes.
Then change the "trigger" cells one by one, with maybe a few seconds in
between (you could even have VBA do it, just incrementing the values with a
timer in between).
I don't know if that would make a difference or not.
On Wed, Jul 31, 2013 at 8:50 AM, Craig <craig.sander@comcast.net > wrote:
> I have a long list of about 1000 different symbols. I am trying to use
> RCHGetYahooQuotes to fetch data (l1 for example) and am doing this using
> 200 element arrays stacked on top of each other. (I've tried 10 100
> element arrays and have the same problem.) This works fine for the top two
> groups, then I start getting blank cells. For this error (blank cells)to
> occur, I need to use different symbols. If I use the same group of 200
> symbols, repeated on top of each other in 5 groups, I get no blank cells.
>
> Any suggestions?
>
one time. I usually only get about 10-20 quotes. From time to time, I'll
get quotes of the S&P 500, but have had no issues there with three
RCHGetYahooQuotes(
probably makes a difference.
Can you stagger the update? For example, instead of using
smfForceRecalculati
quotes, use a cell reference for the NOW() value, but put a value of 1 or 2
or 3 in that cell. Then, that set of 200 quotes will only update when that
cell value changes. You could have one cell for each set of 200 quotes.
Then change the "trigger" cells one by one, with maybe a few seconds in
between (you could even have VBA do it, just incrementing the values with a
timer in between).
I don't know if that would make a difference or not.
On Wed, Jul 31, 2013 at 8:50 AM, Craig <craig.sander@
> I have a long list of about 1000 different symbols. I am trying to use
> RCHGetYahooQuotes to fetch data (l1 for example) and am doing this using
> 200 element arrays stacked on top of each other. (I've tried 10 100
> element arrays and have the same problem.) This works fine for the top two
> groups, then I start getting blank cells. For this error (blank cells)to
> occur, I need to use different symbols. If I use the same group of 200
> symbols, repeated on top of each other in 5 groups, I get no blank cells.
>
> Any suggestions?
>
Wed Jul 31, 2013 5:20 pm (PDT) . Posted by:
"Jason Strauss" g3m1n1980
Does this template work anymore
Wed Jul 31, 2013 6:22 pm (PDT) . Posted by:
"Craig" c1sander
Randy,
Thanks for the suggestion. I tried a first group of 200 without using the NOW() command and followed it by a group of 100 with the NOW() command. The last cell of the second group comes back blank and when I recalculate it stays blank.
Craig
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Sorry, but I have absolutely no experience with getting that much data at
> one time. I usually only get about 10-20 quotes. From time to time, I'll
> get quotes of the S&P 500, but have had no issues there with three
> RCHGetYahooQuotes() functions. But it's usually after hours too, which
> probably makes a difference.
>
> Can you stagger the update? For example, instead of using
> smfForceRecalculation or the NOW() trick to trigger the update of the
> quotes, use a cell reference for the NOW() value, but put a value of 1 or 2
> or 3 in that cell. Then, that set of 200 quotes will only update when that
> cell value changes. You could have one cell for each set of 200 quotes.
> Then change the "trigger" cells one by one, with maybe a few seconds in
> between (you could even have VBA do it, just incrementing the values with a
> timer in between).
>
> I don't know if that would make a difference or not.
>
> On Wed, Jul 31, 2013 at 8:50 AM, Craig <craig.sander@...> wrote:
>
> > I have a long list of about 1000 different symbols. I am trying to use
> > RCHGetYahooQuotes to fetch data (l1 for example) and am doing this using
> > 200 element arrays stacked on top of each other. (I've tried 10 100
> > element arrays and have the same problem.) This works fine for the top two
> > groups, then I start getting blank cells. For this error (blank cells)to
> > occur, I need to use different symbols. If I use the same group of 200
> > symbols, repeated on top of each other in 5 groups, I get no blank cells.
> >
> > Any suggestions?
> >
>
Thanks for the suggestion. I tried a first group of 200 without using the NOW() command and followed it by a group of 100 with the NOW() command. The last cell of the second group comes back blank and when I recalculate it stays blank.
Craig
--- In smf_addin@yahoogrou
>
> Sorry, but I have absolutely no experience with getting that much data at
> one time. I usually only get about 10-20 quotes. From time to time, I'll
> get quotes of the S&P 500, but have had no issues there with three
> RCHGetYahooQuotes(
> probably makes a difference.
>
> Can you stagger the update? For example, instead of using
> smfForceRecalculati
> quotes, use a cell reference for the NOW() value, but put a value of 1 or 2
> or 3 in that cell. Then, that set of 200 quotes will only update when that
> cell value changes. You could have one cell for each set of 200 quotes.
> Then change the "trigger" cells one by one, with maybe a few seconds in
> between (you could even have VBA do it, just incrementing the values with a
> timer in between).
>
> I don't know if that would make a difference or not.
>
> On Wed, Jul 31, 2013 at 8:50 AM, Craig <craig.sander@
>
> > I have a long list of about 1000 different symbols. I am trying to use
> > RCHGetYahooQuotes to fetch data (l1 for example) and am doing this using
> > 200 element arrays stacked on top of each other. (I've tried 10 100
> > element arrays and have the same problem.) This works fine for the top two
> > groups, then I start getting blank cells. For this error (blank cells)to
> > occur, I need to use different symbols. If I use the same group of 200
> > symbols, repeated on top of each other in 5 groups, I get no blank cells.
> >
> > Any suggestions?
> >
>
Wed Jul 31, 2013 6:26 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
No. The web site is gone.
On Wed, Jul 31, 2013 at 5:20 PM, Jason Strauss <xxjaysonxx@aol.com > wrote:
> Does this template work anymore
>
On Wed, Jul 31, 2013 at 5:20 PM, Jason Strauss <xxjaysonxx@aol.
> Does this template work anymore
>
Wed Jul 31, 2013 12:53 pm (PDT) . Posted by:
"gbullr" gbullr
Help Please
Have this formula
=smfgetoptionquotes($G5,I$3,0,"Y") in a cell
where
g5 = VIX Jan 2014 $24 Call
i3 = b
I have also tried ^VIX and I get an error in both instances.
Using Excel 2010
Other options work fine.
Thoughts / Suggestions
Thanks.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> I just uploaded RCH_Stock_Market_Functions-2.1.2013.06.28.zip to the "Works
> in Progress" folder. I just expanded the "mini" option coding I added at
> the start of the month. It was specific for a "7" instead of any numeric.
>
> Let me know if that works for you...
>
> On Fri, Jun 28, 2013 at 12:37 PM, Don <don@...> wrote:
>
> > Would it be possible for you to add something along the lines of ****
> > below, or add another variable, to be able to get Yahoo quotes for stocks
> > like CX which has non-standard options such as CX4 in 2014? Unfortunately,
> > the sURL and the Find1 are different.
> >
> > 'modGetOptionQuotes
> >
> > 'Function smfGetYahooOptionQuote
> >
> > '------------------> Do primary search
> > Dim nLast As Variant
> > If sTicker = "VIX" Then sTicker = "^VIX"
> >
> > **** If IsNumeric(Right(sTicker, 1)) Then sTicker = Left(sTicker,
> > Len(sTicker) - 1)
> >
> > sURL = "http://finance.yahoo.com/q/op?s= " & sTicker & "&m=" &
> > Format(pExpiry, "yyyy-mm")
> >
> >
>
Have this formula
=smfgetoptionquotes
where
g5 = VIX Jan 2014 $24 Call
i3 = b
I have also tried ^VIX and I get an error in both instances.
Using Excel 2010
Other options work fine.
Thoughts / Suggestions
Thanks.
--- In smf_addin@yahoogrou
>
> I just uploaded RCH_Stock_Market_
> in Progress" folder. I just expanded the "mini" option coding I added at
> the start of the month. It was specific for a "7" instead of any numeric.
>
> Let me know if that works for you...
>
> On Fri, Jun 28, 2013 at 12:37 PM, Don <don@...> wrote:
>
> > Would it be possible for you to add something along the lines of ****
> > below, or add another variable, to be able to get Yahoo quotes for stocks
> > like CX which has non-standard options such as CX4 in 2014? Unfortunately,
> > the sURL and the Find1 are different.
> >
> > 'modGetOptionQu
> >
> > 'Function smfGetYahooOptionQu
> >
> > '-------
> > Dim nLast As Variant
> > If sTicker = "VIX" Then sTicker = "^VIX"
> >
> > **** If IsNumeric(Right(
> > Len(sTicker) - 1)
> >
> > sURL = "http://finance.
> > Format(pExpiry, "yyyy-mm"
> >
> >
>
Wed Jul 31, 2013 2:01 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
This works for me:
=smfGetOptionQuotes("VIX 1/22 2014 $24 Call","b",0,"Y")
...but you need the beta version of the add-in from the "Works in Progress"
folder from the FILES area of the group in order to make the "VIX" symbol
work -- because Yahoo doesn't create a consistent URL for the option pages
for ticker symbol "^VIX". I had to add code to handle this exception.
Also, note that I had to use "1/22" instead of "Jan". That's because ^VIX
uses non-standard monthly expiration dates.
On Wed, Jul 31, 2013 at 12:53 PM, gbullr <gaston.bullrich@gmail.com > wrote:
> Help Please
>
> Have this formula
>
> =smfgetoptionquotes($G5,I$3,0,"Y") in a cell
>
> where
> g5 = VIX Jan 2014 $24 Call
> i3 = b
>
> I have also tried ^VIX and I get an error in both instances.
>
> Using Excel 2010
>
> Other options work fine.
>
=smfGetOptionQuotes
...but you need the beta version of the add-in from the "Works in Progress"
folder from the FILES area of the group in order to make the "VIX" symbol
work -- because Yahoo doesn't create a consistent URL for the option pages
for ticker symbol "^VIX"
Also, note that I had to use "1/22" instead of "Jan"
uses non-standard monthly expiration dates.
On Wed, Jul 31, 2013 at 12:53 PM, gbullr <gaston.bullrich@
> Help Please
>
> Have this formula
>
> =smfgetoptionquotes
>
> where
> g5 = VIX Jan 2014 $24 Call
> i3 = b
>
> I have also tried ^VIX and I get an error in both instances.
>
> Using Excel 2010
>
> Other options work fine.
>
Wed Jul 31, 2013 2:18 pm (PDT) . Posted by:
"Lawrence" lawleesh
Hi Randy
Is there a way to put a formula in excel to return "Currency in SGD" for this web page (found on last line)?
http://sg.finance.yahoo.com/q/is?s=EB5.SI&annual
I realize that there is no element number for Yahoo data currency. It is only available for Google.
thanks
LL
Is there a way to put a formula in excel to return "Currency in SGD" for this web page (found on last line)?
http://sg.finance.
I realize that there is no element number for Yahoo data currency. It is only available for Google.
thanks
LL
Wed Jul 31, 2013 2:38 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
You can get the "Currency in" from that web page with another function:
=smfstrExtr(RCHGetTableCell("
http://sg.finance.yahoo.com/q/is?s=EB5.SI&annual ",0,"Currency in")," in
",".")
But I don't know of any way to tell Yahoo to give you the data in USD. You
could use the exchange rate to convert them, but I'm not sure it makes
sense to apply the current exchange rate to financial statements from
different periods in time. I would think you'd need the exchange rate as of
each point in time.
On Wed, Jul 31, 2013 at 2:18 PM, Lawrence <lawrence.leesh@gmail.com > wrote:
>
> Is there a way to put a formula in excel to return "Currency in SGD" for
> this web page (found on last line)?
>
> http://sg.finance.yahoo.com/q/is?s=EB5.SI&annual
>
> I realize that there is no element number for Yahoo data currency. It is
> only available for Google.
>
=smfstrExtr(
http://sg.finance.
","
But I don't know of any way to tell Yahoo to give you the data in USD. You
could use the exchange rate to convert them, but I'm not sure it makes
sense to apply the current exchange rate to financial statements from
different periods in time. I would think you'd need the exchange rate as of
each point in time.
On Wed, Jul 31, 2013 at 2:18 PM, Lawrence <lawrence.leesh@
>
> Is there a way to put a formula in excel to return "Currency in SGD" for
> this web page (found on last line)?
>
> http://sg.finance.
>
> I realize that there is no element number for Yahoo data currency. It is
> only available for Google.
>
