3 New Messages
Digest #2843
Messages
Tue Nov 5, 2013 7:41 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
When at the add-in manager (not the list of installed add-ins, which is
where I think you are), you need to use the "Browse" function to find what
you've unzipped and add it to the Add-in manager. In EXCEL 2007, there's a
"Go" button at the bottom of the list I think you're looking at, to GO to
the add-in manager -- which is what allows you to add (or remove) an add-in.
BTW, the keyboard shortcut for the add-in manager is alt+t+i, at lease for
EXCEL 2007.
On Tue, Nov 5, 2013 at 8:18 PM, gfoster07k@sbcglobal.net <
gfoster07k@sbcglobal.net> wrote:
> Sorry but I still am unable to make this work. I want to add in
> RCHGetYaHooQuotes. I downloaded to my Download Directory and extracted to
> Program Files-SMF Add in. That directory now contains:
> RCH_Stock_Market_Functions 12/29/2012 Microsoft Add in 462kb
> RCHGetElementNumber
> smf-elements - 0
> smf-elements - 1
>
> through
>
> smf-elements - 9
>
> If I start Excel with a new spreadsheet, got to File - Options - Addin the
> Add in Manager does not include any RCH add ins to select. Where an I
> going wrong?
>
where I think you are), you need to use the "Browse" function to find what
you've unzipped and add it to the Add-in manager. In EXCEL 2007, there's a
"Go" button at the bottom of the list I think you're looking at, to GO to
the add-in manager -- which is what allows you to add (or remove) an add-in.
BTW, the keyboard shortcut for the add-in manager is alt+t+i, at lease for
EXCEL 2007.
On Tue, Nov 5, 2013 at 8:18 PM, gfoster07k@sbcgloba
gfoster07k@sbcgloba
> Sorry but I still am unable to make this work. I want to add in
> RCHGetYaHooQuotes. I downloaded to my Download Directory and extracted to
> Program Files-SMF Add in. That directory now contains:
> RCH_Stock_Market_
> RCHGetElementNumber
> smf-elements - 0
> smf-elements - 1
>
> through
>
> smf-elements - 9
>
> If I start Excel with a new spreadsheet, got to File - Options - Addin the
> Add in Manager does not include any RCH add ins to select. Where an I
> going wrong?
>
Tue Nov 5, 2013 7:44 pm (PST) . Posted by:
"Jim Ranum" amt2100
Wow. Thanks!
I'm going to have to work on this a while to figure what you just gave me.
J
Thanks again Randy.
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Tuesday, November 05, 2013 10:30 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Solution for a #VALUE return in a formula
You can use negative numbers for "pCells" in the RCHGetTableCell() function. That tells the function to count backwards from the end of table row. So, to use examples with less than 10 years, 10 years, and less than 5 quarters:
pCells
ALN
ALN
MMM
MMM
VJET
VJET
-1
--
--
--
--
--
--
-2
--
--
--
--
--
--
-3
--
--
--
--
--
--
-4
Jun13
2.5
Sep13
1230
Jun13
-0.52
-5
Mar13
2.52
Jun13
1197
Dec12
-0.15
-6
Dec12
7.49
Mar13
1129
Jun12
0.44
-7
Sep12
5.61
Dec12
991
TTM
-0.66
-8
Jun12
3.33
Sep12
1161
Dec12
0.29
-9
TTM
18.12
TTM
4547
Dec11
0.06
-10
Dec12
20.42
Dec12
4444
Dec10
-0.28
-11
Dec11
19.89
Dec11
4283
Fiscal Period
--
-12
Dec10
17.84
Dec10
4085
Error
--
-13
Dec09
14.41
Dec09
3193
Error
--
-14
Dec08
14.7
Dec08
3460
Error
--
-15
Dec07
9.75
Dec07
4096
Error
--
-16
Dec06
5.93
Dec06
3851
Error
--
-17
Dec05
4.22
Dec05
3111
Error
--
-18
Fiscal Period
--
Dec04
2841
Error
--
-19
Error
--
Dec03
2403
Error
--
-20
Error
--
Fiscal Period
--
Error
--
-21
Error
--
Error
--
Error
--
Note that the most recent fiscal quarter always starts at a -4 for pCells. And if you look up where the "TTM" lies within the "Fiscal Period" headings, you can start your fiscal years based on its location. So, just retrieve the array of fiscal period headers first, look for the "TTM" header, and you'll know where fiscal quarters end and fiscal years start.
The basic formulas I'm using:
=RCHGetTableCell("http://www.gurufocus.com/financials/"&D$4,$C5,">Fiscal Period")
=IFERROR(RCHGetTableCell("http://www.gurufocus.com/financials/"&D$4,$C5,">Fiscal Period",">Net Income<")/1,"--")
The last three table cells of each row are used for some type of formatting, so no data is in them.
On Tue, Nov 5, 2013 at 7:39 PM, Jim Ranum <amt2100@gmail.com> wrote:
Thank you very much Randy.
That was amazingly fast response.
It turns out this problem is a little more complex than I originally thought. I figured out how to modify the formulas so that I could make a simple entry and change where the cells look for the latest year column, but when I run out of years/columns, instead of giving me a null result, I will always get at least one result that is the title of the column. Which isn't exactly an error, but still gives a #VALUE result in the Excel formula involving that cell.
For example ticker: ALN only has 5 years of data, so my 6th column will give a result of "Net Income", because that is the "zero" result. I can't come up with a creative way to avoid that.
=RCHGetTableCell("http://www.gurufocus.com/financials/"&F43,(5-F44),">Fiscal Period",">Net Income<")
Where in this case "5-F44" => 5 is the number for the data column and F44 is the number of years of data missing from the normal 10 years. It works great until I get to the "5-5" year, and then I get "Net Income" for the result, which screws up my formulas.
Any ideas on this one?
Thanks Randy. You provide amazing support.
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Tuesday, November 05, 2013 9:10 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Solution for a #VALUE return in a formula
From 2007 on, to assign a 0 if the "regular formula" has an error:
=IFERROR(....regular formula....,0)
=IFERROR(....regular formula....,"--")
Otherwise:
=IF(ISERR(....regular formula....),0,....regular formula....)
=IF(ISERR(....regular formula....),"--",....regular formula....)
Not as convenient since the "regular formula" has to be entered twice.
On Tue, Nov 5, 2013 at 6:58 PM, Jim Ranum <amt2100@gmail.com> wrote:
Does anyone know how to write the formula to have the cell in Excel give a zero or an empty entry in the cell instead of an #VALUE result if there is no data?
I am pulling data from the gurufocus website and taking the results and using them in formulas. I would like to not have the entire formula crash just because one part of the data is missing, which is what happens if any part of the formula has #VALUE in it.
This happens on specific tickers that are less than 10 years old and the normal number of columns are not present on the Income/Balance sheet.
I'm going to have to work on this a while to figure what you just gave me.
J
Thanks again Randy.
Jim
From: smf_addin@yahoogrou
Sent: Tuesday, November 05, 2013 10:30 PM
To: smf_addin@yahoogrou
Subject: Re: [smf_addin] Solution for a #VALUE return in a formula
You can use negative numbers for "pCells" in the RCHGetTableCell(
pCells
ALN
ALN
MMM
MMM
VJET
VJET
-1
--
--
--
--
--
--
-2
--
--
--
--
--
--
-3
--
--
--
--
--
--
-4
Jun13
2.5
Sep13
1230
Jun13
-0.52
-5
Mar13
2.52
Jun13
1197
Dec12
-0.15
-6
Dec12
7.49
Mar13
1129
Jun12
0.44
-7
Sep12
5.61
Dec12
991
TTM
-0.66
-8
Jun12
3.33
Sep12
1161
Dec12
0.29
-9
TTM
18.12
TTM
4547
Dec11
0.06
-10
Dec12
20.42
Dec12
4444
Dec10
-0.28
-11
Dec11
19.89
Dec11
4283
Fiscal Period
--
-12
Dec10
17.84
Dec10
4085
Error
--
-13
Dec09
14.41
Dec09
3193
Error
--
-14
Dec08
14.7
Dec08
3460
Error
--
-15
Dec07
9.75
Dec07
4096
Error
--
-16
Dec06
5.93
Dec06
3851
Error
--
-17
Dec05
4.22
Dec05
3111
Error
--
-18
Fiscal Period
--
Dec04
2841
Error
--
-19
Error
--
Dec03
2403
Error
--
-20
Error
--
Fiscal Period
--
Error
--
-21
Error
--
Error
--
Error
--
Note that the most recent fiscal quarter always starts at a -4 for pCells. And if you look up where the "TTM" lies within the "Fiscal Period" headings, you can start your fiscal years based on its location. So, just retrieve the array of fiscal period headers first, look for the "TTM" header, and you'll know where fiscal quarters end and fiscal years start.
The basic formulas I'm using:
=RCHGetTableCell(
=IFERROR(RCHGetTabl
The last three table cells of each row are used for some type of formatting, so no data is in them.
On Tue, Nov 5, 2013 at 7:39 PM, Jim Ranum <amt2100@gmail.
Thank you very much Randy.
That was amazingly fast response.
It turns out this problem is a little more complex than I originally thought. I figured out how to modify the formulas so that I could make a simple entry and change where the cells look for the latest year column, but when I run out of years/columns, instead of giving me a null result, I will always get at least one result that is the title of the column. Which isn't exactly an error, but still gives a #VALUE result in the Excel formula involving that cell.
For example ticker: ALN only has 5 years of data, so my 6th column will give a result of "Net Income", because that is the "zero" result. I can't come up with a creative way to avoid that.
=RCHGetTableCell(
Where in this case "5-F44" => 5 is the number for the data column and F44 is the number of years of data missing from the normal 10 years. It works great until I get to the "5-5" year, and then I get "Net Income" for the result, which screws up my formulas.
Any ideas on this one?
Thanks Randy. You provide amazing support.
Jim
From: smf_addin@yahoogrou
Sent: Tuesday, November 05, 2013 9:10 PM
To: smf_addin@yahoogrou
Subject: Re: [smf_addin] Solution for a #VALUE return in a formula
From 2007 on, to assign a 0 if the "regular formula" has an error:
=IFERROR(...
=IFERROR(...
Otherwise:
=IF(ISERR(..
=IF(ISERR(..
Not as convenient since the "regular formula" has to be entered twice.
On Tue, Nov 5, 2013 at 6:58 PM, Jim Ranum <amt2100@gmail.
Does anyone know how to write the formula to have the cell in Excel give a zero or an empty entry in the cell instead of an #VALUE result if there is no data?
I am pulling data from the gurufocus website and taking the results and using them in formulas. I would like to not have the entire formula crash just because one part of the data is missing, which is what happens if any part of the formula has #VALUE in it.
This happens on specific tickers that are less than 10 years old and the normal number of columns are not present on the Income/Balance sheet.
Tue Nov 5, 2013 11:56 pm (PST) . Posted by:
wongkayau
Hi leosh03,
I am encourtering the same problem when grabing data from sina. Did you find out the solution finally?
Thank you very much.
Cheers,
Laurent
---In smf_addin@yahoogroups.com, <leosh03@...> wrote:
when I use RCHGetTableCell to get data from a chinese website, the
RCHGetURLData only returns many "?????", please help, thanks in advance
I am encourtering the same problem when grabing data from sina. Did you find out the solution finally?
Thank you very much.
Cheers,
Laurent
---In smf_addin@yahoogrou
when I use RCHGetTableCell to get data from a chinese website, the
RCHGetURLData only returns many "?????"
Tidak ada komentar:
Posting Komentar