13 Messages
Digest #3581
Messages
Sun Dec 13, 2015 2:58 am (PST) . Posted by:
mark.boogaers
Hi,
I know this function is still in the beta stage, but trying to use this anyway and maybe my experience is some use for improving the feature. I want to do a little back testing on some Asian (Hong Kong and Malaysia) data and like to use Gurufocus data.
I have made a spreadsheet with roughly 400 companies in it and am trying to import approx 20 data points per company. The RCHGetGurufocusItem command seems to function fine.
If I try to copy the RCHGetGurufocusItem command to all the 400 lines x 20 cells Excel will stop functioning once I calculate manually.
I now tried making a macro that copies 20 cells RCHGetGuruFocusItem command to each company line 1 by 1, get the data, copy it to elsewhere on the sheet as 'value only' then move on to the next company. The process starts fine, but seems to slow down gradually and totally stops after a while. So far I gave up after a couple of hours waiting and restarted Excel to try something different.
Any idea whether this is:
- Something related to Excel and memory filling up or something like that
- Gurufocus website sensing large access and slowing down the process of sending the requested data
- Something related to the RCHGetGuruFocusItem command
Thanks
Mark Boogaers
I am using:
Windows 10
Excel 2013
Gurufocus premium member for USA/Europe/Asia
I know this function is still in the beta stage, but trying to use this anyway and maybe my experience is some use for improving the feature. I want to do a little back testing on some Asian (Hong Kong and Malaysia) data and like to use Gurufocus data.
I have made a spreadsheet with roughly 400 companies in it and am trying to import approx 20 data points per company. The RCHGetGurufocusItem command seems to function fine.
If I try to copy the RCHGetGurufocusItem command to all the 400 lines x 20 cells Excel will stop functioning once I calculate manually.
I now tried making a macro that copies 20 cells RCHGetGuruFocusItem command to each company line 1 by 1, get the data, copy it to elsewhere on the sheet as 'value only' then move on to the next company. The process starts fine, but seems to slow down gradually and totally stops after a while. So far I gave up after a couple of hours waiting and restarted Excel to try something different.
Any idea whether this is:
- Something related to Excel and memory filling up or something like that
- Gurufocus website sensing large access and slowing down the process of sending the requested data
- Something related to the RCHGetGuruFocusItem command
Thanks
Mark Boogaers
I am using:
Windows 10
Excel 2013
Gurufocus premium member for USA/Europe/Asia
Sun Dec 13, 2015 11:04 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Keep in mind that the add-in is designed for ad hoc data collection, not
building of databases. You would probably be better off filtering down to a
smaller set of tickers before collecting more data on each. Besides, I'm
not sure how thrilled GuruFocus would be to have someone grabbing that much
data automatically...
Having said that, I would suspect the problem is memory. From my usage of
the command, the GuruFocus website typically returns a web page in under a
second. However, each web page is over 600K bytes. The add-in stores the
web page locally before extracting data. I'm not sure how well the VBA
environment flows with that much memory being used for data storage...
An easy way to test that hypothesis would be to add an
smfForceRecalculation call in your line by line process. However, change
the ticker symbol to "None" before running smfForceRecalculation.
Otherwise, you would just re-retrieve the web page for the ticker symbol
you had just processed, and you'd be doing twice as much work.
The process you are currently doing can be done with the
smfUpdateDownloadTable macro. But it would probably run into the same
problem. However, it might be able to diagnose the problem if you add a
time stamp to each row. You could see if each additional row is taking
longer. Also, the smfUpdateDownloadTable macro does have the ability to
selectively updates rows and/or columns of data, so you could more easily
do it piecemeal.
By the way, on their own EXCEL add-in and API tools, GuruFocus does refer
to download limits. I don't see any specific number mentioned on their
add-in, but the API says it is limited to 2000 requests per month.
On Sun, Dec 13, 2015 at 3:58 AM, mark.boogaers@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> I know this function is still in the beta stage, but trying to use this
> anyway and maybe my experience is some use for improving the feature. I
> want to do a little back testing on some Asian (Hong Kong and Malaysia)
> data and like to use Gurufocus data.
>
> I have made a spreadsheet with roughly 400 companies in it and am trying
> to import approx 20 data points per company. The RCHGetGurufocusItem
> command seems to function fine.
>
> If I try to copy the RCHGetGurufocusItem command to all the 400 lines x 20
> cells Excel will stop functioning once I calculate manually.
>
> I now tried making a macro that copies 20 cells RCHGetGuruFocusItem
> command to each company line 1 by 1, get the data, copy it to elsewhere on
> the sheet as 'value only' then move on to the next company. The process
> starts fine, but seems to slow down gradually and totally stops after a
> while. So far I gave up after a couple of hours waiting and restarted Excel
> to try something different.
>
> Any idea whether this is:
>
> - Something related to Excel and memory filling up or something like that
>
> - Gurufocus website sensing large access and slowing down the process of
> sending the requested data
>
> - Something related to the RCHGetGuruFocusItem command
>
>
> Thanks
>
> Mark Boogaers
>
>
> I am using:
>
> Windows 10
>
> Excel 2013
>
> Gurufocus premium member for USA/Europe/Asia
>
>
>
>
building of databases. You would probably be better off filtering down to a
smaller set of tickers before collecting more data on each. Besides, I'm
not sure how thrilled GuruFocus would be to have someone grabbing that much
data automatically...
Having said that, I would suspect the problem is memory. From my usage of
the command, the GuruFocus website typically returns a web page in under a
second. However, each web page is over 600K bytes. The add-in stores the
web page locally before extracting data. I'm not sure how well the VBA
environment flows with that much memory being used for data storage...
An easy way to test that hypothesis would be to add an
smfForceRecalculation call in your line by line process. However, change
the ticker symbol to "None" before running smfForceRecalculation.
Otherwise, you would just re-retrieve the web page for the ticker symbol
you had just processed, and you'd be doing twice as much work.
The process you are currently doing can be done with the
smfUpdateDownloadTable macro. But it would probably run into the same
problem. However, it might be able to diagnose the problem if you add a
time stamp to each row. You could see if each additional row is taking
longer. Also, the smfUpdateDownloadTable macro does have the ability to
selectively updates rows and/or columns of data, so you could more easily
do it piecemeal.
By the way, on their own EXCEL add-in and API tools, GuruFocus does refer
to download limits. I don't see any specific number mentioned on their
add-in, but the API says it is limited to 2000 requests per month.
On Sun, Dec 13, 2015 at 3:58 AM, mark.boogaers@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> I know this function is still in the beta stage, but trying to use this
> anyway and maybe my experience is some use for improving the feature. I
> want to do a little back testing on some Asian (Hong Kong and Malaysia)
> data and like to use Gurufocus data.
>
> I have made a spreadsheet with roughly 400 companies in it and am trying
> to import approx 20 data points per company. The RCHGetGurufocusItem
> command seems to function fine.
>
> If I try to copy the RCHGetGurufocusItem command to all the 400 lines x 20
> cells Excel will stop functioning once I calculate manually.
>
> I now tried making a macro that copies 20 cells RCHGetGuruFocusItem
> command to each company line 1 by 1, get the data, copy it to elsewhere on
> the sheet as 'value only' then move on to the next company. The process
> starts fine, but seems to slow down gradually and totally stops after a
> while. So far I gave up after a couple of hours waiting and restarted Excel
> to try something different.
>
> Any idea whether this is:
>
> - Something related to Excel and memory filling up or something like that
>
> - Gurufocus website sensing large access and slowing down the process of
> sending the requested data
>
> - Something related to the RCHGetGuruFocusItem command
>
>
> Thanks
>
> Mark Boogaers
>
>
> I am using:
>
> Windows 10
>
> Excel 2013
>
> Gurufocus premium member for USA/Europe/Asia
>
>
>
>
Sun Dec 13, 2015 10:20 am (PST) . Posted by:
luca_dalmolin
Hi there,
I'm a new member, I installed the add in but apparently I can only get data from Yahoo when I enter the code from RCHGetElementNumber-Element-Definitions. All other codes that refer to other sites generate an error.
Can anyone direct me on how to get financial statements from other sites or give me some uselful links where I can get an asnwer?
Thank you!
Luca
I'm a new member, I installed the add in but apparently I can only get data from Yahoo when I enter the code from RCHGetElementNumber
Can anyone direct me on how to get financial statements from other sites or give me some uselful links where I can get an asnwer?
Thank you!
Luca
Sun Dec 13, 2015 11:09 am (PST) . Posted by:
"Randy Harmelink" rharmelink
You may have to make adjustments to your Internet Options to get some web
sites to work. For example, check the "Tips and FAQs" web page for AdvFN
items. It's my understanding that the cross domains fix for AdvFN also
fixes any issue with Google.
Otherwise, I'd need more specifics.
The RCHGetElementNumber() data elements are really just saved versions of
other functions, so you could use some of the other functions to go after
the data directly.
For financial statements data, be sure to check out the templates. Some are
for RCHGetElementNumber() functions, but others use other add-in functions.
These days, my preferred financials data source is GuruFocus, but I'm a
subscriber. A large chunk of there data is limited to subscribers only.
On Sun, Dec 13, 2015 at 11:06 AM, luca_dalmolin@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> I'm a new member, I installed the add in but apparently I can only get
> data from Yahoo when I enter the code
> from RCHGetElementNumber-Element-Definitions. All other codes that refer to
> other sites generate an error.
>
> Can anyone direct me on how to get financial statements from other sites
> or give me some uselful links where I can get an asnwer?
>
>
sites to work. For example, check the "Tips and FAQs" web page for AdvFN
items. It's my understanding that the cross domains fix for AdvFN also
fixes any issue with Google.
Otherwise, I'd need more specifics.
The RCHGetElementNumber() data elements are really just saved versions of
other functions, so you could use some of the other functions to go after
the data directly.
For financial statements data, be sure to check out the templates. Some are
for RCHGetElementNumber() functions, but others use other add-in functions.
These days, my preferred financials data source is GuruFocus, but I'm a
subscriber. A large chunk of there data is limited to subscribers only.
On Sun, Dec 13, 2015 at 11:06 AM, luca_dalmolin@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> I'm a new member, I installed the add in but apparently I can only get
> data from Yahoo when I enter the code
> from RCHGetElementNumber
> other sites generate an error.
>
> Can anyone direct me on how to get financial statements from other sites
> or give me some uselful links where I can get an asnwer?
>
>
Sun Dec 13, 2015 10:20 am (PST) . Posted by:
john_hoel
Using version 2.1.2015.08.13. RCHGetYahooQuotes works fine in Win7, throws error in Win10. Any ideas?
Sun Dec 13, 2015 11:11 am (PST) . Posted by:
"Randy Harmelink" rharmelink
If RCHGetYahooQuotes() is returning an "Error" result, it's probably some
type of communication problem. I know others are using the add-in under
Win10. Do you have some type of firewall that is preventing EXCEL and/or
the add-in from accessing the Internet? Can you do a normal Web Query in
EXCEL?
On Sun, Dec 13, 2015 at 11:20 AM, john@nerva.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> Using version 2.1.2015.08.13. RCHGetYahooQuotes works fine in Win7, throws
> error in Win10. Any ideas?
>
>
type of communication problem. I know others are using the add-in under
Win10. Do you have some type of firewall that is preventing EXCEL and/or
the add-in from accessing the Internet? Can you do a normal Web Query in
EXCEL?
On Sun, Dec 13, 2015 at 11:20 AM, john@nerva.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> Using version 2.1.2015.08.
> error in Win10. Any ideas?
>
>
Sun Dec 13, 2015 11:25 am (PST) . Posted by:
"John Hoel"
The error spontaneously clears, then some time later recurs. Communication
parameters / environment does not change. Very strange.
On Sun, Dec 13, 2015 at 11:11 AM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> If RCHGetYahooQuotes() is returning an "Error" result, it's probably some
> type of communication problem. I know others are using the add-in under
> Win10. Do you have some type of firewall that is preventing EXCEL and/or
> the add-in from accessing the Internet? Can you do a normal Web Query in
> EXCEL?
>
> On Sun, Dec 13, 2015 at 11:20 AM, john@nerva.com [smf_addin] <
> smf_addin@yahoogroups.com> wrote:
>
>> Using version 2.1.2015.08.13. RCHGetYahooQuotes works fine in Win7,
>> throws error in Win10. Any ideas?
>>
>>
>
>
--
John Hoel
parameters / environment does not change. Very strange.
On Sun, Dec 13, 2015 at 11:11 AM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> If RCHGetYahooQuotes() is returning an "Error" result, it's probably some
> type of communication problem. I know others are using the add-in under
> Win10. Do you have some type of firewall that is preventing EXCEL and/or
> the add-in from accessing the Internet? Can you do a normal Web Query in
> EXCEL?
>
> On Sun, Dec 13, 2015 at 11:20 AM, john@nerva.com [smf_addin] <
> smf_addin@yahoogroups.com> wrote:
>
>> Using version 2.1.2015.08.
>> throws error in Win10. Any ideas?
>>
>>
>
>
--
John Hoel
Sun Dec 13, 2015 11:44 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Others have noted sporadic issues with Yahoo in the past week or two.
Possibly the same issue?
On Sun, Dec 13, 2015 at 12:25 PM, John Hoel johnhoel1248@...wrote:
>
> The error spontaneously clears, then some time later recurs. Communication
> parameters / environment does not change. Very strange.
>
>
Possibly the same issue?
On Sun, Dec 13, 2015 at 12:25 PM, John Hoel johnhoel1248@
>
> The error spontaneously clears, then some time later recurs. Communication
> parameters / environment does not change. Very strange.
>
>
Sun Dec 13, 2015 11:51 am (PST) . Posted by:
"John Hoel"
I suppose it could be, although this never happens with Win 7.
On Sun, Dec 13, 2015 at 11:44 AM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> Others have noted sporadic issues with Yahoo in the past week or two.
> Possibly the same issue?
>
> On Sun, Dec 13, 2015 at 12:25 PM, John Hoel johnhoel1248@...wrote:
>
>>
>> The error spontaneously clears, then some time later recurs.
>> Communication parameters / environment does not change. Very strange.
>>
>>
>
>
--
John Hoel
On Sun, Dec 13, 2015 at 11:44 AM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> Others have noted sporadic issues with Yahoo in the past week or two.
> Possibly the same issue?
>
> On Sun, Dec 13, 2015 at 12:25 PM, John Hoel johnhoel1248@
>
>>
>> The error spontaneously clears, then some time later recurs.
>> Communication parameters / environment does not change. Very strange.
>>
>>
>
>
--
John Hoel
Sun Dec 13, 2015 10:21 am (PST) . Posted by:
midi_guitar
Many thanks to Randy for this great add-in.
I would like to track index option quotes using the Yahoo function smfgetyahoooptionquote() if possible. I'm especially interested in ^RUT and ^SPX. I did find that using the OX version, I can get calls on SPX using SPXPM as the ticker, but not puts, and nothing seems to work for ^RUT.
Has anyone tried this and any possible fix?
Thanks,
J
I would like to track index option quotes using the Yahoo function smfgetyahoooptionqu
Has anyone tried this and any possible fix?
Thanks,
J
Sun Dec 13, 2015 11:43 am (PST) . Posted by:
"Randy Harmelink" rharmelink
The "OX" data source is for the greeks and is limited to calls only because
I haven't been able to figure out a bookmarkable URL to the puts. However,
you can use the "OX2" data source for non-greeks data, both puts and calls.
For example:
=smfGetOptionQuotes("RUT 12/19 2015 $1200 Put","l",,"OX2")
=smfGetOptionQuotes("SPX 12/19 2015 $2000 Put","l",,"OX2")
Unfortunately, OptionsXpress is a lot slower than Yahoo, so I know Yahoo
would be preferable. But Yahoo does their indexes differently than regular
options, which is why they don't work. Usually, the Yahoo option ticker
symbol starts with the ticker symbol of the equity. However, with the
indexes, that's not true. I did do a one-shot fix for VIX, but didn't
really want to put in code for each of the possible index ticker symbols.
You could always do the work-around yourself, using the RCHGetTableCell()
function. For example:
=RCHGetTableCell("http://finance.yahoo.com/q/op?s=
^RUT&m=2015-12",1,"RUT151219P01200000")
However, the S&P 500 index is an issue because of their ^SPX/^GSPC mix. But
if you're happy with ^SPXPM:
=RCHGetTableCell("http://finance.yahoo.com/q/op?s=
^SPXPM&m=2015-12",1,"SPXPM151219P02000000")
I don't do anything with the indexes, so I'm not aware of all the ins and
outs that go on for option quotes there. And the issue is complicated
because they show different data displays for Chrome/FireFox and IE, and my
version of IE is out-of-date.
On Sun, Dec 13, 2015 at 10:33 AM, midi_guitar@... wrote:
> I would like to track index option quotes using the Yahoo function
> smfgetyahoooptionquote() if possible. I'm especially interested in ^RUT
> and ^SPX. I did find that using the OX version, I can get calls on SPX
> using SPXPM as the ticker, but not puts, and nothing seems to work for
> ^RUT.
>
> Has anyone tried this and any possible fix?
>
>
I haven't been able to figure out a bookmarkable URL to the puts. However,
you can use the "OX2" data source for non-greeks data, both puts and calls.
For example:
=smfGetOptionQuotes("RUT 12/19 2015 $1200 Put","l",,"OX2")
=smfGetOptionQuotes("SPX 12/19 2015 $2000 Put","l",,"OX2")
Unfortunately, OptionsXpress is a lot slower than Yahoo, so I know Yahoo
would be preferable. But Yahoo does their indexes differently than regular
options, which is why they don't work. Usually, the Yahoo option ticker
symbol starts with the ticker symbol of the equity. However, with the
indexes, that's not true. I did do a one-shot fix for VIX, but didn't
really want to put in code for each of the possible index ticker symbols.
You could always do the work-around yourself, using the RCHGetTableCell()
function. For example:
=RCHGetTableCell("http://finance.yahoo.com/q/op?s=
^RUT&m=2015-12",1,"RUT151219P01200000")
However, the S&P 500 index is an issue because of their ^SPX/^GSPC mix. But
if you're happy with ^SPXPM:
=RCHGetTableCell("http://finance.yahoo.com/q/op?s=
^SPXPM&m=2015-
I don't do anything with the indexes, so I'm not aware of all the ins and
outs that go on for option quotes there. And the issue is complicated
because they show different data displays for Chrome/FireFox and IE, and my
version of IE is out-of-date.
On Sun, Dec 13, 2015 at 10:33 AM, midi_guitar@
> I would like to track index option quotes using the Yahoo function
> smfgetyahoooptionqu
> and ^SPX. I did find that using the OX version, I can get calls on SPX
> using SPXPM as the ticker, but not puts, and nothing seems to work for
> ^RUT.
>
> Has anyone tried this and any possible fix?
>
>
Sun Dec 13, 2015 1:47 pm (PST) . Posted by:
sharpshooter0788
I just downloaded and installed the add-in with the latest version you had posted on the website, so I think that should be fine in terms of being up to date. I tried to changing the internet explorer settings, and I don't think I need to worry about my zone as I'm solidly in Ohio. haha.
Also, it does seem to work with other elements, such as in the 3000 ranges pulling from google. AdvVN is still not working, however.
Also, it does seem to work with other elements, such as in the 3000 ranges pulling from google. AdvVN is still not working, however.
Sun Dec 13, 2015 3:14 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
What do you get for the following formulas:
=RCHGetElementNumber("Version")
=smfStrExtr(RCHGetWebData("
http://www.advfn.com/p.php?pid=financials&mode=quarterly_reports&symbol=IPG",
"/stock-market/", 50), "/stock-market/", "/")
=smfGetAdvFNElement("IPG","A",999)
=smfConvertData(smfStrExtr(RIGHT(smfGetTagContent("
http://www.advfn.com/stock-market/NYSE/IPG/financials?&mode=annual_reports&btn=start_date",
"select", 1, "Select start date"), 35), "'", "'"))
=smfGetAdvFNElement("IPG","A",1,">year end date<")
=RCHGetElementNumber("IPG", 5196)
=RCHGetTableCell("
http://www.advfn.com/stock-market/NYSE/IPG/financials?&mode=annual_reports&btn=start_date",
5,">year end date<")
On Sun, Dec 13, 2015 at 2:47 PM, ckagy1@... wrote:
>
> I just downloaded and installed the add-in with the latest version you had
> posted on the website, so I think that should be fine in terms of being up
> to date. I tried to changing the internet explorer settings, and I don't
> think I need to worry about my zone as I'm solidly in Ohio. haha.
>
> Also, it does seem to work with other elements, such as in the 3000 ranges
> pulling from google. AdvVN is still not working, however.
>
>
=RCHGetElementNumber("Version")
=smfStrExtr(RCHGetWebData("
http://www.advfn.com/p.php?pid=financials&mode=quarterly_reports&symbol=IPG",
"/stock-market/", 50), "/stock-market/", "/")
=smfGetAdvFNElement("IPG","A",999)
=smfConvertData(smfStrExtr(RIGHT(smfGetTagContent("
http://www.advfn.com/stock-market/NYSE/IPG/financials?&mode=annual_reports&btn=start_date",
"select", 1, "Select start date"), 35), "'", "'"))
=smfGetAdvFNElement("IPG","A",1,">year end date<")
=RCHGetElementNumber("IPG", 5196)
=RCHGetTableCell("
http://www.advfn.com/stock-market/NYSE/IPG/financials?&mode=annual_reports&btn=start_date",
5,">
On Sun, Dec 13, 2015 at 2:47 PM, ckagy1@... wrote:
>
> I just downloaded and installed the add-in with the latest version you had
> posted on the website, so I think that should be fine in terms of being up
> to date. I tried to changing the internet explorer settings, and I don't
> think I need to worry about my zone as I'm solidly in Ohio. haha.
>
> Also, it does seem to work with other elements, such as in the 3000 ranges
> pulling from google. AdvVN is still not working, however.
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar