15 Messages
Digest #3010
Messages
Wed Mar 19, 2014 12:23 am (PDT) . Posted by:
"Nikola Ganev" ganevniko
Hi there,
I am experiencing two problems with the rchgetelementnumber funtion :
1) When I open the samne file on two different computers the getelementnumber funtion shows an error : I have excel 2007 on my laptop and it works but on an other computer with excel 2010 the rchgetelementnumber function gives me only errors for the same file.
2) I pull fundamental data with the rchgetelementnumber function and then when I want to make operations with this data (averages, sums...) it gives an error.
Does anybody know how I can avoid these errors?
I am experiencing two problems with the rchgetelementnumber funtion :
1) When I open the samne file on two different computers the getelementnumber funtion shows an error : I have excel 2007 on my laptop and it works but on an other computer with excel 2010 the rchgetelementnumber function gives me only errors for the same file.
2) I pull fundamental data with the rchgetelementnumber function and then when I want to make operations with this data (averages, sums...) it gives an error.
Does anybody know how I can avoid these errors?
Wed Mar 19, 2014 1:02 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
What do you get on each machine with:
=RCHGetElementNumber("Version")
On the machine that gets errors, what do you get with:
=RCHGetElementNumber("MMM",941)
=RCHGetTableCell("http://finance.yahoo.com/q/ks?s=IBM",1,">Market Cap")
Give me details about the functions you're using in item (2).
On Wed, Mar 19, 2014 at 12:20 AM, Nikola Ganev <ganevniko@yahoo.com> wrote:
>
> I am experiencing two problems with the rchgetelementnumber funtion :
>
> 1) When I open the samne file on two different computers the
> getelementnumber funtion shows an error : I have excel 2007 on my
> laptop and it works but on an other computer with excel 2010 the
> rchgetelementnumber function gives me only errors for the same file.
>
> 2) I pull fundamental data with the rchgetelementnumber function and then
> when I want to make operations with this data (averages, sums...) it gives
> an error.
>
> Does anybody know how I can avoid these errors?
>
=RCHGetElementNumber("Version")
On the machine that gets errors, what do you get with:
=RCHGetElementNumber("MMM",941)
=RCHGetTableCell("http://finance.yahoo.com/q/ks?s=IBM",1,">Market Cap")
Give me details about the functions you're using in item (2).
On Wed, Mar 19, 2014 at 12:20 AM, Nikola Ganev <ganevniko@yahoo.com> wrote:
>
> I am experiencing two problems with the rchgetelementnumber funtion :
>
> 1) When I open the samne file on two different computers the
> getelementnumber funtion shows an error : I have excel 2007 on my
> laptop and it works but on an other computer with excel 2010 the
> rchgetelementnumber function gives me only errors for the same file.
>
> 2) I pull fundamental data with the rchgetelementnumber function and then
> when I want to make operations with this data (averages, sums...) it gives
> an error.
>
> Does anybody know how I can avoid these errors?
>
Wed Mar 19, 2014 11:10 am (PDT) . Posted by:
"Nikola Ganev" ganevniko
This what I get on one of the machines :
I dont have acces to the other one yet (the one that gives errors) I will let you know asap.
For the item two, I use
rchgetelementnumber("KO",9446) = 13,070.0 in C5
rchgetelementnumber("KO",9447) = 13,070.0 in D5
rchgetelementnumber("KO",9448) = 6,196.0 in E5
Then sum(D5:E5) gives me 0
________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Wednesday, March 19, 2014 9:02 AM
Subject: Re: [smf_addin] Errors with Rchgetelementnumber function
What do you get on each machine with:
=RCHGetElementNumber("Version")
On the machine that gets errors, what do you get with:
=RCHGetElementNumber("MMM",941)
=RCHGetTableCell("http://finance.yahoo.com/q/ks?s=IBM",1,">Market Cap")
Give me details about the functions you're using in item (2).
On Wed, Mar 19, 2014 at 12:20 AM, Nikola Ganev <ganevniko@yahoo.com> wrote:
>
>I am experiencing two problems with the rchgetelementnumber funtion :
>
>1) When I open the samne file on two different computers the getelementnumber funtion shows an error : I have excel 2007 on my laptop and it works but on an other computer with excel 2010 the rchgetelementnumber function gives me only errors for the same file.
>
>2) I pull fundamental data with the rchgetelementnumber function and then when I want to make operations with this data (averages, sums...) it gives an error.
>
>Does anybody know how I can avoid these errors?
>
RCHGetElementNumber("version") Stock Market Functions add-in, Version 2.1.2014.01.30 (C:\Program Files\SMF Add-In; www; 33)
RCHGetElementNumber("mmm";941) 87710000
RCHGetTableCell("http://finance.yahoo.com/q/ks?s=IBM";1;">Market Cap") 193730000
I dont have acces to the other one yet (the one that gives errors) I will let you know asap.
For the item two, I use
rchgetelementnumber("KO",9446) = 13,070.0 in C5
rchgetelementnumber("KO",9447) = 13,070.0 in D5
rchgetelementnumber("KO",9448) = 6,196.0 in E5
Then sum(D5:E5) gives me 0
________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Wednesday, March 19, 2014 9:02 AM
Subject: Re: [smf_addin] Errors with Rchgetelementnumber function
What do you get on each machine with:
=RCHGetElementNumber("Version")
On the machine that gets errors, what do you get with:
=RCHGetElementNumber("MMM",941)
=RCHGetTableCell("http://finance.yahoo.com/q/ks?s=IBM",1,">Market Cap")
Give me details about the functions you're using in item (2).
On Wed, Mar 19, 2014 at 12:20 AM, Nikola Ganev <ganevniko@yahoo.com> wrote:
>
>I am experiencing two problems with the rchgetelementnumber funtion :
>
>1) When I open the samne file on two different computers the getelementnumber funtion shows an error : I have excel 2007 on my laptop and it works but on an other computer with excel 2010 the rchgetelementnumber function gives me only errors for the same file.
>
>2) I pull fundamental data with the rchgetelementnumber function and then when I want to make operations with this data (averages, sums...) it gives an error.
>
>Does anybody know how I can avoid these errors?
>
RCHGetElementNumber("version") Stock Market Functions add-in, Version 2.1.2014.01.30 (C:\Program Files\SMF Add-In; www; 33)
RCHGetElementNumber("mmm";941) 87710000
RCHGetTableCell("http://finance.yahoo.com/q/ks?s=IBM";1;"
Wed Mar 19, 2014 11:11 am (PDT) . Posted by:
"Nikola Ganev" ganevniko
This what I get on the other machine Stock Market Functions add-in, Version 2.1.2012.12.29 (C:\Program Files\SMF Add-In; 1)
Undefined
193260000
________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Wednesday, March 19, 2014 9:02 AM
Subject: Re: [smf_addin] Errors with Rchgetelementnumber function
What do you get on each machine with:
=RCHGetElementNumber("Version")
On the machine that gets errors, what do you get with:
=RCHGetElementNumber("MMM",941)
=RCHGetTableCell("http://finance.yahoo.com/q/ks?s=IBM",1,">Market Cap")
Give me details about the functions you're using in item (2).
On Wed, Mar 19, 2014 at 12:20 AM, Nikola Ganev <ganevniko@yahoo.com> wrote:
>
>I am experiencing two problems with the rchgetelementnumber funtion :
>
>1) When I open the samne file on two different computers the getelementnumber funtion shows an error : I have excel 2007 on my laptop and it works but on an other computer with excel 2010 the rchgetelementnumber function gives me only errors for the same file.
>
>2) I pull fundamental data with the rchgetelementnumber function and then when I want to make operations with this data (averages, sums...) it gives an error.
>
>Does anybody know how I can avoid these errors?
>
Undefined
193260000
________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Wednesday, March 19, 2014 9:02 AM
Subject: Re: [smf_addin] Errors with Rchgetelementnumber function
What do you get on each machine with:
=RCHGetElementNumber("Version")
On the machine that gets errors, what do you get with:
=RCHGetElementNumber("MMM",941)
=RCHGetTableCell("http://finance.yahoo.com/q/ks?s=IBM",1,">Market Cap")
Give me details about the functions you're using in item (2).
On Wed, Mar 19, 2014 at 12:20 AM, Nikola Ganev <ganevniko@yahoo.com> wrote:
>
>I am experiencing two problems with the rchgetelementnumber funtion :
>
>1) When I open the samne file on two different computers the getelementnumber funtion shows an error : I have excel 2007 on my laptop and it works but on an other computer with excel 2010 the rchgetelementnumber function gives me only errors for the same file.
>
>2) I pull fundamental data with the rchgetelementnumber function and then when I want to make operations with this data (averages, sums...) it gives an error.
>
>Does anybody know how I can avoid these errors?
>
Wed Mar 19, 2014 2:30 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
"Undefined" means you don't have the smf-element-*.txt files in the folder
with the add-in file. You need to extract ALL of the files from the ZIP
archive. Those smf-element-*.txt files contain the element definitions. So
if they're not in that folder, none of the RCHGetElementNumber() functions
are going to work.
On Wed, Mar 19, 2014 at 11:09 AM, Nikola Ganev <ganevniko@yahoo.com> wrote:
>
> This what I get on the other machine
> Stock Market Functions add-in, Version 2.1.2012.12.29 (C:\Program
> Files\SMF Add-In; 1)
> Undefined
> 193260000
>
> *From:* Randy Harmelink <rharmelink@gmail.com>
> *To:* smf_addin@yahoogroups.com
> *Sent:* Wednesday, March 19, 2014 9:02 AM
> *Subject:* Re: [smf_addin] Errors with Rchgetelementnumber function
>
>
> What do you get on each machine with:
>
> =RCHGetElementNumber("Version")
>
> On the machine that gets errors, what do you get with:
>
> =RCHGetElementNumber("MMM",941)
> =RCHGetTableCell("http://finance.yahoo.com/q/ks?s=IBM",1,">Market Cap")
>
with the add-in file. You need to extract ALL of the files from the ZIP
archive. Those smf-element-*.txt files contain the element definitions. So
if they're not in that folder, none of the RCHGetElementNumber() functions
are going to work.
On Wed, Mar 19, 2014 at 11:09 AM, Nikola Ganev <ganevniko@yahoo.com> wrote:
>
> This what I get on the other machine
> Stock Market Functions add-in, Version 2.1.2012.12.29 (C:\Program
> Files\SMF Add-In; 1)
> Undefined
> 193260000
>
> *From:* Randy Harmelink <rharmelink@gmail.com>
> *To:* smf_addin@yahoogroups.com
> *Sent:* Wednesday, March 19, 2014 9:02 AM
> *Subject:* Re: [smf_addin] Errors with Rchgetelementnumber function
>
>
> What do you get on each machine with:
>
> =RCHGetElementNumber("Version")
>
> On the machine that gets errors, what do you get with:
>
> =RCHGetElementNumber("MMM",941)
> =RCHGetTableCell("http://finance.yahoo.com/q/ks?s=IBM",1,"
>
Wed Mar 19, 2014 2:35 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
It looks like those are still text items, possibly because of your regional
settings. I assume you don't express numbers with commas and periods like
that? If so, they were never converted into numbers, and left as text,
because the VBA functions that convert text into numbers are based on your
regional setting and won't recognize such data.
On Wed, Mar 19, 2014 at 11:07 AM, Nikola Ganev <ganevniko@yahoo.com> wrote:
>
> For the item two, I use
>
> rchgetelementnumber("KO",9446) = 13,070.0 in C5
> rchgetelementnumber("KO",9447) = 13,070.0 in D5
> rchgetelementnumber("KO",9448) = 6,196.0 in E5
>
> Then sum(D5:E5) gives me 0
>
settings. I assume you don't express numbers with commas and periods like
that? If so, they were never converted into numbers, and left as text,
because the VBA functions that convert text into numbers are based on your
regional setting and won't recognize such data.
On Wed, Mar 19, 2014 at 11:07 AM, Nikola Ganev <ganevniko@yahoo.com> wrote:
>
> For the item two, I use
>
> rchgetelementnumber
> rchgetelementnumber
> rchgetelementnumber
>
> Then sum(D5:E5) gives me 0
>
Wed Mar 19, 2014 12:56 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Hmmm. That's really strange...sorry I have nothing to offer.
The RCHCreateComment() is unique in that it does something it's not
supposed to be able to do. Functions are only supposed to be able to return
values. They shouldn't be able to change anything else in the workbook. For
example, a format of a cell. However, MicroSoft screwed up in this case,
and allowed functions access to the comment object in the workbook. So this
function is using an "undocumented" feature. It wouldn't surprise me if the
capability disappears in the future.
On Tue, Mar 18, 2014 at 3:13 PM, Pete A <optionzz@gmail.com> wrote:
>
> With this awful formula, I̢۪m getting cell content AND a popup chart into
> the same cell. Ugly, but mostly works. However, a strange problem has
> cropped up after using it for a few days.
>
>
>
> =IF(B6="",IF(RCHCreateComment("None",99)="None",""),IF(ISNA(VLOOKUP(B6,'TtP
> Holdings'!$B$4:$B$53,1,FALSE)),RCHCreateComment("
> http://stockcharts.com/c-sc/sc?s=
> "&B6&"&p=D&b=5&g=0&id=p01478707151",99,350,360,,,,,,B6),IF(RCHCreateComment("None",99)="None","x")))
>
>
>
>
> When I enter the formula above, and press enter, I get (the Green Arrow),
> But when I recalculate I get the Red Arrow.
>
>
>
> This is new to me. I have to put the cursor into the formula bar, and
> press enter, 1 for each row. That gives me entries like the green arrow,
> but properly CINF>.
>
>
> But as soon as I save, and reopen, it̢۪s back to displaying part of the
> formula.
>
>
>
> As I wrote this email, I decided to try “ForceRecalculation†macro. That
> did it.
>
>
>
> Publishing this only because it might help others solve similar problems.
>
>
>
> Pete A
>
> <http://www.ttp-s.com/php/toolsmenu.php>
>
>
>
The RCHCreateComment() is unique in that it does something it's not
supposed to be able to do. Functions are only supposed to be able to return
values. They shouldn't be able to change anything else in the workbook. For
example, a format of a cell. However, MicroSoft screwed up in this case,
and allowed functions access to the comment object in the workbook. So this
function is using an "undocumented" feature. It wouldn't surprise me if the
capability disappears in the future.
On Tue, Mar 18, 2014 at 3:13 PM, Pete A <optionzz@gmail.com> wrote:
>
> With this awful formula, I̢۪m getting cell content AND a popup chart into
> the same cell. Ugly, but mostly works. However, a strange problem has
> cropped up after using it for a few days.
>
>
>
> =IF(B6="",IF(RCHCreateComment("None",99)="None",""),IF(ISNA(VLOOKUP(B6,'TtP
> Holdings'!$B$4:$B$53,1,FALSE)),RCHCreateComment("
> http://stockcharts.com/c-sc/sc?s=
> "&B6&"&p=D&b=5&g=0&id=p01478707151",99,350,360,,,,,,B6),IF(RCHCreateComment("None",99)="None","x")))
>
>
>
>
> When I enter the formula above, and press enter, I get (the Green Arrow),
> But when I recalculate I get the Red Arrow.
>
>
>
> This is new to me. I have to put the cursor into the formula bar, and
> press enter, 1 for each row. That gives me entries like the green arrow,
> but properly CINF>.
>
>
> But as soon as I save, and reopen, it̢۪s back to displaying part of the
> formula.
>
>
>
> As I wrote this email, I decided to try “ForceRecalculation†macro. That
> did it.
>
>
>
> Publishing this only because it might help others solve similar problems.
>
>
>
> Pete A
>
> <http://www.ttp-s.com/php/toolsmenu.php>
>
>
>
Wed Mar 19, 2014 4:52 am (PDT) . Posted by:
"Lezman, Douglas" lezm001
I currently pull in a lot of data for many ticker symbols. I often hit the 1000 record limit. I have an individual formula in each cell and would like to convert my formulas to arrays but have not been successful. Does the RCHGetElementNumber function work in an array? I'd like to have one column with multiple ticker symbols in it and one row with many different element numbers listed and use an array to pull in the information. I've attached an example of what I'm trying to accomplish. Please let me know what I'm doing wrong.
Thanks,
Doug Lezman
Director, Engineering, Modernization & Skilled Trades
Steelcase Inc.
o. 616.698.1323
c. 616.291.0795
[cid:image003.jpg@01CF4348.1EF4A6D0]
Thanks,
Doug Lezman
Director, Engineering, Modernization & Skilled Trades
Steelcase Inc.
o. 616.698.1323
c. 616.291.0795
[cid:image003.jpg@01CF4348.1EF4A6D0]
Attachment(s) from Lezman, Douglas
1 of 1 File(s)
Wed Mar 19, 2014 6:20 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
There is no array-entered version of RCHGetElementNumber(). There would be
no advantage in doing so, since the add-in would still need to get all of
the same individual web pages.
In any case, the add-in is designed for ad hoc usage, not bulk downloading.
I don't think it fair to burden the free data sources with excessive data
requests. It's one of the issues I worried about when I decided to share
the add-in. It would be easy enough for them to prevent the add-in from
accessing their data.
On Wed, Mar 19, 2014 at 4:52 AM, Lezman, Douglas <dlezman@steelcase.com>wrote:
>
> I currently pull in a lot of data for many ticker symbols. I often hit
> the 1000 record limit. I have an individual formula in each cell and would
> like to convert my formulas to arrays but have not been successful. Does
> the RCHGetElementNumber function work in an array? I̢۪d like to have one
> column with multiple ticker symbols in it and one row with many different
> element numbers listed and use an array to pull in the information. I̢۪ve
> attached an example of what I̢۪m trying to accomplish. Please let me know
> what I̢۪m doing wrong.
>
>
>
no advantage in doing so, since the add-in would still need to get all of
the same individual web pages.
In any case, the add-in is designed for ad hoc usage, not bulk downloading.
I don't think it fair to burden the free data sources with excessive data
requests. It's one of the issues I worried about when I decided to share
the add-in. It would be easy enough for them to prevent the add-in from
accessing their data.
On Wed, Mar 19, 2014 at 4:52 AM, Lezman, Douglas <dlezman@steelcase.com>wrote:
>
> I currently pull in a lot of data for many ticker symbols. I often hit
> the 1000 record limit. I have an individual formula in each cell and would
> like to convert my formulas to arrays but have not been successful. Does
> the RCHGetElementNumber function work in an array? I̢۪d like to have one
> column with multiple ticker symbols in it and one row with many different
> element numbers listed and use an array to pull in the information. I̢۪ve
> attached an example of what I̢۪m trying to accomplish. Please let me know
> what I̢۪m doing wrong.
>
>
>
Wed Mar 19, 2014 7:02 am (PDT) . Posted by:
lezm001
Thanks, I appreciate the explanation. I'll need to research if some of the other array entered functions available could pull in the information I'm looking for.
Wed Mar 19, 2014 7:53 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
The main choices I know of are:
-- RCHGetYahooQuotes()
-- smfGetCSVFile() on MorningStar CSV files
-- smfGetCSVFile() on FinVIz CSV files
Actually, RCHGetYahooQuotes() is just an enhanced version of
smfGetCSVFile(), designed for a particular data source. There was also a
recent discussion on grabbing the XLS file available from GuruFocus, using
VBA code.
On Wed, Mar 19, 2014 at 7:02 AM, <dlezman@steelcase.com> wrote:
>
> Thanks, I appreciate the explanation. I'll need to research if some of
> the other array entered functions available could pull in the information
> I'm looking for.
>
-- RCHGetYahooQuotes()
-- smfGetCSVFile() on MorningStar CSV files
-- smfGetCSVFile() on FinVIz CSV files
Actually, RCHGetYahooQuotes() is just an enhanced version of
smfGetCSVFile(), designed for a particular data source. There was also a
recent discussion on grabbing the XLS file available from GuruFocus, using
VBA code.
On Wed, Mar 19, 2014 at 7:02 AM, <dlezman@steelcase.com> wrote:
>
> Thanks, I appreciate the explanation. I'll need to research if some of
> the other array entered functions available could pull in the information
> I'm looking for.
>
Wed Mar 19, 2014 9:11 am (PDT) . Posted by:
lezm001
Please give me an example using SMFgetCSVFile with morningstar if I wanted to pull multipel pieces of data from morningstar for mutual fund ticker ATHIX. I'm not sure how to enter this example into a spreadsheet.
thanks
thanks
Wed Mar 19, 2014 9:23 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I think the only CSV file that MorningStar has for mutual funds is for
their holdings:
=smfGetCSVFile("
http://portfolios.morningstar.com/fund/holdingsExport?t=ATHIX&exportType=details
")
On Wed, Mar 19, 2014 at 9:11 AM, <dlezman@steelcase.com> wrote:
> Please give me an example using SMFgetCSVFile with morningstar if I
> wanted to pull multipel pieces of data from morningstar for mutual fund
> ticker ATHIX. I'm not sure how to enter this example into a spreadsheet.
>
their holdings:
=smfGetCSVFile("
http://portfolios.morningstar.com/fund/holdingsExport?t=ATHIX&exportType=details
")
On Wed, Mar 19, 2014 at 9:11 AM, <dlezman@steelcase.com> wrote:
> Please give me an example using SMFgetCSVFile with morningstar if I
> wanted to pull multipel pieces of data from morningstar for mutual fund
> ticker ATHIX. I'm not sure how to enter this example into a spreadsheet.
>
Wed Mar 19, 2014 11:57 am (PDT) . Posted by:
dmatu2000
I know this an odd screener input... Anyway, is anyone aware of a screen that will let me filter by the age of a firm or at least by the time in years that a firm has been publicly traded?
Tidak ada komentar:
Posting Komentar