2 Messages
Digest #3149
Messages
Wed Aug 13, 2014 3:55 pm (PDT) . Posted by:
digsupply
Hi RandyI am using a macro (VBA) to execute the formulas below to obtain the Next Earnings date, as well as other data, for approximately 400 tickers..FormulaR1C1 = "=DATEVALUE(RCHGetTableCell(""http://www.bloomberg.com/quote/"" & RC62 & "":US/key-statistics"",0,""Next Earnings Announcement"",1))".FormulaR1C1 = "=RCHGetTableCell(""http://finance.yahoo.com/q?s="" & RC62 & ""&ql=1"",1,""Next Earnings Date:"")".FormulaR1C1 = "=RCHGetTableCell(""http://www.zacks.com/stock/research/""& RC62 &""/company-reports"",1,""Next EPS Date"")"The problem is the process is too slow, even when I use the formula "RCHGetElementNumber()"My question is, is there any way to speed up the process? Could I use some FormulaArray like RCHGetYahooQuotes?Many thanks
Wed Aug 13, 2014 4:36 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The way you're doing it, you need to access 1200 web pages to get your
data. So that will be slow. In fact, it should error out after 1000 web
pages, since that's the number of web pages the add-in can store in order
to extract data from them.
The only reason RCHGetYahooQuotes() can be array-entered is because the
parameters there are passed to Yahoo and Yahoo provides all of the data on
a single file -- in one Internet access.
The FinViz screener and Zacks screener both have next earnings date on
them, You could export their entire databases, then do VLOOKUP() functions
to find the tickers you want. FinViz can be automated with the
smfGetCSVFile() function -- see the "Tips and FAQs" web page for more info.
Why are you placing formulas into the cells? As long as you're running the
macro, just place values. Then you don't have to worry about things
recalculating.
And, BTW, there's no speed-up using RCHGetElementNumber(). It's basically
just a stored version of another function. Using RCHGetElementNumber()
would actually be slightly slower (a nanosecond or so) because it has to
look up what the formula is for that element number.
On Wed, Aug 13, 2014 at 3:55 PM, picsound@... wrote:
>
> Hi RandyI am using a macro (VBA) to execute the formulas below to obtain
> the Next Earnings date, as well as other data, for approximately 400
> tickers..FormulaR1C1 = "=DATEVALUE(RCHGetTableCell(""
> http://www.bloomberg.com/quote/"" & RC62 &
> "":US/key-statistics"",0,""Next Earnings Announcement"",1))".FormulaR1C1 =
> "=RCHGetTableCell(""http://finance.yahoo.com/q?s="" & RC62 &
> ""&ql=1"",1,""Next Earnings Date:"")".FormulaR1C1 = "=RCHGetTableCell(""
> http://www.zacks.com/stock/research/""& RC62
> &""/company-reports"",1,""Next EPS Date"")"The problem is the process is
> too slow, even when I use the formula "RCHGetElementNumber()"My question
> is, is there any way to speed up the process? Could I use some FormulaArray
> like RCHGetYahooQuotes?Many thanks
>
data. So that will be slow. In fact, it should error out after 1000 web
pages, since that's the number of web pages the add-in can store in order
to extract data from them.
The only reason RCHGetYahooQuotes() can be array-entered is because the
parameters there are passed to Yahoo and Yahoo provides all of the data on
a single file -- in one Internet access.
The FinViz screener and Zacks screener both have next earnings date on
them, You could export their entire databases, then do VLOOKUP() functions
to find the tickers you want. FinViz can be automated with the
smfGetCSVFile() function -- see the "Tips and FAQs" web page for more info.
Why are you placing formulas into the cells? As long as you're running the
macro, just place values. Then you don't have to worry about things
recalculating.
And, BTW, there's no speed-up using RCHGetElementNumber(). It's basically
just a stored version of another function. Using RCHGetElementNumber()
would actually be slightly slower (a nanosecond or so) because it has to
look up what the formula is for that element number.
On Wed, Aug 13, 2014 at 3:55 PM, picsound@... wrote:
>
> Hi RandyI am using a macro (VBA) to execute the formulas below to obtain
> the Next Earnings date, as well as other data, for approximately 400
> tickers..FormulaR1C1 = "=DATEVALUE(RCHGetTableCell(""
> http://www.bloomberg.com/quote/"" & RC62 &
> "":US/key-statistics"",0,""Next Earnings Announcement"",1))".FormulaR1C1 =
> "=RCHGetTableCell(""http://finance.yahoo.com/q?s="" & RC62 &
> ""&ql=1"",1,""Next Earnings Date:"")".FormulaR1C1 = "=RCHGetTableCell(""
> http://www.zacks.com/stock/research/""
> &""
> too slow, even when I use the formula "RCHGetElement
> is, is there any way to speed up the process? Could I use some FormulaArray
> like RCHGetYahooQuotes?
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar