Messages

1a

Thu Oct 30, 2014 5:39 pm (PDT) . Posted by:

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...

> 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 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? >> >> > >

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!

> 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? >>> >>> >> >> > >

1d

Thu Oct 30, 2014 7:49 pm (PDT) . Posted by:

> > 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.

2a

Thu Oct 30, 2014 6:07 pm (PDT) . Posted by:

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.

2c

Fri Oct 31, 2014 7:48 am (PDT) . Posted by:

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?

...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...

> > 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? >

2e

Fri Oct 31, 2014 8:38 am (PDT) . Posted by:

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.

> > 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 'switch39; 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. >

3a

Thu Oct 30, 2014 8:17 pm (PDT) . Posted by:

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?

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") >