9 New Messages
Digest #2957
Messages
Wed Feb 5, 2014 12:53 pm (PST) . Posted by:
5626ab246680da11c468b726d6775461
Thanks Mike and Randy. This now works. When I have the fourway key I look up various financial statement numbers as you can see from the spreadsheet. Unfortunately there seems to be a mechanism on the website which blocks "too many requests" as I hit the website for about 1000 companies. Is there a way to delay the HTTP request by a few milli seconds each time?
Wed Feb 5, 2014 1:10 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
It's the add-in that has the 1000-web page limit. That's because the add-in
stores the web pages in VBA before extracting the data from the web page.
The add-in is intended for ad hoc data retrieval, not for building
databases. I really don't want people abusing the free data services. It
would be easy enough for them to write their web pages in a way that would
block the add-in from being able to access the data.
On Wed, Feb 5, 2014 at 1:53 PM, <stefan.schmidt296@gmail.com> wrote:
> Thanks Mike and Randy. This now works. When I have the fourway key I
> look up various financial statement numbers as you can see from the
> spreadsheet. Unfortunately there seems to be a mechanism on the website
> which blocks "too many requests" as I hit the website for about 1000
> companies. Is there a way to delay the HTTP request by a few milli seconds
> each time?
>
stores the web pages in VBA before extracting the data from the web page.
The add-in is intended for ad hoc data retrieval, not for building
databases. I really don't want people abusing the free data services. It
would be easy enough for them to write their web pages in a way that would
block the add-in from being able to access the data.
On Wed, Feb 5, 2014 at 1:53 PM, <stefan.schmidt296@
> Thanks Mike and Randy. This now works. When I have the fourway key I
> look up various financial statement numbers as you can see from the
> spreadsheet. Unfortunately there seems to be a mechanism on the website
> which blocks "too many requests" as I hit the website for about 1000
> companies. Is there a way to delay the HTTP request by a few milli seconds
> each time?
>
Wed Feb 5, 2014 1:11 pm (PST) . Posted by:
5626ab246680da11c468b726d6775461
Btw one problem with the query is if the symbol is not unique like for instance:
http://www.londonstockexchange.com/exchange/searchengine/search.html?q=AAA
... then it returns just an empty string instead of the fourwaykey which should be the one from first row...
http://www.londonst
... then it returns just an empty string instead of the fourwaykey which should be the one from first row...
Wed Feb 5, 2014 1:17 pm (PST) . Posted by:
5626ab246680da11c468b726d6775461
I see that makes sense. I am doing this to sceen for a few companies out of a larger universe but I see your point.
Wed Feb 5, 2014 1:19 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I usually suggest as much front-end screening be done first, using the
on-line screeners. Then use the add-in to gather more in-depth information
on the smaller subset of remaining companies...
On Wed, Feb 5, 2014 at 2:17 PM, <stefan.schmidt296@gmail.com> wrote:
> I see that makes sense. I am doing this to sceen for a few companies out
> of a larger universe but I see your point.
>
on-line screeners. Then use the add-in to gather more in-depth information
on the smaller subset of remaining companies...
On Wed, Feb 5, 2014 at 2:17 PM, <stefan.schmidt296@
> I see that makes sense. I am doing this to sceen for a few companies out
> of a larger universe but I see your point.
>
Wed Feb 5, 2014 1:20 pm (PST) . Posted by:
mikemcq802
Yep, this should fix it:
=smfGetTagContent("http://www.londonstockexchange.com/exchange/searchengine/search.html?lang=en&x=0&y=0&q=AAA","td",1,"Total Results:",">Symbol","AAA")
=smfGetTagContent(
Wed Feb 5, 2014 2:57 pm (PST) . Posted by:
woodburydance
I have a test spreadsheet with 11 stocks in it. Each line looks up the stock name, current price, and the yield. These 33 lookups are taking about 5 minutes to complete. My actual spreadsheet will have 85 stocks in it. Is there a better way to use the function so that it responds quickly? It used to run almost instantaneously on this test spreadsheet.
Bruce
Bruce
Attachments with this message:
1 of 1 File(s)
Wed Feb 5, 2014 3:35 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
It only takes about 7 or 8 seconds here...
And you actually have up to 44 lookups. Your "Yield" column will call the
function twice if the company doesn't return "N/A" for yield. If you have
EXCEL 2007 or later, you'd be better off with:
=IFERROR(RCHGetYahooQuotes(B3, "y")/100,0)
If I change your three columns to array-entered functions, it takes less
than a second. Although you could retrieve all three columns with one
array-entered function -- it can get data on up to 200 ticker symbols.
On Wed, Feb 5, 2014 at 3:57 PM, <bameyer@optonline.net> wrote:
> I have a test spreadsheet with 11 stocks in it. Each line looks up the
> stock name, current price, and the yield. These 33 lookups are taking
> about 5 minutes to complete. My actual spreadsheet will have 85 stocks in
> it. Is there a better way to use the function so that it responds
> quickly? It used to run almost instantaneously on this test spreadsheet.
>
And you actually have up to 44 lookups. Your "Yield" column will call the
function twice if the company doesn't return "N/A" for yield. If you have
EXCEL 2007 or later, you'd be better off with:
=IFERROR(RCHGetYaho
If I change your three columns to array-entered functions, it takes less
than a second. Although you could retrieve all three columns with one
array-entered function -- it can get data on up to 200 ticker symbols.
On Wed, Feb 5, 2014 at 3:57 PM, <bameyer@optonline.
> I have a test spreadsheet with 11 stocks in it. Each line looks up the
> stock name, current price, and the yield. These 33 lookups are taking
> about 5 minutes to complete. My actual spreadsheet will have 85 stocks in
> it. Is there a better way to use the function so that it responds
> quickly? It used to run almost instantaneously on this test spreadsheet.
>
Wed Feb 5, 2014 10:58 pm (PST) . Posted by:
woodburydance
Yes, now it is after midnight and the data is coming up faster but it can take a long time. I did set up the array in the test workbook for the Name and Current Price. I couldn't get the array to work for the Yield. It is much faster. I do sell stocks and buy other ones from time to time and I can't just delete the lines of the deleted stocks and insert the new ones into the array. Do I just have to break the array and set it up again each time I make a change? I am running Excel 2003 so the other formula is not an option.
Tidak ada komentar:
Posting Komentar