6 Messages
Digest #3913
Messages
Tue Feb 7, 2017 11:15 pm (PST) . Posted by:
caleb.mullins25
Hi,
It works using
RCHGetTableCell("http://www.nasdaq.com/symbol/aapl/institutional-holdings",1,"Total Value of Holdings (millions)")
but when i try
RCHGetTableCell("http://www.nasdaq.com/symbol/"&A1&"/institutional-holdings",1,"Total Value of Holdings (millions)")
it returns "Error"
Same is the case for any data on that page. Cell reference is not working
It works using
RCHGetTableCell("http://www.nasdaq.com/symbol/aapl/institutional-holdings",1,"Total Value of Holdings (millions)")
but when i try
RCHGetTableCell("http://www.nasdaq.com/symbol/"&A1&"
it returns "Error"
Same is the case for any data on that page. Cell reference is not working
Wed Feb 8, 2017 3:29 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Both of your formulas work for me. HOWEVER, the URL won't work unless the
URL is all lower case. So, cell A1 would need to contain "aapl" and not
"AAPL". Easy to get around with:
=RCHGetTableCell("http://www.nasdaq.com/symbol/"&LOWER(A1)&"/institutional-holdings",1,"Total
Value of Holdings (millions)")
...to force the contents of A1 to be concatenated in lower case.
On Wed, Feb 8, 2017 at 12:15 AM, naeem_4tk@
...
wrote:
>
>
> I
> t works using
>
> RCHGetTableCell("http://www.nasdaq.com/symbol/aapl/institutional-holdings
> ",1,"Total Value of Holdings (millions)")
>
> but when i try
>
> RCHGetTableCell("http://www.nasdaq.com/symbol/"&A1&"/
> institutional-holdings",1,"Total Value of Holdings (millions)")
>
> it returns "Error"
>
> Same is the case for any data on that page. Cell reference is not working
>
URL is all lower case. So, cell A1 would need to contain "aapl" and not
"AAPL". Easy to get around with:
=RCHGetTableCell("http://www.nasdaq.com/symbol/"&LOWER(A1)&"/institutional-holdings",1,"Total
Value of Holdings (millions)")
...to force the contents of A1 to be concatenated in lower case.
On Wed, Feb 8, 2017 at 12:15 AM, naeem_4tk@
...
wrote:
>
>
> I
> t works using
>
> RCHGetTableCell("http://www.nasdaq.com/symbol/aapl/institutional-holdings
> ",1,"Total Value of Holdings (millions)")
>
> but when i try
>
> RCHGetTableCell("http://www.nasdaq.com/symbol/"&A1&"
> institutional-
>
> it returns "Error"
>
> Same is the case for any data on that page. Cell reference is not working
>
Wed Feb 8, 2017 3:47 pm (PST) . Posted by:
who1012000
Hello,
I am bringing in data from -> http://finance.yahoo.com/q?s=" & Symbol <- this is put into column Z, where I then search for data. No problems here, but I have been clearing the columns "Y:AZ" using Columns("Y:AZ")Clear
The above has worked like a charm for quite a long time. Now it is take a very long time up to 10 seconds. I have tried several variations including Columns("Y:AA").EntireColumn.Delete and
With Sheets(ActiveSheet.Name)
.Columns("Y:AA").EntireColumn.Delete
End With
and
Columns("Y:AA").EntireColumn.Delete
But all of these things are taking a very long time. This is pretty new. I had been using application.ScreenUpdating=False , but changed that to observe what was happening.
Anyone have any idea what might have changed to make these things take so long?
I do the above on several web sites to get other data and all have been slowed down?
Hope all are having a good day.
Thanks in advance, David
I am bringing in data from -> http://finance.yahoo.com/q?s=" & Symbol <- this is put into column Z, where I then search for data. No problems here, but I have been clearing the columns "Y:AZ" using Columns("
The above has worked like a charm for quite a long time. Now it is take a very long time up to 10 seconds. I have tried several variations including Columns("
With Sheets(ActiveSheet.
.Columns("
End With
and
Columns("
But all of these things are taking a very long time. This is pretty new. I had been using application.
Anyone have any idea what might have changed to make these things take so long?
I do the above on several web sites to get other data and all have been slowed down?
Hope all are having a good day.
Thanks in advance, David
Wed Feb 8, 2017 4:08 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Assuming this is add-in related, have you added a number of volatile
RCHGetYahooQuotes() functions? Those will recalculate any time you delete
columns. If you have many, it could easily take 10 seconds to update.
If it's not add-in related, you should ask the question in a Yahoo VBA or
EXCEL group...
On Wed, Feb 8, 2017 at 4:47 PM, who1012000@
...wrote:
> I am bringing in data from -> http://finance.yahoo.com/q?s=" & Symbol <-
> this is put into column Z, where I then search for data. No problems here,
> but I have been clearing the columns "Y:AZ" using Columns("Y:AZ")Clear
>
> The above has worked like a charm for quite a long time. Now it is take a
> very long time up to 10 seconds. I have tried several variations
> including Columns("Y:AA").EntireColumn.Delete and
>
> With Sheets(ActiveSheet.Name)
>
> .Columns("Y:AA").EntireColumn.Delete
>
> End With
>
> and
>
> Columns("Y:AA").EntireColumn.Delete
>
> But all of these things are taking a very long time. This is pretty new. I
> had been using application.ScreenUpdating=False , but
> changed that to observe what was happening.
>
> Anyone have any idea what might have changed to make these things take so
> long?
>
> I do the above on several web sites to get other data and all have been
> slowed down?
>
>
>
RCHGetYahooQuotes() functions? Those will recalculate any time you delete
columns. If you have many, it could easily take 10 seconds to update.
If it's not add-in related, you should ask the question in a Yahoo VBA or
EXCEL group...
On Wed, Feb 8, 2017 at 4:47 PM, who1012000@
...wrote:
> I am bringing in data from -> http://finance.yahoo.com/q?s=" & Symbol <-
> this is put into column Z, where I then search for data. No problems here,
> but I have been clearing the columns "Y:AZ" using Columns("
>
> The above has worked like a charm for quite a long time. Now it is take a
> very long time up to 10 seconds. I have tried several variations
> including Columns("
>
> With Sheets(ActiveSheet.
>
> .Columns("
>
> End With
>
> and
>
> Columns("
>
> But all of these things are taking a very long time. This is pretty new. I
> had been using application.
> changed that to observe what was happening.
>
> Anyone have any idea what might have changed to make these things take so
> long?
>
> I do the above on several web sites to get other data and all have been
> slowed down?
>
>
>
Wed Feb 8, 2017 4:22 pm (PST) . Posted by:
"David" who1012000
Thanks Randy. I will address it in another group. David
From: "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com>
To: smf_addin@yahoogroups.com
Sent: Wednesday, February 8, 2017 4:01 PM
Subject: Re: [smf_addin] VBA clear columns
#yiv5974332897 #yiv5974332897 -- #yiv5974332897 .yiv5974332897ygrp-photo-title{clear:both;font-size:smaller;height:15px;overflow:hidden;text-align:center;width:75px;}#yiv5974332897 div.yiv5974332897ygrp-photo{background-position:center;background-repeat:no-repeat;background-color:white;border:1px solid black;height:62px;width:62px;}#yiv5974332897 div.yiv5974332897photo-title a, #yiv5974332897 div.yiv5974332897photo-title a:active, #yiv5974332897 div.yiv5974332897photo-title a:hover, #yiv5974332897 div.yiv5974332897photo-title a:visited {text-decoration:none;}#yiv5974332897 div.yiv5974332897attach-table div.yiv5974332897attach-row {clear:both;}#yiv5974332897 div.yiv5974332897attach-table div.yiv5974332897attach-row div {float:left;}#yiv5974332897 p {clear:both;padding:15px 0 3px 0;overflow:hidden;}#yiv5974332897 div.yiv5974332897ygrp-file {width:30px;}#yiv5974332897 div.yiv5974332897attach-table div.yiv5974332897attach-row div div a {text-decoration:none;}#yiv5974332897 div.yiv5974332897attach-table div.yiv5974332897attach-row div div span {font-weight:normal;}#yiv5974332897 div.yiv5974332897ygrp-file-title {font-weight:bold;}#yiv5974332897 #yiv5974332897 #yiv5974332897 #yiv5974332897 --#yiv5974332897ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5974332897 #yiv5974332897ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5974332897 #yiv5974332897ygrp-mkp #yiv5974332897hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv5974332897 #yiv5974332897ygrp-mkp #yiv5974332897ads {margin-bottom:10px;}#yiv5974332897 #yiv5974332897ygrp-mkp .yiv5974332897ad {padding:0 0;}#yiv5974332897 #yiv5974332897ygrp-mkp .yiv5974332897ad p {margin:0;}#yiv5974332897 #yiv5974332897ygrp-mkp .yiv5974332897ad a {color:#0000ff;text-decoration:none;}#yiv5974332897
Assuming this is add-in related, have you added a number of volatile RCHGetYahooQuotes() functions? Those will recalculate any time you delete columns. If you have many, it could easily take 10 seconds to update.
If it's not add-in related, you should ask the question in a Yahoo VBA or EXCEL group...
On Wed, Feb 8, 2017 at 4:47 PM, who1012000@...wrote:
I am bringing in data from -> http://finance.yahoo.com/q?s=" & Symbol <- this is put into column Z, where I then search for data. No problems here, but I have been clearing the columns "Y:AZ" using Columns("Y:AZ")ClearThe above has worked like a charm for quite a long time. Now it is take a very long time up to 10 seconds. I have tried several variations including Columns("Y:AA"). EntireColumn.Delete and With Sheets(ActiveSheet.Name) .Columns("Y:AA").EntireColumn. DeleteEnd Withand Columns("Y:AA").EntireColumn. Delete
But all of these things are taking a very long time. This is pretty new. I had been using application.ScreenUpdating= False , but changed that to observe what was happening.Anyone have any idea what might have changed to make these things take so long?I do the above on several web sites to get other data and all have been slowed down?
From: "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com>
To: smf_addin@yahoogroups.com
Sent: Wednesday, February 8, 2017 4:01 PM
Subject: Re: [smf_addin] VBA clear columns
#yiv5974332897 #yiv5974332897 -- #yiv5974332897 .yiv5974332897ygrp-photo-title{clear:both;font-size:smaller;height:15px;overflow:hidden;text-align:center;width:75px;}#yiv5974332897 div.yiv5974332897ygrp-photo{background-position:center;background-repeat:no-repeat;background-color:white;border:1px solid black;height:62px;width:62px;}#yiv5974332897 div.yiv5974332897photo-title a, #yiv5974332897 div.yiv5974332897photo-title a:active, #yiv5974332897 div.yiv5974332897photo-title a:hover, #yiv5974332897 div.yiv5974332897photo-title a:visited {text-decoration:none;}#yiv5974332897 div.yiv5974332897attach-table div.yiv5974332897attach-row {clear:both;}#yiv5974332897 div.yiv5974332897attach-table div.yiv5974332897attach-row div {float:left;}#yiv5974332897 p {clear:both;padding:15px 0 3px 0;overflow:hidden;}#yiv5974332897 div.yiv5974332897ygrp-file {width:30px;}#yiv5974332897 div.yiv5974332897attach-table div.yiv5974332897attach-row div div a {text-decoration:none;}#yiv5974332897 div.yiv5974332897attach-table div.yiv5974332897attach-row div div span {font-weight:normal;}#yiv5974332897 div.yiv5974332897ygrp-file-title {font-weight:bold;}#yiv5974332897 #yiv5974332897 #yiv5974332897 #yiv5974332897 --#yiv5974332897ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5974332897 #yiv5974332897ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5974332897 #yiv5974332897ygrp-mkp #yiv5974332897hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv5974332897 #yiv5974332897ygrp-mkp #yiv5974332897ads {margin-bottom:10px;}#yiv5974332897 #yiv5974332897ygrp-mkp .yiv5974332897ad {padding:0 0;}#yiv5974332897 #yiv5974332897ygrp-mkp .yiv5974332897ad p {margin:0;}#yiv5974332897 #yiv5974332897ygrp-mkp .yiv5974332897ad a {color:#0000ff;text-decoration:none;}#yiv5974332897
Assuming this is add-in related, have you added a number of volatile RCHGetYahooQuotes() functions? Those will recalculate any time you delete columns. If you have many, it could easily take 10 seconds to update.
If it's not add-in related, you should ask the question in a Yahoo VBA or EXCEL group...
On Wed, Feb 8, 2017 at 4:47 PM, who1012000@...wrote:
I am bringing in data from -> http://finance.yahoo.com/q?s=" & Symbol <- this is put into column Z, where I then search for data. No problems here, but I have been clearing the columns "Y:AZ" using Columns("
But all of these things are taking a very long time. This is pretty new. I had been using application.
Wed Feb 8, 2017 4:30 pm (PST) . Posted by:
buckleca
Hi Randy,
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.
Thanks.
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(
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.
Thanks.
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar