15 Messages
Digest #3914
3b
Re: Is it possible to house too many RCH functions on one spreadshee by "Randy Harmelink" rharmelink
3e
Re: Is it possible to house too many RCH functions on one spreadshee by "Randy Harmelink" rharmelink
Messages
Wed Feb 8, 2017 6:30 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
As I recall, the only time the offset is an issue is on the first ticker.
Otherwise, it could be in the hundreds without causing an issue (although
the Canadian stocks may differ in how much data is sent with each ticker).
Easiest workaround would be to code a dummy ticker symbol in the literal
portion of the URL, one that you won't be retrieving.
Then just set your offset to -100 and you shouldn't have a problem?
For example, in cell C5:
=smfConvertData(smfStrExtr(smfStrExtr(RCHGetWebData("
http://www.google.com/finance/info?infotype=infoquoteall&q=X,"&smfJoin($B$5:$B$27,","),"""t""
: """&$B5&";""",,-100),"{","}"),C$2,""""))
On Wed, Feb 8, 2017 at 5:29 PM, buckleca@
...wrote:
>
> Looking at your excellent RCHGetWebData-Google-API-Quotes.xls template.
>
> I've modified so that it works for TSX (Toronto) stocks. I note that the
> ID length for some stocks can be extra long and the formula moves (-17)
> spaces to I think get to the front of string.
>
> smfConvertData(smfStrExtr(smfStrExtr(RCHGetWebData("http
> ://www.google.com/finance/info?infotype=infoquoteall&q="
> &smfJoin($C$5:$C$110,","),"""t"" : """&$B10&"""",,-17),"{","}"),O$2,""""))
>
>
> For the longer ID's - if I change the number to -26 it seems to work.
>
>
> smfConvertData(smfStrExtr(smfStrExtr(RCHGetWebData("http
> ://www.google.ca/finance/info?infotype=infoquoteall&q="&
> smfJoin($C$5:$C$110,","),"""t"" : """&$B17&"""",,-26),"{","}"),D$2,""""))
>
>
> An example of a -17 stock would be TSE:BBD.B (common stock)
>
>
> An example of a -26 stock would be TSE:BCE-Y (preferred stock)
>
>
> I was wondering if you knew of a method where I could dynamically change
> the formula based on the ID length?
>
>
> The default -17 doesn't return the correct value for the longer ID's and I
> have no way of knowing before hand if the formula needs -17 or -26 to work
> correctly.
>
>
Otherwise, it could be in the hundreds without causing an issue (although
the Canadian stocks may differ in how much data is sent with each ticker).
Easiest workaround would be to code a dummy ticker symbol in the literal
portion of the URL, one that you won't be retrieving.
Then just set your offset to -100 and you shouldn't have a problem?
For example, in cell C5:
=smfConvertData(smfStrExtr(smfStrExtr(RCHGetWebData("
http://www.google.com/finance/info?infotype=infoquoteall&q=X,"&smfJoin(
: ""
On Wed, Feb 8, 2017 at 5:29 PM, buckleca@
...wrote:
>
> Looking at your excellent RCHGetWebData-
>
> I've modified so that it works for TSX (Toronto) stocks. I note that the
> ID length for some stocks can be extra long and the formula moves (-17)
> spaces to I think get to the front of string.
>
> smfConvertData(
> ://www.google.
> &smfJoin($C$
>
>
> For the longer ID's - if I change the number to -26 it seems to work.
>
>
> smfConvertData(
> ://www.google.
> smfJoin($C$5:
>
>
> An example of a -17 stock would be TSE:BBD.B (common stock)
>
>
> An example of a -26 stock would be TSE:BCE-Y (preferred stock)
>
>
> I was wondering if you knew of a method where I could dynamically change
> the formula based on the ID length?
>
>
> The default -17 doesn't return the correct value for the longer ID's and I
> have no way of knowing before hand if the formula needs -17 or -26 to work
> correctly.
>
>
Wed Feb 8, 2017 9:46 pm (PST) . Posted by:
buckleca
Thanks - that did the trick.
I'm still trying to get my head around the need to offset the find. Is it to just grab the ID number?
Too bad Google doesn't provide more data this way. Unfortunately for Canadian stocks, Yahoo's data can be pretty inconsistent.
At least using the Google API is fast and I'm guessing that if I need more than 100 at a time, then I'd just need to change the formula range for the next group.
I'm still trying to get my head around the need to offset the find. Is it to just grab the ID number?
Too bad Google doesn't provide more data this way. Unfortunately for Canadian stocks, Yahoo's data can be pretty inconsistent.
At least using the Google API is fast and I'm guessing that if I need more than 100 at a time, then I'd just need to change the formula range for the next group.
Thu Feb 9, 2017 4:08 am (PST) . Posted by:
"Randy Harmelink" rharmelink
That is the purpose, yes. All of the variables for that ticker are
contained within "{" and "}". If you start from the ticker (i.e. "t") and
go to the ending delimiter of "}", you would have no way to get the ID.
So, if you don't need the ID, you could get rid of the offset if you change
the first set of smfStrExtr() parameters to be:
,"~","}"
...instead of:
"{","}"
That is, in cell D5:
=smfConvertData(smfStrExtr(smfStrExtr(RCHGetWebData("
http://www.google.com/finance/info?infotype=infoquoteall&q="&smfJoin($B$5:$B$27,","),"""t""
: """&$B5&";"""),"~","}"),D$2,""""))
On Wed, Feb 8, 2017 at 10:46 PM, buckleca@
...wrote:
>
> I'm still trying to get my head around the need to offset the find. Is it
> to just grab the ID number?
>
>
contained within "{" and "}". If you start from the ticker (i.e. "t") and
go to the ending delimiter of "}", you would have no way to get the ID.
So, if you don't need the ID, you could get rid of the offset if you change
the first set of smfStrExtr() parameters to be:
,"~","}"
...instead of:
"{","}"
That is, in cell D5:
=smfConvertData(smfStrExtr(smfStrExtr(RCHGetWebData("
http://www.google.com/finance/info?infotype=infoquoteall&q="&smfJoin(
: ""
On Wed, Feb 8, 2017 at 10:46 PM, buckleca@
...wrote:
>
> I'm still trying to get my head around the need to offset the find. Is it
> to just grab the ID number?
>
>
Thu Feb 9, 2017 7:21 am (PST) . Posted by:
buckleca
Much appreciated. Thanks for going the extra mile :-)
Wed Feb 8, 2017 7:52 pm (PST) . Posted by:
jgkiii
Hi, I've trying to figuring out if I can import the last trade price from this website:
http://finra-markets.morningstar.com/BondCenter/BondDetail.jsp?ticker=C578759&symbol=SFD3879026 http://finra-markets.morningstar.com/BondCenter/BondDetail.jsp?ticker=C578759&symbol=SFD3879026
I've tried the RCHGetWebData function but no matter what I try all I get is "Error" or "#Value!". One variation does download a lot of html tags but I can't discern the bond price in the mountains of information in that gets downloaded.
I have also try a couple of variations with the RCHGetHTMLTable function but since it isn't an embedded table I haven't made much headway.
Is what I want even possible? Thanks for any help you can give me.
http://finra-markets.morningstar.com/BondCenter/BondDetail.jsp?ticker=C578759&symbol=SFD3879026 http://finra-markets.morningstar.com/BondCenter/BondDetail.jsp?ticker=C578759&symbol=SFD3879026
I've tried the RCHGetWebData function but no matter what I try all I get is "Error" or "#Value!
I have also try a couple of variations with the RCHGetHTMLTable function but since it isn't an embedded table I haven't made much headway.
Is what I want even possible? Thanks for any help you can give me.
Wed Feb 8, 2017 7:58 pm (PST) . Posted by:
"Joseph G. Keithley, III" joekeithley@verizon.net
Hi, I have been trying to import the "Last Trade Price" from this page:
Bonds Detail
|
| |
Bonds Detail
| |
|
I have tried the RCHGetWebData but I either get "Error" or "#Value!". I did manage to get a lot of HTML information downloaded but I can discern the price data from the vast amount of information that fills the cell.
I also tried the RCHGetHTMLTable but with without much luck. Since the page isn't arranged in a table I did not expect much.
Is what I want even possible? Thanks for any help you can give me.
iii
Bonds Detail
|
| |
Bonds Detail
| |
|
I have tried the RCHGetWebData but I either get "Error" or "#Value!
I also tried the RCHGetHTMLTable but with without much luck. Since the page isn't arranged in a table I did not expect much.
Is what I want even possible? Thanks for any help you can give me.
iii
Wed Feb 8, 2017 8:02 pm (PST) . Posted by:
jgkiii
Sorry for the duplication. It took a long time for my message to post so I tried it a second time.
Wed Feb 8, 2017 8:13 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Wow. Wacky one here. The data you want is within a frame that displays data
from Morningstar, and the page displayed within the frame is dynamically
created.
Try:
=smfConvertData(smfStrExtr(RCHGetWebData("
http://quotes.morningstar.com/bondq/quote/c-banner?&t=C578759","Last Trade
Price",200),"$"," "))
On Wed, Feb 8, 2017 at 8:43 PM, 'Joseph G. Keithley, III' jgkiii@
...wrote:
>
>
> Hi, I have been trying to import the "Last Trade Price" from this page:
>
> Bonds Detail
> <http://finra-markets.morningstar.com/BondCenter/BondDetail.jsp?ticker=C578759&symbol=SFD3879026>
>
> Bonds Detail
> <http://finra-markets.morningstar.com/BondCenter/BondDetail.jsp?ticker=C578759&symbol=SFD3879026>
>
>
> I have tried the RCHGetWebData but I either get "Error" or "#Value!". I
> did manage to get a lot of HTML information downloaded but I can discern
> the price data from the vast amount of information that fills the cell.
>
> I also tried the RCHGetHTMLTable but with without much luck. Since the
> page isn't arranged in a table I did not expect much.
>
> Is what I want even possible? Thanks for any help you can give me.
>
>
from Morningstar, and the page displayed within the frame is dynamically
created.
Try:
=smfConvertData(smfStrExtr(RCHGetWebData("
http://quotes.morningstar.com/bondq/quote/c-banner?&t=C578759","Last Trade
Price",200),"$"," "))
On Wed, Feb 8, 2017 at 8:43 PM, 'Joseph G. Keithley, III' jgkiii@
...wrote:
>
>
> Hi, I have been trying to import the "Last Trade Price" from this page:
>
> Bonds Detail
> <http://finra-markets.morningstar.com/BondCenter/BondDetail.jsp?ticker=C578759&symbol=SFD3879026>
>
> Bonds Detail
> <http://finra-markets.morningstar.com/BondCenter/BondDetail.jsp?ticker=C578759&symbol=SFD3879026>
>
>
> I have tried the RCHGetWebData but I either get "Error" or "#Value!
> did manage to get a lot of HTML information downloaded but I can discern
> the price data from the vast amount of information that fills the cell.
>
> I also tried the RCHGetHTMLTable but with without much luck. Since the
> page isn't arranged in a table I did not expect much.
>
> Is what I want even possible? Thanks for any help you can give me.
>
>
Thu Feb 9, 2017 7:09 am (PST) . Posted by:
jhfrazer@ymail.com
Randy,
Is it possible to house too many RCH functions on a single spreadsheet?
I am working on a spreadsheet that calculates the 3mo, 1yr, 3yr total returns for each member of the S&P500 using versions of =RCHGetTableCell("http://performance.morningstar.com/Performance/stock/trailing-total-returns.action?s=0P00001MK8&align=d&t="&$C479,2,">Total Return",">"&$C479&"<")/100
and then offers up a chart popup for each member.
Problem is the worksheet often seems to "puke" and not even provide the Total Returns much less the chart pop ups.
Any thoughts?
Is it possible to house too many RCH functions on a single spreadsheet?
I am working on a spreadsheet that calculates the 3mo, 1yr, 3yr total returns for each member of the S&P500 using versions of =RCHGetTableCell("http://performance.morningstar.com/Performance/stock/trailing-total-returns.action?s=0P00001MK8&align=d&t="&$C479,
and then offers up a chart popup for each member.
Problem is the worksheet often seems to "puke" and not even provide the Total Returns much less the chart pop ups.
Any thoughts?
Thu Feb 9, 2017 8:08 am (PST) . Posted by:
"Randy Harmelink" rharmelink
The add-in does have a limit of retrieving 1000 web pages in a "session".
Instead of getting the data for each S&P 500 stock, you could use this
instead:
=smfGetCSVFile("
http://portfolios.morningstar.com/fund/holdingsExport?t=SPY&exportType=details
")
Then it's just one Internet access instead of 500. In any case, if you
create a "table" of data like that, you may want to use the
smfUpdateDownloadTable process -- it stores values in the worksheet instead
of having individual formulas that recalculate automatically. You can
request updates by columns, by rows, or for a range.
I wouldn't load the chart pop-ups unless you actually use them. Maybe have
a toggle switch for each ticker, telling it to load it or not?
On Thu, Feb 9, 2017 at 8:04 AM, jhfrazer@
...wrote:
> Is it possible to house too many RCH functions on a single spreadsheet?
>
> I am working on a spreadsheet that calculates the 3mo, 1yr, 3yr total
> returns for each member of the S&P500 using versions of
> =RCHGetTableCell("http://performance.morningstar.com/
> Performance/stock/trailing-total-returns.action?s=0P00001MK8&align=d&t=
> "&$C479,2,">Total Return",">"&$C479&"<")/100
>
> and then offers up a chart popup for each member.
>
> Problem is the worksheet often seems to "puke" and not even provide the
> Total Returns much less the chart pop ups.
>
> Any thoughts?
>
>
Instead of getting the data for each S&P 500 stock, you could use this
instead:
=smfGetCSVFile("
http://portfolios.morningstar.com/fund/holdingsExport?t=SPY&exportType=details
")
Then it's just one Internet access instead of 500. In any case, if you
create a "table" of data like that, you may want to use the
smfUpdateDownloadTable process -- it stores values in the worksheet instead
of having individual formulas that recalculate automatically. You can
request updates by columns, by rows, or for a range.
I wouldn't load the chart pop-ups unless you actually use them. Maybe have
a toggle switch for each ticker, telling it to load it or not?
On Thu, Feb 9, 2017 at 8:04 AM, jhfrazer@
...wrote:
> Is it possible to house too many RCH functions on a single spreadsheet?
>
> I am working on a spreadsheet that calculates the 3mo, 1yr, 3yr total
> returns for each member of the S&P500 using versions of
> =RCHGetTableCell("http://performance.morningstar.com/
> Performance/
> "&$C479,
>
> and then offers up a chart popup for each member.
>
> Problem is the worksheet often seems to "puke" and not even provide the
> Total Returns much less the chart pop ups.
>
> Any thoughts?
>
>
Thu Feb 9, 2017 10:06 am (PST) . Posted by:
jhfrazer@ymail.com
Thank you. Let me ponder my alternatives.
Thu Feb 9, 2017 10:22 am (PST) . Posted by:
jhfrazer@ymail.com
As a workaround, and due to the fact that I sort the S&P500 data table can I get around the 1000 web page retrieval limit by using the "Recalculate Section" item in the SMF right click flyout?
Thu Feb 9, 2017 1:41 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
No. That's for normal EXCEL recalculation. It has no effect on web
retrieval items that would be part of the 1000-page limit.
The reason there is a 1000-page limit is that the web pages are stored
before data is extracted from them. That way, if you extract multiple items
from the web page, the web page still only needs to be retrieved the first
time. However, normal EXCEL recalculation would just recalculate based on
the originally retrieved web pages.
If you need to sort the table, just convert the array-entered range into
values.
On Thu, Feb 9, 2017 at 11:18 AM, jhfrazer@
...wrote:
>
> As a workaround, and due to the fact that I sort the S&P500 data table can
> I get around the 1000 web page retrieval limit by using the "Recalculate
> Section" item in the SMF right click flyout?
>
>
retrieval items that would be part of the 1000-page limit.
The reason there is a 1000-page limit is that the web pages are stored
before data is extracted from them. That way, if you extract multiple items
from the web page, the web page still only needs to be retrieved the first
time. However, normal EXCEL recalculation would just recalculate based on
the originally retrieved web pages.
If you need to sort the table, just convert the array-entered range into
values.
On Thu, Feb 9, 2017 at 11:18 AM, jhfrazer@
...wrote:
>
> As a workaround, and due to the fact that I sort the S&P500 data table can
> I get around the 1000 web page retrieval limit by using the "Recalculate
> Section" item in the SMF right click flyout?
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar