3 Messages
Digest #3048
Messages
Sun Apr 20, 2014 10:32 am (PDT) . Posted by:
"Kermit W. Prather" kermitpra
Thanks, sorry about asking the same question multiple times. I had forgot all about that.
I was able to get the table into a worksheet. However, I have not been able to parse the data.
For the PCAR example, I can parse the ratio because they are all single digit numbers in the ratio.
But for FTR example and other symbols, the numbers have from 1 to 5 digits And I can't figure out how to do the Left and Right formula.
I am hoping you or someone can show me how to parse the ratio so it does not matter if the digits are 1, 5 or more.
I am ,also, having problems trying to determine what row contains the last date or the first text field. Notice the text in the date column is different for each symbols based on the sector.
Any help is greatly appreciated.
Afraid, I am still a novice when it comes to many excel functions. I can do it in VBA but it is not pretty. So I would prefer an Excel formula.
Thanks,
Kermit
Date
Ratio
<http://www.splithistory.com/?symbol=pcar> 08/01/1988
2 for 1
<http://www.splithistory.com/?symbol=pcar> 05/22/1997
2 for 1
<http://www.splithistory.com/?symbol=pcar> 05/29/2002
3 for 2
<http://www.splithistory.com/?symbol=pcar> 02/06/2004
3 for 2
<http://www.splithistory.com/?symbol=pcar> 08/11/2006
3 for 2
<http://www.splithistory.com/?symbol=pcar> 10/10/2007
3 for 2
<http://www.splithistory.com/?symbol=pcar> Consumer Stock Splits
<i>PCAR is categorized under the Consumer sector; below are some other companies in the same sector that also have a history of stock splits:
FTR
Date
Ratio
<http://www.splithistory.com/?symbol=pcar> 06/20/1994
3 for 2
<http://www.splithistory.com/?symbol=pcar> 06/26/1996
11 for 10
<http://www.splithistory.com/?symbol=pcar> 07/22/1997
2 for 1
<http://www.splithistory.com/?symbol=pcar> 06/29/1998
11 for 10
<http://www.splithistory.com/?symbol=pcar> 03/04/1990
10133 for 10000
<http://www.splithistory.com/?symbol=pcar> 06/04/1990
10133 for 10000
Technology Stock Splits <http://www.splithistory.com/?symbol=pcar>
<i>FTR is categorized under the Technology sector; below are some other companies in the same sector that also have a history of stock splits:</i>
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Saturday, April 19, 2014 6:08 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Need help with RCHGetHTMLTable
Already asked and answered:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/25802
Answer: Not possible because they don't code the table properly.
On Sat, Apr 19, 2014 at 2:41 PM, Kermit W. Prather <kermitp@tampabay.rr.com> wrote:
Randy, I hate to ask but I do the RCHGetHTMLTable so seldom I can't remember how to code it properly.
I'm trying to retrieve the table that has the split history which is at the top right side of the webpage.
The webpage is http://www.splithistory.com/?symbol=pcar
I have been trying for 2 hours to get this done.
The formula I came up with is =RCHGetHTMLTable("&A15&A2","&L1&",-1,"",1)
Where:
URL in cell A15 is http://www.splithistory.com/?symbol=
Symbol in A2 is PCAR
Find Begin is in cell L1 is Split History Table
On the webpage it displays as PCAR Split History Table. The symbol will change depending on cell A2 content.
So I did not include it in the Find begin string.
I built a 2 column array with 20 rows to handle a varying number of splits.
My result is nothing appears in the array. In checking the defined array cells the formula above is in all cells.
Apparently, my formula is incorrect.
Can you tell me what my error is?
I was able to get the table into a worksheet. However, I have not been able to parse the data.
For the PCAR example, I can parse the ratio because they are all single digit numbers in the ratio.
But for FTR example and other symbols, the numbers have from 1 to 5 digits And I can't figure out how to do the Left and Right formula.
I am hoping you or someone can show me how to parse the ratio so it does not matter if the digits are 1, 5 or more.
I am ,also, having problems trying to determine what row contains the last date or the first text field. Notice the text in the date column is different for each symbols based on the sector.
Any help is greatly appreciated.
Afraid, I am still a novice when it comes to many excel functions. I can do it in VBA but it is not pretty. So I would prefer an Excel formula.
Thanks,
Kermit
Date
Ratio
<http://www.splithistory.com/?symbol=pcar> 08/01/1988
2 for 1
<http://www.splithistory.com/?symbol=pcar> 05/22/1997
2 for 1
<http://www.splithistory.com/?symbol=pcar> 05/29/2002
3 for 2
<http://www.splithistory.com/?symbol=pcar> 02/06/2004
3 for 2
<http://www.splithistory.com/?symbol=pcar> 08/11/2006
3 for 2
<http://www.splithistory.com/?symbol=pcar> 10/10/2007
3 for 2
<http://www.splithistory.com/?symbol=pcar> Consumer Stock Splits
<i>PCAR is categorized under the Consumer sector; below are some other companies in the same sector that also have a history of stock splits:
FTR
Date
Ratio
<http://www.splithistory.com/?symbol=pcar> 06/20/1994
3 for 2
<http://www.splithistory.com/?symbol=pcar> 06/26/1996
11 for 10
<http://www.splithistory.com/?symbol=pcar> 07/22/1997
2 for 1
<http://www.splithistory.com/?symbol=pcar> 06/29/1998
11 for 10
<http://www.splithistory.com/?symbol=pcar> 03/04/1990
10133 for 10000
<http://www.splithistory.com/?symbol=pcar> 06/04/1990
10133 for 10000
Technology Stock Splits <http://www.splithistory.com/?symbol=pcar>
<i>FTR is categorized under the Technology sector; below are some other companies in the same sector that also have a history of stock splits:</i>
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Saturday, April 19, 2014 6:08 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Need help with RCHGetHTMLTable
Already asked and answered:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/25802
Answer: Not possible because they don't code the table properly.
On Sat, Apr 19, 2014 at 2:41 PM, Kermit W. Prather <kermitp@tampabay.rr.com> wrote:
Randy, I hate to ask but I do the RCHGetHTMLTable so seldom I can't remember how to code it properly.
I'm trying to retrieve the table that has the split history which is at the top right side of the webpage.
The webpage is http://www.splithistory.com/?symbol=pcar
I have been trying for 2 hours to get this done.
The formula I came up with is =RCHGetHTMLTable("&A15&A2","&L1&",-1,"",1)
Where:
URL in cell A15 is http://www.splithistory.com/?symbol=
Symbol in A2 is PCAR
Find Begin is in cell L1 is Split History Table
On the webpage it displays as PCAR Split History Table. The symbol will change depending on cell A2 content.
So I did not include it in the Find begin string.
I built a 2 column array with 20 rows to handle a varying number of splits.
My result is nothing appears in the array. In checking the defined array cells the formula above is in all cells.
Apparently, my formula is incorrect.
Can you tell me what my error is?
Sun Apr 20, 2014 12:13 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Just use the smfStrExtr() function to break it up into the two pieces:
=smfstrExtr(D5,"~"," for ")/smfstrExtr(D5," for ","~")
...or pick up the two pieces with the smfWord() function:
=smfWord(D5,1," for ")/smfWord(D5,2," for ")
The last date field can be identified with something like:
=IF(D12="--","--",IFERROR(smfGetTagContent("
http://www.splithistory.com/?symbol=pcar","td",C13,">Ratio")/1,"--"))
...where D12 refers to the previous date field, and C13 refers to the
increment (i.e. 1,3,5,7, etc).
On Sun, Apr 20, 2014 at 10:32 AM, Kermit W. Prather <kermitp@tampabay.rr.com
> wrote:
>
> Thanks, sorry about asking the same question multiple times. I had forgot
> all about that.
>
>
>
> I was able to get the table into a worksheet. However, I have not been
> able to parse the data.
>
>
>
> For the PCAR example, I can parse the ratio because they are all single
> digit numbers in the ratio.
>
>
>
> But for FTR example and other symbols, the numbers have from 1 to 5 digits
> And I can't figure out how to do the Left and Right formula.
>
>
>
> I am hoping you or someone can show me how to parse the ratio so it does
> not matter if the digits are 1, 5 or more.
>
>
>
> I am ,also, having problems trying to determine what row contains the last
> date or the first text field. Notice the text in the date column is
> different for each symbols based on the sector.
>
>
>
> Any help is greatly appreciated.
>
>
>
> Afraid, I am still a novice when it comes to many excel functions. I can
> do it in VBA but it is not pretty. So I would prefer an Excel formula.
>
>
>
> Thanks,
>
> Kermit
>
>
>
> Date
>
> Ratio
>
> 08/01/1988 <http://www.splithistory.com/?symbol=pcar>
>
> 2 for 1
>
> 05/22/1997 <http://www.splithistory.com/?symbol=pcar>
>
> 2 for 1
>
> 05/29/2002 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> 02/06/2004 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> 08/11/2006 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> 10/10/2007 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> Consumer Stock Splits <http://www.splithistory.com/?symbol=pcar>
>
> <i>PCAR is categorized under the Consumer sector; below are some other
> companies in the same sector that also have a history of stock splits:
>
>
>
> FTR
>
>
>
> Date
>
> Ratio
>
> 06/20/1994 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> 06/26/1996 <http://www.splithistory.com/?symbol=pcar>
>
> 11 for 10
>
> 07/22/1997 <http://www.splithistory.com/?symbol=pcar>
>
> 2 for 1
>
> 06/29/1998 <http://www.splithistory.com/?symbol=pcar>
>
> 11 for 10
>
> 03/04/1990 <http://www.splithistory.com/?symbol=pcar>
>
> 10133 for 10000
>
> 06/04/1990 <http://www.splithistory.com/?symbol=pcar>
>
> 10133 for 10000
>
> Technology Stock Splits <http://www.splithistory.com/?symbol=pcar>
>
> <i>FTR is categorized under the Technology sector; below are some other
> companies in the same sector that also have a history of stock splits:</i>
>
>
>
>
=smfstrExtr(D5,"~"," for ")/smfstrExtr(D5," for ","~")
...or pick up the two pieces with the smfWord() function:
=smfWord(D5,1," for ")/smfWord(D5,2," for ")
The last date field can be identified with something like:
=IF(D12="--","--",IFERROR(smfGetTagContent("
http://www.splithistory.com/?symbol=pcar","td",C13,">Ratio")/1,"--"))
...where D12 refers to the previous date field, and C13 refers to the
increment (i.e. 1,3,5,7, etc).
On Sun, Apr 20, 2014 at 10:32 AM, Kermit W. Prather <kermitp@tampabay.rr.com
> wrote:
>
> Thanks, sorry about asking the same question multiple times. I had forgot
> all about that.
>
>
>
> I was able to get the table into a worksheet. However, I have not been
> able to parse the data.
>
>
>
> For the PCAR example, I can parse the ratio because they are all single
> digit numbers in the ratio.
>
>
>
> But for FTR example and other symbols, the numbers have from 1 to 5 digits
> And I can't figure out how to do the Left and Right formula.
>
>
>
> I am hoping you or someone can show me how to parse the ratio so it does
> not matter if the digits are 1, 5 or more.
>
>
>
> I am ,also, having problems trying to determine what row contains the last
> date or the first text field. Notice the text in the date column is
> different for each symbols based on the sector.
>
>
>
> Any help is greatly appreciated.
>
>
>
> Afraid, I am still a novice when it comes to many excel functions. I can
> do it in VBA but it is not pretty. So I would prefer an Excel formula.
>
>
>
> Thanks,
>
> Kermit
>
>
>
> Date
>
> Ratio
>
> 08/01/1988 <http://www.splithistory.com/?symbol=pcar>
>
> 2 for 1
>
> 05/22/1997 <http://www.splithistory.com/?symbol=pcar>
>
> 2 for 1
>
> 05/29/2002 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> 02/06/2004 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> 08/11/2006 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> 10/10/2007 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> Consumer Stock Splits <http://www.splithistory.com/?symbol=pcar>
>
> <i>PCAR is categorized under the Consumer sector; below are some other
> companies in the same sector that also have a history of stock splits:
>
>
>
> FTR
>
>
>
> Date
>
> Ratio
>
> 06/20/1994 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> 06/26/1996 <http://www.splithistory.com/?symbol=pcar>
>
> 11 for 10
>
> 07/22/1997 <http://www.splithistory.com/?symbol=pcar>
>
> 2 for 1
>
> 06/29/1998 <http://www.splithistory.com/?symbol=pcar>
>
> 11 for 10
>
> 03/04/1990 <http://www.splithistory.com/?symbol=pcar>
>
> 10133 for 10000
>
> 06/04/1990 <http://www.splithistory.com/?symbol=pcar>
>
> 10133 for 10000
>
> Technology Stock Splits <http://www.splithistory.com/?symbol=pcar>
>
> <i>FTR is categorized under the Technology sector; below are some other
> companies in the same sector that also have a history of stock splits:</
>
>
>
>
Sun Apr 20, 2014 12:14 pm (PDT) . Posted by:
"Randy Randall" randyr_cds
Kermit -
One of the ways to extract the split information is by using the "FIND"
formula as an input to the LEFT (RIGHT) formula. As an example:
=RIGHT($D1,LEN($D1)-FIND(" for ",$D1)-4) (where D1 contains the string "10133
for 10000")
To determine if a cell contains a date, you could use something like
"=ISTEXT(D2)" or =ISNONTEXT(D2)". You could add a helper column that
contains this formula.
Randy
On Sun, Apr 20, 2014 at 1:32 PM, Kermit W. Prather
<kermitp@tampabay.rr.com>wrote:
>
>
> Thanks, sorry about asking the same question multiple times. I had forgot
> all about that.
>
>
>
> I was able to get the table into a worksheet. However, I have not been
> able to parse the data.
>
>
>
> For the PCAR example, I can parse the ratio because they are all single
> digit numbers in the ratio.
>
>
>
> But for FTR example and other symbols, the numbers have from 1 to 5 digits
> And I can't figure out how to do the Left and Right formula.
>
>
>
> I am hoping you or someone can show me how to parse the ratio so it does
> not matter if the digits are 1, 5 or more.
>
>
>
> I am ,also, having problems trying to determine what row contains the last
> date or the first text field. Notice the text in the date column is
> different for each symbols based on the sector.
>
>
>
> Any help is greatly appreciated.
>
>
>
> Afraid, I am still a novice when it comes to many excel functions. I can
> do it in VBA but it is not pretty. So I would prefer an Excel formula.
>
>
>
> Thanks,
>
> Kermit
>
>
>
> Date
>
> Ratio
>
> 08/01/1988 <http://www.splithistory.com/?symbol=pcar>
>
> 2 for 1
>
> 05/22/1997 <http://www.splithistory.com/?symbol=pcar>
>
> 2 for 1
>
> 05/29/2002 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> 02/06/2004 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> 08/11/2006 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> 10/10/2007 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> Consumer Stock Splits <http://www.splithistory.com/?symbol=pcar>
>
> <i>PCAR is categorized under the Consumer sector; below are some other
> companies in the same sector that also have a history of stock splits:
>
>
>
> FTR
>
>
>
> Date
>
> Ratio
>
> 06/20/1994 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> 06/26/1996 <http://www.splithistory.com/?symbol=pcar>
>
> 11 for 10
>
> 07/22/1997 <http://www.splithistory.com/?symbol=pcar>
>
> 2 for 1
>
> 06/29/1998 <http://www.splithistory.com/?symbol=pcar>
>
> 11 for 10
>
> 03/04/1990 <http://www.splithistory.com/?symbol=pcar>
>
> 10133 for 10000
>
> 06/04/1990 <http://www.splithistory.com/?symbol=pcar>
>
> 10133 for 10000
>
> Technology Stock Splits <http://www.splithistory.com/?symbol=pcar>
>
> <i>FTR is categorized under the Technology sector; below are some other
> companies in the same sector that also have a history of stock splits:</i>
>
>
>
>
>
>
> *From:* smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] *On
> Behalf Of *Randy Harmelink
> *Sent:* Saturday, April 19, 2014 6:08 PM
> *To:* smf_addin@yahoogroups.com
> *Subject:* Re: [smf_addin] Need help with RCHGetHTMLTable
>
>
>
>
>
>
> Already asked and answered:
>
> https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/25802
>
> Answer: Not possible because they don't code the table properly.
>
>
>
> On Sat, Apr 19, 2014 at 2:41 PM, Kermit W. Prather <
> kermitp@tampabay.rr.com> wrote:
>
>
>
> Randy, I hate to ask but I do the RCHGetHTMLTable so seldom I can't
> remember how to code it properly.
>
> I'm trying to retrieve the table that has the split history which is at
> the top right side of the webpage.
>
>
>
> The webpage is http://www.splithistory.com/?symbol=pcar
>
>
>
> I have been trying for 2 hours to get this done.
>
>
>
> The formula I came up with is =RCHGetHTMLTable("&A15&A2","&L1&",-1,"",1)
>
> Where:
>
> URL in cell A15 is http://www.splithistory.com/?symbol=
>
> Symbol in A2 is PCAR
>
> Find Begin is in cell L1 is Split History Table
>
>
>
> On the webpage it displays as PCAR Split History Table. The symbol will
> change depending on cell A2 content.
>
> So I did not include it in the Find begin string.
>
>
>
> I built a 2 column array with 20 rows to handle a varying number of
> splits.
>
>
>
> My result is nothing appears in the array. In checking the defined array
> cells the formula above is in all cells.
>
> Apparently, my formula is incorrect.
>
> Can you tell me what my error is?
>
>
>
>
>
>
>
>
>
>
>
>
One of the ways to extract the split information is by using the "FIND"
formula as an input to the LEFT (RIGHT) formula. As an example:
=RIGHT($D1,LEN($D1)-FIND(" for ",$D1)-4) (where D1 contains the string "10133
for 10000")
To determine if a cell contains a date, you could use something like
"=ISTEXT(D2)" or =ISNONTEXT(D2)". You could add a helper column that
contains this formula.
Randy
On Sun, Apr 20, 2014 at 1:32 PM, Kermit W. Prather
<kermitp@tampabay.rr.com>wrote:
>
>
> Thanks, sorry about asking the same question multiple times. I had forgot
> all about that.
>
>
>
> I was able to get the table into a worksheet. However, I have not been
> able to parse the data.
>
>
>
> For the PCAR example, I can parse the ratio because they are all single
> digit numbers in the ratio.
>
>
>
> But for FTR example and other symbols, the numbers have from 1 to 5 digits
> And I can't figure out how to do the Left and Right formula.
>
>
>
> I am hoping you or someone can show me how to parse the ratio so it does
> not matter if the digits are 1, 5 or more.
>
>
>
> I am ,also, having problems trying to determine what row contains the last
> date or the first text field. Notice the text in the date column is
> different for each symbols based on the sector.
>
>
>
> Any help is greatly appreciated.
>
>
>
> Afraid, I am still a novice when it comes to many excel functions. I can
> do it in VBA but it is not pretty. So I would prefer an Excel formula.
>
>
>
> Thanks,
>
> Kermit
>
>
>
> Date
>
> Ratio
>
> 08/01/1988 <http://www.splithistory.com/?symbol=pcar>
>
> 2 for 1
>
> 05/22/1997 <http://www.splithistory.com/?symbol=pcar>
>
> 2 for 1
>
> 05/29/2002 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> 02/06/2004 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> 08/11/2006 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> 10/10/2007 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> Consumer Stock Splits <http://www.splithistory.com/?symbol=pcar>
>
> <i>PCAR is categorized under the Consumer sector; below are some other
> companies in the same sector that also have a history of stock splits:
>
>
>
> FTR
>
>
>
> Date
>
> Ratio
>
> 06/20/1994 <http://www.splithistory.com/?symbol=pcar>
>
> 3 for 2
>
> 06/26/1996 <http://www.splithistory.com/?symbol=pcar>
>
> 11 for 10
>
> 07/22/1997 <http://www.splithistory.com/?symbol=pcar>
>
> 2 for 1
>
> 06/29/1998 <http://www.splithistory.com/?symbol=pcar>
>
> 11 for 10
>
> 03/04/1990 <http://www.splithistory.com/?symbol=pcar>
>
> 10133 for 10000
>
> 06/04/1990 <http://www.splithistory.com/?symbol=pcar>
>
> 10133 for 10000
>
> Technology Stock Splits <http://www.splithistory.com/?symbol=pcar>
>
> <i>FTR is categorized under the Technology sector; below are some other
> companies in the same sector that also have a history of stock splits:</i>
>
>
>
>
>
>
> *From:* smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] *On
> Behalf Of *Randy Harmelink
> *Sent:* Saturday, April 19, 2014 6:08 PM
> *To:* smf_addin@yahoogroups.com
> *Subject:* Re: [smf_addin] Need help with RCHGetHTMLTable
>
>
>
>
>
>
> Already asked and answered:
>
> https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/25802
>
> Answer: Not possible because they don't code the table properly.
>
>
>
> On Sat, Apr 19, 2014 at 2:41 PM, Kermit W. Prather <
> kermitp@tampabay.rr.com> wrote:
>
>
>
> Randy, I hate to ask but I do the RCHGetHTMLTable so seldom I can't
> remember how to code it properly.
>
> I'm trying to retrieve the table that has the split history which is at
> the top right side of the webpage.
>
>
>
> The webpage is http://www.splithistory.com/?symbol=pcar
>
>
>
> I have been trying for 2 hours to get this done.
>
>
>
> The formula I came up with is =RCHGetHTMLTable("&A15&A2","&L1&",-1,"",1)
>
> Where:
>
> URL in cell A15 is http://www.splithistory.com/?symbol=
>
> Symbol in A2 is PCAR
>
> Find Begin is in cell L1 is Split History Table
>
>
>
> On the webpage it displays as PCAR Split History Table. The symbol will
> change depending on cell A2 content.
>
> So I did not include it in the Find begin string.
>
>
>
> I built a 2 column array with 20 rows to handle a varying number of
> splits.
>
>
>
> My result is nothing appears in the array. In checking the defined array
> cells the formula above is in all cells.
>
> Apparently, my formula is incorrect.
>
> Can you tell me what my error is?
>
>
>
>
>
>
>
>
>
>
>
>
Tidak ada komentar:
Posting Komentar