15 Messages
Digest #3226
Messages
Thu Oct 30, 2014 5:39 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
You appear to be doing everything I would suggest, so I'm at a loss. Unless
you are going over the 1000 web page limit DURING the process? Because
smfForceRecalculation would reset that, but not in the middle of a 1000+
web page retrieval process...
In any case, I just tried this VBA code:
For Each Cell In Range("D4:D7")
Cell.Offset(0, 1) = (RCHGetTableCell("https://finance.yahoo.com/q?s=" &
Cell.Value & "&ql=1", 1, "Legal Type") = "Exchange Traded Fund")
Next Cell
...and got the results I think you're looking for:
Ticker ETF? SPY TRUE IWM TRUE MMM FALSE VFINX FALSE
On Thu, Oct 30, 2014 at 1:17 PM, brad.reel@... wrote:
> Hi Randy. Stuck on this.
>
> From a process perspective I have a screen running (whole different
> process, through my broker), I get that list and it usually has 10 or 20
> symbols that are new to me. I built a procedure to go check if they are an
> ETF or not. I need to know if they are an ETF or not for other downstream
> processes.
>
> I have a VBA sub that is invoking the following code:
>
> If ETFCheck = True Then
>
> Symbol = Sheets(SheetName).Range("A" & SymbolLoop).Value
>
> ETFResponse = RCHGetTableCell("https://finance.yahoo.com/q?s="
> & Symbol & "&ql=1", 1, "Legal Type")
>
> If ETFResponse = "Exchange Traded Fund" Then
>
> Sheets(SheetName).Range("B" & SymbolLoop).Value = True
>
> Else
>
> Sheets(SheetName).Range("B" & SymbolLoop).Value = False
>
> End If
> Basically, if I get a new symbol, I check it and if it results in
> "Exchange Traded Fund" I set a value to TRUE, otherwise FALSE (in the case
> of an equity).
>
> At the end of the day, I am trying to confirm whether or not a list of
> symbols I have is an ETF or stock. The information I am pulling on the web
> page is the most reliable source I have found. So, first, if you are aware
> of some other simpler way I can query on a symbol and get whether or not it
> is an ETF, I am all ears.
>
> The main point is I noticed I was not getting consistent results when
> looking at the output. Symbols I knew were ETF's were coming back as
> equities and vice-versa. So, to troubleshoot, I pulled out the formula and
> built a spreadsheet with 100 items to just make sure I wasn't doing
> something else wrong, etc. When I tried to retrieve for the 100 symbols,
> the result I got back was "Error" for all 100. "Error" is the result I
> would expect for a stock (since this field does not exist on the page for a
> given stock). This is basically the same result I was seeing in my
> procedure output. I am not really getting anything that should be set to
> ETF to be set that way; everything is coming back as equity.
>
> In the spreadsheet, my suspicion is that this might come down to the page
> being cached. I've tried everything I found in your FAQ to address that
> (clear browser, set to get new page every time, run your macro, etc.). and
> I suspect that could be the problem I am experiencing on the spreadsheet.
> Ultimately the question I have that goes with my very long-winded
> explanation is could I have the same caching issue when making an
> individual VBA procedure call for each symbol? And if I am, what good way
> is there for me to work around it?
>
>
you are going over the 1000 web page limit DURING the process? Because
smfForceRecalculation would reset that, but not in the middle of a 1000+
web page retrieval process...
In any case, I just tried this VBA code:
For Each Cell In Range("D4:D7")
Cell.Offset(0, 1) = (RCHGetTableCell("https://finance.yahoo.com/q?s=" &
Cell.Value & "&ql=1", 1, "Legal Type") = "Exchange Traded Fund")
Next Cell
...and got the results I think you're looking for:
Ticker ETF? SPY TRUE IWM TRUE MMM FALSE VFINX FALSE
On Thu, Oct 30, 2014 at 1:17 PM, brad.reel@... wrote:
> Hi Randy. Stuck on this.
>
> From a process perspective I have a screen running (whole different
> process, through my broker), I get that list and it usually has 10 or 20
> symbols that are new to me. I built a procedure to go check if they are an
> ETF or not. I need to know if they are an ETF or not for other downstream
> processes.
>
> I have a VBA sub that is invoking the following code:
>
> If ETFCheck = True Then
>
> Symbol = Sheets(SheetName).Range("A" & SymbolLoop).Value
>
> ETFResponse = RCHGetTableCell("https://finance.yahoo.com/q?s="
> & Symbol & "&ql=1"
>
> If ETFResponse = "Exchange Traded Fund" Then
>
> Sheets(SheetName)
>
> Else
>
> Sheets(SheetName)
>
> End If
> Basically, if I get a new symbol, I check it and if it results in
> "Exchange Traded Fund" I set a value to TRUE, otherwise FALSE (in the case
> of an equity).
>
> At the end of the day, I am trying to confirm whether or not a list of
> symbols I have is an ETF or stock. The information I am pulling on the web
> page is the most reliable source I have found. So, first, if you are aware
> of some other simpler way I can query on a symbol and get whether or not it
> is an ETF, I am all ears.
>
> The main point is I noticed I was not getting consistent results when
> looking at the output. Symbols I knew were ETF's were coming back as
> equities and vice-versa. So, to troubleshoot, I pulled out the formula and
> built a spreadsheet with 100 items to just make sure I wasn't doing
> something else wrong, etc. When I tried to retrieve for the 100 symbols,
> the result I got back was "Error" for all 100. "Error" is the result I
> would expect for a stock (since this field does not exist on the page for a
> given stock). This is basically the same result I was seeing in my
> procedure output. I am not really getting anything that should be set to
> ETF to be set that way; everything is coming back as equity.
>
> In the spreadsheet, my suspicion is that this might come down to the page
> being cached. I've tried everything I found in your FAQ to address that
> (clear browser, set to get new page every time, run your macro, etc.). and
> I suspect that could be the problem I am experiencing on the spreadsheet.
> Ultimately the question I have that goes with my very long-winded
> explanation is could I have the same caching issue when making an
> individual VBA procedure call for each symbol? And if I am, what good way
> is there for me to work around it?
>
>
Thu Oct 30, 2014 7:13 pm (PDT) . Posted by:
"Brad Reel" reeldeal9090
Your code is a lot cleaner than mine. I'm still very much a brute force
coder; I don't do this for a living, never have, never will. :) I might
try your code and see if I have better success.
Most likely it is a problem in my code and I will go back to assuming that
and digging. I just felt the need to eliminate what I thought might be a
problem. Thanks for the help.
On Thu, Oct 30, 2014 at 7:39 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> You appear to be doing everything I would suggest, so I'm at a loss.
> Unless you are going over the 1000 web page limit DURING the process?
> Because smfForceRecalculation would reset that, but not in the middle of a
> 1000+ web page retrieval process...
>
> In any case, I just tried this VBA code:
>
> For Each Cell In Range("D4:D7")
> Cell.Offset(0, 1) = (RCHGetTableCell("https://finance.yahoo.com/q?s="
> & Cell.Value & "&ql=1", 1, "Legal Type") = "Exchange Traded Fund")
> Next Cell
>
> ...and got the results I think you're looking for:
>
> Ticker ETF? SPY TRUE IWM TRUE MMM FALSE VFINX FALSE
> On Thu, Oct 30, 2014 at 1:17 PM, brad.reel@... wrote:
>
>> Hi Randy. Stuck on this.
>>
>> From a process perspective I have a screen running (whole different
>> process, through my broker), I get that list and it usually has 10 or 20
>> symbols that are new to me. I built a procedure to go check if they are an
>> ETF or not. I need to know if they are an ETF or not for other downstream
>> processes.
>>
>> I have a VBA sub that is invoking the following code:
>>
>> If ETFCheck = True Then
>>
>> Symbol = Sheets(SheetName).Range("A" & SymbolLoop).Value
>>
>> ETFResponse = RCHGetTableCell("https://finance.yahoo.com/q?s="
>> & Symbol & "&ql=1", 1, "Legal Type")
>>
>> If ETFResponse = "Exchange Traded Fund" Then
>>
>> Sheets(SheetName).Range("B" & SymbolLoop).Value = True
>>
>> Else
>>
>> Sheets(SheetName).Range("B" & SymbolLoop).Value = False
>>
>> End If
>> Basically, if I get a new symbol, I check it and if it results in
>> "Exchange Traded Fund" I set a value to TRUE, otherwise FALSE (in the case
>> of an equity).
>>
>> At the end of the day, I am trying to confirm whether or not a list of
>> symbols I have is an ETF or stock. The information I am pulling on the web
>> page is the most reliable source I have found. So, first, if you are aware
>> of some other simpler way I can query on a symbol and get whether or not it
>> is an ETF, I am all ears.
>>
>> The main point is I noticed I was not getting consistent results when
>> looking at the output. Symbols I knew were ETF's were coming back as
>> equities and vice-versa. So, to troubleshoot, I pulled out the formula and
>> built a spreadsheet with 100 items to just make sure I wasn't doing
>> something else wrong, etc. When I tried to retrieve for the 100 symbols,
>> the result I got back was "Error" for all 100. "Error" is the result I
>> would expect for a stock (since this field does not exist on the page for a
>> given stock). This is basically the same result I was seeing in my
>> procedure output. I am not really getting anything that should be set to
>> ETF to be set that way; everything is coming back as equity.
>>
>> In the spreadsheet, my suspicion is that this might come down to the page
>> being cached. I've tried everything I found in your FAQ to address that
>> (clear browser, set to get new page every time, run your macro, etc.). and
>> I suspect that could be the problem I am experiencing on the spreadsheet.
>> Ultimately the question I have that goes with my very long-winded
>> explanation is could I have the same caching issue when making an
>> individual VBA procedure call for each symbol? And if I am, what good way
>> is there for me to work around it?
>>
>>
>
>
coder; I don't do this for a living, never have, never will. :) I might
try your code and see if I have better success.
Most likely it is a problem in my code and I will go back to assuming that
and digging. I just felt the need to eliminate what I thought might be a
problem. Thanks for the help.
On Thu, Oct 30, 2014 at 7:39 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> You appear to be doing everything I would suggest, so I'm at a loss.
> Unless you are going over the 1000 web page limit DURING the process?
> Because smfForceRecalculation would reset that, but not in the middle of a
> 1000+ web page retrieval process...
>
> In any case, I just tried this VBA code:
>
> For Each Cell In Range("D4:D7")
> Cell.Offset(0, 1) = (RCHGetTableCell("https://finance.yahoo.com/q?s="
> & Cell.Value & "&ql=1", 1, "Legal Type") = "Exchange Traded Fund")
> Next Cell
>
> ...and got the results I think you're looking for:
>
> Ticker ETF? SPY TRUE IWM TRUE MMM FALSE VFINX FALSE
> On Thu, Oct 30, 2014 at 1:17 PM, brad.reel@... wrote:
>
>> Hi Randy. Stuck on this.
>>
>> From a process perspective I have a screen running (whole different
>> process, through my broker), I get that list and it usually has 10 or 20
>> symbols that are new to me. I built a procedure to go check if they are an
>> ETF or not. I need to know if they are an ETF or not for other downstream
>> processes.
>>
>> I have a VBA sub that is invoking the following code:
>>
>> If ETFCheck = True Then
>>
>> Symbol = Sheets(SheetName).Range("A" & SymbolLoop).Value
>>
>> ETFResponse = RCHGetTableCell("https://finance.yahoo.com/q?s="
>> & Symbol & "&ql=1"
>>
>> If ETFResponse = "Exchange Traded Fund" Then
>>
>> Sheets(SheetName)
>>
>> Else
>>
>> Sheets(SheetName)
>>
>> End If
>> Basically, if I get a new symbol, I check it and if it results in
>> "Exchange Traded Fund" I set a value to TRUE, otherwise FALSE (in the case
>> of an equity).
>>
>> At the end of the day, I am trying to confirm whether or not a list of
>> symbols I have is an ETF or stock. The information I am pulling on the web
>> page is the most reliable source I have found. So, first, if you are aware
>> of some other simpler way I can query on a symbol and get whether or not it
>> is an ETF, I am all ears.
>>
>> The main point is I noticed I was not getting consistent results when
>> looking at the output. Symbols I knew were ETF's were coming back as
>> equities and vice-versa. So, to troubleshoot, I pulled out the formula and
>> built a spreadsheet with 100 items to just make sure I wasn't doing
>> something else wrong, etc. When I tried to retrieve for the 100 symbols,
>> the result I got back was "Error" for all 100. "Error" is the result I
>> would expect for a stock (since this field does not exist on the page for a
>> given stock). This is basically the same result I was seeing in my
>> procedure output. I am not really getting anything that should be set to
>> ETF to be set that way; everything is coming back as equity.
>>
>> In the spreadsheet, my suspicion is that this might come down to the page
>> being cached. I've tried everything I found in your FAQ to address that
>> (clear browser, set to get new page every time, run your macro, etc.). and
>> I suspect that could be the problem I am experiencing on the spreadsheet.
>> Ultimately the question I have that goes with my very long-winded
>> explanation is could I have the same caching issue when making an
>> individual VBA procedure call for each symbol? And if I am, what good way
>> is there for me to work around it?
>>
>>
>
>
Thu Oct 30, 2014 7:28 pm (PDT) . Posted by:
"Brad Reel" reeldeal9090
OK, now I wish I had not responded so quickly. Right after I hit respond,
I set up the following procedure:
Sub Test()
For Each Cell In Sheets("Test").Range("A1:A50")
Cell.Offset(0, 1) = (RCHGetTableCell("https://finance.yahoo.com/q?s="
& Cell.Value & "&ql=1", 1, "Legal Type") = "Exchange Traded Fund")
Next Cell
End Sub
And I used this list of symbols, which by the way per my prior data were
about evenly split as ETF vs equity:
ABY ACAD ACCO ACGL ACHC ACHN ACI ACIW ACLS ACM ACMP ACN ACOR
ACT ABC ABCB ADZ AEP AERI AGA AGF AGG AIA ALKS AMGN AMLP ANAC
APOG ASEA ASHR ATO AUSE AVB AVNR B BAB BABY BIV BKLN BLV BND
I ran the code, it ran in no time flat, and it returned results that upon
checking seem to look correct.
So, questions:
1. Your code is running way faster than mine. Does running the code
like you describe queue up one big 50 item request (in this example) that
gets sent and returned?
2. I don't completely understand how you got the cell.offset line set up
as a Boolean. Any chance you can elaborate on that? Does VBA understand
that you are just comparing two values and simply assume it is True/False
depending on whether they are equal or not?
Thanks again Randy. I know you hear it all the time, but you really are
awesome!
On Thu, Oct 30, 2014 at 9:13 PM, Brad Reel <brad.reel90@gmail.com> wrote:
> Your code is a lot cleaner than mine. I'm still very much a brute force
> coder; I don't do this for a living, never have, never will. :) I might
> try your code and see if I have better success.
>
> Most likely it is a problem in my code and I will go back to assuming that
> and digging. I just felt the need to eliminate what I thought might be a
> problem. Thanks for the help.
>
>
>
> On Thu, Oct 30, 2014 at 7:39 PM, Randy Harmelink rharmelink@gmail.com
> [smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>>
>>
>> You appear to be doing everything I would suggest, so I'm at a loss.
>> Unless you are going over the 1000 web page limit DURING the process?
>> Because smfForceRecalculation would reset that, but not in the middle of a
>> 1000+ web page retrieval process...
>>
>> In any case, I just tried this VBA code:
>>
>> For Each Cell In Range("D4:D7")
>> Cell.Offset(0, 1) = (RCHGetTableCell("https://finance.yahoo.com/q?s="
>> & Cell.Value & "&ql=1", 1, "Legal Type") = "Exchange Traded Fund")
>> Next Cell
>>
>> ...and got the results I think you're looking for:
>>
>> Ticker ETF? SPY TRUE IWM TRUE MMM FALSE VFINX FALSE
>> On Thu, Oct 30, 2014 at 1:17 PM, brad.reel@... wrote:
>>
>>> Hi Randy. Stuck on this.
>>>
>>> From a process perspective I have a screen running (whole different
>>> process, through my broker), I get that list and it usually has 10 or 20
>>> symbols that are new to me. I built a procedure to go check if they are an
>>> ETF or not. I need to know if they are an ETF or not for other downstream
>>> processes.
>>>
>>> I have a VBA sub that is invoking the following code:
>>>
>>> If ETFCheck = True Then
>>>
>>> Symbol = Sheets(SheetName).Range("A" & SymbolLoop).Value
>>>
>>> ETFResponse = RCHGetTableCell("
>>> https://finance.yahoo.com/q?s=" & Symbol & "&ql=1", 1, "Legal Type")
>>>
>>> If ETFResponse = "Exchange Traded Fund" Then
>>>
>>> Sheets(SheetName).Range("B" & SymbolLoop).Value = True
>>>
>>> Else
>>>
>>> Sheets(SheetName).Range("B" & SymbolLoop).Value = False
>>>
>>> End If
>>> Basically, if I get a new symbol, I check it and if it results in
>>> "Exchange Traded Fund" I set a value to TRUE, otherwise FALSE (in the case
>>> of an equity).
>>>
>>> At the end of the day, I am trying to confirm whether or not a list of
>>> symbols I have is an ETF or stock. The information I am pulling on the web
>>> page is the most reliable source I have found. So, first, if you are aware
>>> of some other simpler way I can query on a symbol and get whether or not it
>>> is an ETF, I am all ears.
>>>
>>> The main point is I noticed I was not getting consistent results when
>>> looking at the output. Symbols I knew were ETF's were coming back as
>>> equities and vice-versa. So, to troubleshoot, I pulled out the formula and
>>> built a spreadsheet with 100 items to just make sure I wasn't doing
>>> something else wrong, etc. When I tried to retrieve for the 100 symbols,
>>> the result I got back was "Error" for all 100. "Error" is the result I
>>> would expect for a stock (since this field does not exist on the page for a
>>> given stock). This is basically the same result I was seeing in my
>>> procedure output. I am not really getting anything that should be set to
>>> ETF to be set that way; everything is coming back as equity.
>>>
>>> In the spreadsheet, my suspicion is that this might come down to the
>>> page being cached. I've tried everything I found in your FAQ to address
>>> that (clear browser, set to get new page every time, run your macro, etc.).
>>> and I suspect that could be the problem I am experiencing on the
>>> spreadsheet. Ultimately the question I have that goes with my very
>>> long-winded explanation is could I have the same caching issue when making
>>> an individual VBA procedure call for each symbol? And if I am, what good
>>> way is there for me to work around it?
>>>
>>>
>>
>>
>
>
I set up the following procedure:
Sub Test()
For Each Cell In Sheets("Test").Range("A1:A50")
Cell.Offset(0, 1) = (RCHGetTableCell("https://finance.yahoo.com/q?s="
& Cell.Value & "&ql=1", 1, "Legal Type") = "Exchange Traded Fund")
Next Cell
End Sub
And I used this list of symbols, which by the way per my prior data were
about evenly split as ETF vs equity:
ABY ACAD ACCO ACGL ACHC ACHN ACI ACIW ACLS ACM ACMP ACN ACOR
ACT ABC ABCB ADZ AEP AERI AGA AGF AGG AIA ALKS AMGN AMLP ANAC
APOG ASEA ASHR ATO AUSE AVB AVNR B BAB BABY BIV BKLN BLV BND
I ran the code, it ran in no time flat, and it returned results that upon
checking seem to look correct.
So, questions:
1. Your code is running way faster than mine. Does running the code
like you describe queue up one big 50 item request (in this example) that
gets sent and returned?
2. I don't completely understand how you got the cell.offset line set up
as a Boolean. Any chance you can elaborate on that? Does VBA understand
that you are just comparing two values and simply assume it is True/False
depending on whether they are equal or not?
Thanks again Randy. I know you hear it all the time, but you really are
awesome!
On Thu, Oct 30, 2014 at 9:13 PM, Brad Reel <brad.reel90@gmail.com> wrote:
> Your code is a lot cleaner than mine. I'm still very much a brute force
> coder; I don't do this for a living, never have, never will. :) I might
> try your code and see if I have better success.
>
> Most likely it is a problem in my code and I will go back to assuming that
> and digging. I just felt the need to eliminate what I thought might be a
> problem. Thanks for the help.
>
>
>
> On Thu, Oct 30, 2014 at 7:39 PM, Randy Harmelink rharmelink@gmail.com
> [smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>>
>>
>> You appear to be doing everything I would suggest, so I'm at a loss.
>> Unless you are going over the 1000 web page limit DURING the process?
>> Because smfForceRecalculation would reset that, but not in the middle of a
>> 1000+ web page retrieval process...
>>
>> In any case, I just tried this VBA code:
>>
>> For Each Cell In Range("D4:D7")
>> Cell.Offset(0, 1) = (RCHGetTableCell("https://finance.yahoo.com/q?s="
>> & Cell.Value & "&ql=1", 1, "Legal Type") = "Exchange Traded Fund")
>> Next Cell
>>
>> ...and got the results I think you're looking for:
>>
>> Ticker ETF? SPY TRUE IWM TRUE MMM FALSE VFINX FALSE
>> On Thu, Oct 30, 2014 at 1:17 PM, brad.reel@... wrote:
>>
>>> Hi Randy. Stuck on this.
>>>
>>> From a process perspective I have a screen running (whole different
>>> process, through my broker), I get that list and it usually has 10 or 20
>>> symbols that are new to me. I built a procedure to go check if they are an
>>> ETF or not. I need to know if they are an ETF or not for other downstream
>>> processes.
>>>
>>> I have a VBA sub that is invoking the following code:
>>>
>>> If ETFCheck = True Then
>>>
>>> Symbol = Sheets(SheetName).Range("A" & SymbolLoop).Value
>>>
>>> ETFResponse = RCHGetTableCell("
>>> https://finance.yahoo.com/q?s=" & Symbol & "&ql=1"
>>>
>>> If ETFResponse = "Exchange Traded Fund" Then
>>>
>>> Sheets(SheetName)
>>>
>>> Else
>>>
>>> Sheets(SheetName)
>>>
>>> End If
>>> Basically, if I get a new symbol, I check it and if it results in
>>> "Exchange Traded Fund" I set a value to TRUE, otherwise FALSE (in the case
>>> of an equity).
>>>
>>> At the end of the day, I am trying to confirm whether or not a list of
>>> symbols I have is an ETF or stock. The information I am pulling on the web
>>> page is the most reliable source I have found. So, first, if you are aware
>>> of some other simpler way I can query on a symbol and get whether or not it
>>> is an ETF, I am all ears.
>>>
>>> The main point is I noticed I was not getting consistent results when
>>> looking at the output. Symbols I knew were ETF's were coming back as
>>> equities and vice-versa. So, to troubleshoot, I pulled out the formula and
>>> built a spreadsheet with 100 items to just make sure I wasn't doing
>>> something else wrong, etc. When I tried to retrieve for the 100 symbols,
>>> the result I got back was "Error" for all 100. "Error" is the result I
>>> would expect for a stock (since this field does not exist on the page for a
>>> given stock). This is basically the same result I was seeing in my
>>> procedure output. I am not really getting anything that should be set to
>>> ETF to be set that way; everything is coming back as equity.
>>>
>>> In the spreadsheet, my suspicion is that this might come down to the
>>> page being cached. I've tried everything I found in your FAQ to address
>>> that (clear browser, set to get new page every time, run your macro, etc.).
>>> and I suspect that could be the problem I am experiencing on the
>>> spreadsheet. Ultimately the question I have that goes with my very
>>> long-winded explanation is could I have the same caching issue when making
>>> an individual VBA procedure call for each symbol? And if I am, what good
>>> way is there for me to work around it?
>>>
>>>
>>
>>
>
>
Thu Oct 30, 2014 7:49 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
On Thu, Oct 30, 2014 at 7:27 PM, Brad Reel brad.reel90@... wrote:
>
> So, questions:
>
> 1. Your code is running way faster than mine. Does running the code
> like you describe queue up one big 50 item request (in this example) that
> gets sent and returned?
>
> My assumption would be that you already had retrieved the 50 pages in
previous processing. So, just having to extract data from the already
retrieved and saved web pages would run very quickly.
If you ran smfForceRecalculation to purge the saved web pages, then ran the
VBA code again, I would bet it would be a tad slower as it retrieved each
Yahoo web page. In my request logs, Yahoo averages about 0.75 seconds per
web page (ranging from 0.2 to 15), so retrieving 50 web pages would
typically add about 37 seconds to the process, but could go longer if you
were unlucky enough to get one of the longer response times.
> 1. I don't completely understand how you got the cell.offset line set
> up as a Boolean. Any chance you can elaborate on that? Does VBA
> understand that you are just comparing two values and simply assume it is
> True/False depending on whether they are equal or not?
>
> You are correct -- VBA understands it is just comparing two values, so the
result has to be a Boolean True/False result. It's more or less the same
thing that would happen in an IF statement, as in:
IF (X=1) then...
That results in a Boolean True or False result, based on the comparison of
the two values.
>
> So, questions:
>
> 1. Your code is running way faster than mine. Does running the code
> like you describe queue up one big 50 item request (in this example) that
> gets sent and returned?
>
> My assumption would be that you already had retrieved the 50 pages in
previous processing. So, just having to extract data from the already
retrieved and saved web pages would run very quickly.
If you ran smfForceRecalculati
VBA code again, I would bet it would be a tad slower as it retrieved each
Yahoo web page. In my request logs, Yahoo averages about 0.75 seconds per
web page (ranging from 0.2 to 15), so retrieving 50 web pages would
typically add about 37 seconds to the process, but could go longer if you
were unlucky enough to get one of the longer response times.
> 1. I don't completely understand how you got the cell.offset line set
> up as a Boolean. Any chance you can elaborate on that? Does VBA
> understand that you are just comparing two values and simply assume it is
> True/False depending on whether they are equal or not?
>
> You are correct -- VBA understands it is just comparing two values, so the
result has to be a Boolean True/False result. It's more or less the same
thing that would happen in an IF statement, as in:
IF (X=1) then...
That results in a Boolean True or False result, based on the comparison of
the two values.
Thu Oct 30, 2014 6:07 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Hmmm. It looks to me like IE right now is bringing up the old format of the
option web pages. Both IE and FireFox last week had the new format of the
option web pages. FireFox is currently displaying the new format, while IE
is displaying the old format.
So, hopefully a temporary glitch on Yahoo's side?
On Thu, Oct 30, 2014 at 5:34 PM, Gary.Hartling@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I'm getting lots of option quote errors. The 5 following produce "Error":
>
> =smfGetOptionQuotes("EBAY 11/22 2014 52.5 C","v")
> =smfGetOptionQuotes("EBAY 11/22 2014 52.5 C","i")
> =smfGetOptionQuotes("EBAY 11/22 2014 52.5 C","l")
> =smfGetOptionQuotes("EBAY 11/22 2014 52.5 C","b")
> =smfGetOptionQuotes("EBAY 11/22 2014 52.5 C","a")
>
> Some quotes work. For example:
> =smfGetOptionQuotes("MMM 10/31 2014 $150 Call","b") works.
> =smfGetOptionQuotes("MMM 10/31 2014 $150 Call","a") works.
> =smfGetOptionQuotes("MMM 10/31 2014 $150 Call","v") works.
>
> But:
> =smfGetOptionQuotes("MMM 10/31 2014 $150 Call","i") Error
> =smfGetOptionQuotes("MMM 10/31 2014 $150 Call","l") Error
>
>
option web pages. Both IE and FireFox last week had the new format of the
option web pages. FireFox is currently displaying the new format, while IE
is displaying the old format.
So, hopefully a temporary glitch on Yahoo's side?
On Thu, Oct 30, 2014 at 5:34 PM, Gary.Hartling@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I'm getting lots of option quote errors. The 5 following produce "Error"
>
> =smfGetOptionQuotes
> =smfGetOptionQuotes
> =smfGetOptionQuotes
> =smfGetOptionQuotes
> =smfGetOptionQuotes
>
> Some quotes work. For example:
> =smfGetOptionQuotes
> =smfGetOptionQuotes
> =smfGetOptionQuotes
>
> But:
> =smfGetOptionQuotes
> =smfGetOptionQuotes
>
>
Fri Oct 31, 2014 6:40 am (PDT) . Posted by:
bill_d_tx
Hi Randy - I am using the latest SMF files (from 10/23)
I use a table/array with this value in each cell {=smfGetOptionQuotes(AB6:AB120,AC4:AG4,$AC$2)}
Column AB has option symbol info example WFM 11/22 2014 $36 Put
Cells AC4:AG4 contain u l b a v to pull Underlying,Last,Bid,Ask,Volume
Cell AC2 contains the 'switch' for the source i.e. 1=Yahoo, 6=Google,2=MSN
I generally have it set to 6, for some reason 1 has an impact on formatting that I haven't figured out yet. In any event all has been working until yesterday (10/30/14)
The underlying price is pulling for all rows and the Last and Volume are pulling for first row, then all cells after 1st row are errors (except underlying)
Seems like a formatting issue in data but I don't know where else to look.
As always, thanks for your generous help.
I use a table/array with this value in each cell {=smfGetOptionQuote
Column AB has option symbol info example WFM 11/22 2014 $36 Put
Cells AC4:AG4 contain u l b a v to pull Underlying,Last,
Cell AC2 contains the 'switch&#
I generally have it set to 6, for some reason 1 has an impact on formatting that I haven't figured out yet. In any event all has been working until yesterday (10/30/14)
The underlying price is pulling for all rows and the Last and Volume are pulling for first row, then all cells after 1st row are errors (except underlying)
Seems like a formatting issue in data but I don't know where else to look.
As always, thanks for your generous help.
Fri Oct 31, 2014 7:48 am (PDT) . Posted by:
billtrig
My yahoo quotes also broke as of 2 days ago -- it's still broken today (I pull data from within Excel 2013). So I added some columns to get quotes from google, and that seems to work except for options of ETFs ... they all return blanks for all 3 prices (last, bid, and ask), but I do get a valid return value for z (Actual Options Symbol), so it is interpreting something correctly.
So now I'm stuck ... almost nothing from yahoo and partial results from google.Oh, BTW, I did get one pair of returns from yahoo (out of hundreds of calls), but the few that did get returned did not match the google b/a prices. Just now TCS141122C17.5 returned {0,1.20} from yahoo and {1.20,1.40} from google.
FYI, I updated the smf file last week. Any thoughts on how to override yahoo's reversal?
billtrig@yahoo.com
So now I'm stuck ... almost nothing from yahoo and partial results from google.Oh, BTW, I did get one pair of returns from yahoo (out of hundreds of calls), but the few that did get returned did not match the google b/a prices. Just now TCS141122C17.5 returned {0,1.20} from yahoo and {1.20,1.40} from google.
FYI, I updated the smf file last week. Any thoughts on how to override yahoo's reversal?
billtrig@yahoo.com
Fri Oct 31, 2014 8:31 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I just restored the old beta version of the add-in to the website, although
it's not listed on the web page:
RCH_Stock_Market_Functions-2.1.2014.05.25.zip
<http://ogres-crypt.com/SMF/Works-In-Progress/RCH_Stock_Market_Functions-2.1.2014.05.25.zip>
...so that would get you back to Yahoo processing before the changes. I'm
not sure why Yahoo went back, or if they'll reinstate the changes anytime
soon. The changes are still showing up in FireFox and Chrome when I go
there. My version of IE, 7, won't even display the options web page.
They have to be doing something on the server side to be sending out
different web page source code for different browsers...
On Fri, Oct 31, 2014 at 7:48 AM, billtrig@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> My yahoo quotes also broke as of 2 days ago -- it's still broken today (I
> pull data from within Excel 2013). So I added some columns to get quotes
> from google, and that seems to work except for options of ETFs ... they all
> return blanks for all 3 prices (last, bid, and ask), but I do get a valid
> return value for z (Actual Options Symbol), so it is interpreting something
> correctly.
>
> So now I'm stuck ... almost nothing from yahoo and partial results from
> google.Oh, BTW, I did get one pair of returns from yahoo (out of hundreds
> of calls), but the few that did get returned did not match the google b/a
> prices. Just now TCS141122C17.5 returned {0,1.20} from yahoo and
> {1.20,1.40} from google.
>
> FYI, I updated the smf file last week. Any thoughts on how to override
> yahoo's reversal?
>
it's not listed on the web page:
RCH_Stock_Market_Functions-2.1.2014.05.25.zip
<http://ogres-crypt.com/SMF/Works-In-Progress/RCH_Stock_Market_Functions-2.1.2014.05.25.zip>
...so that would get you back to Yahoo processing before the changes. I'm
not sure why Yahoo went back, or if they'll reinstate the changes anytime
soon. The changes are still showing up in FireFox and Chrome when I go
there. My version of IE, 7, won't even display the options web page.
They have to be doing something on the server side to be sending out
different web page source code for different browsers...
On Fri, Oct 31, 2014 at 7:48 AM, billtrig@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> My yahoo quotes also broke as of 2 days ago -- it's still broken today (I
> pull data from within Excel 2013). So I added some columns to get quotes
> from google, and that seems to work except for options of ETFs ... they all
> return blanks for all 3 prices (last, bid, and ask), but I do get a valid
> return value for z (Actual Options Symbol), so it is interpreting something
> correctly.
>
> So now I'm stuck ... almost nothing from yahoo and partial results from
> google.Oh, BTW, I did get one pair of returns from yahoo (out of hundreds
> of calls), but the few that did get returned did not match the google b/a
> prices. Just now TCS141122C17.
> {1.20,1.40} from google.
>
> FYI, I updated the smf file last week. Any thoughts on how to override
> yahoo's reversal?
>
Fri Oct 31, 2014 8:38 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
All Yahoo web pages have reverted back to the old web page format when
serving up web pages for IE (or the add-in). FireFox and Chrome browsers
are still showing the new format for me.
MSN options are no more, because they restructured all their web pages. I
also get different source code from them depending on the browser asking
for the web page.
Google often misses a lot of contracts, especially on ETFs, as you noticed.
Even though they are much slower, I usually use OptionsXPress as my data
source. People are actually using their data for trading, so I wouldn't
expect missing contracts and missing expiration dates.
I'm concened about you using values of 1, 2, and 6 as "switches". The
numeric values are only valid as prefixes to use on data codes when you are
retrieving data from multiple data sources in one array-entered range. You
wouldn't use those numeric values as your "source" parameter in the
function.
On Fri, Oct 31, 2014 at 6:40 AM, wmd0914@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Hi Randy - I am using the latest SMF files (from 10/23)
>
> I use a table/array with this value in each cell
> {=smfGetOptionQuotes(AB6:AB120,AC4:AG4,$AC$2)}
>
> Column AB has option symbol info example WFM 11/22 2014 $36 Put
> Cells AC4:AG4 contain u l b a v to pull
> Underlying,Last,Bid,Ask,Volume
> Cell AC2 contains the 'switch' for the source i.e. 1=Yahoo, 6=Google,2=MSN
>
> I generally have it set to 6, for some reason 1 has an impact on
> formatting that I haven't figured out yet. In any event all has been
> working until yesterday (10/30/14)
>
> The underlying price is pulling for all rows and the Last and Volume are
> pulling for first row, then all cells after 1st row are errors (except
> underlying)
>
> Seems like a formatting issue in data but I don't know where else to look.
>
> As always, thanks for your generous help.
>
serving up web pages for IE (or the add-in). FireFox and Chrome browsers
are still showing the new format for me.
MSN options are no more, because they restructured all their web pages. I
also get different source code from them depending on the browser asking
for the web page.
Google often misses a lot of contracts, especially on ETFs, as you noticed.
Even though they are much slower, I usually use OptionsXPress as my data
source. People are actually using their data for trading, so I wouldn't
expect missing contracts and missing expiration dates.
I'm concened about you using values of 1, 2, and 6 as "switches". The
numeric values are only valid as prefixes to use on data codes when you are
retrieving data from multiple data sources in one array-entered range. You
wouldn't use those numeric values as your "source" parameter in the
function.
On Fri, Oct 31, 2014 at 6:40 AM, wmd0914@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Hi Randy - I am using the latest SMF files (from 10/23)
>
> I use a table/array with this value in each cell
> {=smfGetOptionQuote
>
> Column AB has option symbol info example WFM 11/22 2014 $36 Put
> Cells AC4:AG4 contain u l b a v to pull
> Underlying,Last,
> Cell AC2 contains the 'switch&#
>
> I generally have it set to 6, for some reason 1 has an impact on
> formatting that I haven't figured out yet. In any event all has been
> working until yesterday (10/30/14)
>
> The underlying price is pulling for all rows and the Last and Volume are
> pulling for first row, then all cells after 1st row are errors (except
> underlying)
>
> Seems like a formatting issue in data but I don't know where else to look.
>
> As always, thanks for your generous help.
>
Fri Oct 31, 2014 8:58 am (PDT) . Posted by:
bill_d_tx
Thanks, my poor choice of words. I am only looking for reliable price data for positions I hold. I will reread the documentation. Clearly, I didn't understand it the first time.
Fri Oct 31, 2014 10:07 am (PDT) . Posted by:
bill_d_tx
I restored the older version and everything is working fine.
Thanks!
Thanks!
Fri Oct 31, 2014 11:16 am (PDT) . Posted by:
ssztaba
Is there a link back to the "Older" options smf folder which appears to be working again ??
Where do we find the old smf versions ??
Thanks
Stan
Where do we find the old smf versions ??
Thanks
Stan
Thu Oct 30, 2014 8:17 pm (PDT) . Posted by:
tzewei_79
Hi Randy,
Thanks it works.
But I noticed yahoo doesn't have all the data for some stocks....I have a ticker NYCB...the entire financial statement is empty..
I tried MSN again, and it seems that the link changed a little today but I can't get it to work like yahoo. Not sure if it was because the ticker prefix is a "-" and not "=". The formula below will show an error. Can this hyperlink be used this way?
=RCHGetTableCell("http://www.msn.com/en-us/money/stockdetails/financials/fi-"&A12,1
,"Operating Income")
Thanks it works.
But I noticed yahoo doesn't have all the data for some stocks....I have a ticker NYCB...the entire financial statement is empty..
I tried MSN again, and it seems that the link changed a little today but I can't get it to work like yahoo. Not sure if it was because the ticker prefix is a "-" and not "=". The formula below will show an error. Can this hyperlink be used this way?
=RCHGetTableCell("http://www.msn.com/en-us/money/stockdetails/financials/fi-"&A12,1
,"Operating Income"
Thu Oct 30, 2014 8:49 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The MSN web pages I'm looking at don't have the data in tables, but they
use "li" and "p" tags to simulate a table. So, this works for a number of
ticker symbols:
=smfConvertData(smfGetTagContent("
http://www.msn.com/en-us/money/stockdetails/financials/fi-"&B7,"p",1,"Operating
Income"))
However, when the add-in retrieves the source code of the NYCB web page,
there is no financial statements data on the web page. FireFox displays it
however, which is strange.
I'm seeing this more and more often lately -- FireFox displays one thing,
but the add-in retrieves something different. I'm not sure what the cause
is. I don't use IE as a browser, and don't have the current version of IE,
so I can't really say what happens there. But it's still strange, as I
though the XMLHTTP protocol I use to retrieve source code of a web page is
the same engine that IE uses. Perhaps there is some server-side processing
going on to identify the type of browser so that different sets of source
code can be delivered? I don't know...
On Thu, Oct 30, 2014 at 8:17 PM, tzewei_79@... wrote:
>
> But I noticed yahoo doesn't have all the data for some stocks....I have a
> ticker NYCB...the entire financial statement is empty..
>
> I tried MSN again, and it seems that the link changed a little today but I
> can't get it to work like yahoo. Not sure if it was because the ticker
> prefix is a "-" and not "=". The formula below will show an error. Can this
> hyperlink be used this way?
>
> =RCHGetTableCell("
> http://www.msn.com/en-us/money/stockdetails/financials/fi-"&A12,1
> ,"Operating Income")
>
use "li" and "p" tags to simulate a table. So, this works for a number of
ticker symbols:
=smfConvertData(smfGetTagContent("
http://www.msn.com/en-us/money/stockdetails/financials/fi-"&B7,"p",1,"Operating
Income"))
However, when the add-in retrieves the source code of the NYCB web page,
there is no financial statements data on the web page. FireFox displays it
however, which is strange.
I'm seeing this more and more often lately -- FireFox displays one thing,
but the add-in retrieves something different. I'm not sure what the cause
is. I don't use IE as a browser, and don't have the current version of IE,
so I can't really say what happens there. But it's still strange, as I
though the XMLHTTP protocol I use to retrieve source code of a web page is
the same engine that IE uses. Perhaps there is some server-side processing
going on to identify the type of browser so that different sets of source
code can be delivered? I don't know...
On Thu, Oct 30, 2014 at 8:17 PM, tzewei_79@... wrote:
>
> But I noticed yahoo doesn't have all the data for some stocks....I have a
> ticker NYCB...the entire financial statement is empty..
>
> I tried MSN again, and it seems that the link changed a little today but I
> can't get it to work like yahoo. Not sure if it was because the ticker
> prefix is a "-" and not "=". The formula below will show an error. Can this
> hyperlink be used this way?
>
> =RCHGetTableCell("
> http://www.msn.com/en-us/money/stockdetails/financials/fi-"&A12,1
> ,"Operating Income"
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar