EXCEL Stock Market Functions Add-in Group

8 New Messages

Digest #2900

Re: SMF-Template-Zacks-Earnings-Announcements.xls from work in progr by "Jacob Jose" pepecan47

답장: [smf_addin] SMF-Template-Zacks-Earnings-Announcements.x by "이준호" junholeelee

Re: SMF-Template-Zacks-Earnings-Announcements.xls from work in progr by "Randy Harmelink" rharmelink

Re: SMF-Template-Zacks-Earnings-Announcements.xls from work in progr by "Jacob Jose" pepecan47

Re: SMF-Template-Zacks-Earnings-Announcements.xls from work in progr by "Randy Harmelink" rharmelink

Re: Lowest Price by gphillipsuk

TSX listed stocks by altaclip

why am i getting 'error' for zacks rating? by zbgb952

Messages

1a

Re: SMF-Template-Zacks-Earnings-Announcements.xls from work in progr

Fri Dec 20, 2013 8:04 am (PST) . Posted by:

"Jacob Jose" pepecan47

Hi Randy, B4 gives me
#VALUE!

B2 :
window.app_data_earnings =            {             columns : [                    { "mDataProp"   : "Date"                    , "sTitle"      : "Date"                    , "sClass"      : "alpha"                    , "sType"       : "us_date"                    }                    ,                    {                      "mDataProp"   : "Period Ending"                    , "sTitle"      : "Period Ending"                    , "sClass"      : "alpha"                    , "sType"       : "monthYear"                    }                    ,                    {                      "mDataProp"   :
"Estimate";                    , "sTitle"      : "Estimate";                    , "sClass"      : ""                    , "sType"       : "numeric"                    , "sType"       : "numsort"                    }                    ,                    {                      "mDataProp"   : "Reported";                    , "sTitle"      : "Reported";                    , "sClass"      : ""                    , "sType"       : "numeric"                    , "sType"       : "numsort"                    }                    ,                    {                      "mDataProp"   :
"Surprise";                    , "sTitle"      : "Surprise";                    , "sClass"      : ""                    , "sType"       : "numeric"                    , "sType"       : "numsort"                    }                    ,                    {                      "mDataProp"   : "Time"                    , "sTitle"      : "Time"                    , "sClass"      : "report"                    , "sType"       : "alpha"                    , "sType"       : "html"                    , "bSortable"   : false                    }                ]            , "data" : [ { "Date" : "1/30/2014", "Period
Ending" : "12/2013", "Estimate"; : "$1.60", "Reported"; : "--", "Surprise"; :      "--", "Time" : "--" } , { "Date" : "10/24/2013", "Period Ending" : "9/2013", "Estimate"; : "$1.76", "Reported"; : "$1.78", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.02 (1.14%)</div>", "Time" : "Before Open" } , { "Date" : "7/25/2013", "Period Ending" : "6/2013", "Estimate"; : "$1.70", "Reported"; : "$1.71", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.01 (0.59%)</div>", "Time" : "Before Open" } , { "Date" : "4/25/2013", "Period Ending" : "3/2013", "Estimate"; : "$1.65", "Reported"; : "$1.61", "Surprise"; :      "<div class=\"right neg negative neg_icon showinline down\">-0.04 (2.42%)</div>", "Time" : "Before Open" } , { "Date" : "1/24/2013", "Period Ending" : "12/2012", "Estimate"; : "$1.41", "Reported"; : "$1.41", "Surprise"; :      "<div
class=\"right pos_na showinline\">0.00 (0.00%)</div>", "Time" : "Before Open" } , { "Date" : "10/23/2012", "Period Ending" : "9/2012", "Estimate"; : "$1.66", "Reported"; : "$1.65", "Surprise"; :      "<div class=\"right neg negative neg_icon showinline down\">-0.01 (0.60%)</div>", "Time" : "Before Open" } , { "Date" : "7/26/2012", "Period Ending" : "6/2012", "Estimate"; : "$1.65", "Reported"; : "$1.66", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.01 (0.61%)</div>", "Time" : "Before Open" } , { "Date" : "4/24/2012", "Period Ending" : "3/2012", "Estimate"; : "$1.48", "Reported"; : "$1.59", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.11 (7.43%)</div>", "Time" : "Before Open" } , { "Date" : "1/26/2012", "Period Ending" : "12/2011", "Estimate"; : "$1.31", "Reported"; : "$1.35", "Surprise"; :      "<div class=\"right pos positive pos_icon
showinline up\">0.04 (3.05%)</div>", "Time" : "Before Open" } , { "Date" : "10/25/2011", "Period Ending" : "9/2011", "Estimate"; : "$1.61", "Reported"; : "$1.52", "Surprise"; :      "<div class=\"right neg negative neg_icon showinline down\">-0.09 (5.59%)</div>", "Time" : "Before Open" } , { "Date" : "7/26/2011", "Period Ending" : "6/2011", "Estimate"; : "$1.60", "Reported"; : "$1.60", "Surprise"; :      "<div class=\"right pos_na showinline\">0.00 (0.00%)</div>", "Time" : "Before Open" } , { "Date" : "4/26/2011", "Period Ending" : "3/2011", "Estimate"; : "$1.43", "Reported"; : "$1.49", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.06 (4.20%)</div>", "Time" : "Before Open" } , { "Date" : "1/25/2011", "Period Ending" : "12/2010", "Estimate"; : "$1.27", "Reported"; : "$1.27", "Surprise"; :      "<div class=\"right pos_na showinline\">0.00 (0.00%)</div>", "Time" : "Before
Open" } , { "Date" : "10/28/2010", "Period Ending" : "9/2010", "Estimate"; : "$1.51", "Reported"; : "--", "Surprise"; :      "--", "Time" : "Before Open" } , { "Date" : "7/22/2010", "Period Ending" : "6/2010", "Estimate"; : "$1.48", "Reported"; : "--", "Surprise"; :      "--", "Time" : "--" } , { "Date" : "4/27/2010", "Period Ending" : "3/2010", "Estimate"; : "$1.21", "Reported"; : "--", "Surprise"; :      "--", "Time" : "Before Open" } , { "Date" : "1/28/2010", "Period Ending" : "12/2009", "Estimate"; : "$1.21", "Reported"; : "--", "Surprise"; :      "--", "Time" : "Before Open" } , { "Date" : "10/22/2009", "Period Ending" : "9/2009", "Estimate"; : "$1.17", "Reported"; : "--", "Surprise"; :      "--", "Time" : "Before Open" } , { "Date" : "7/23/2009", "Period Ending" : "6/2009", "Estimate"; : "$0.94", "Reported"; : "--", "Surprise"; :      "--", "Time" : "Before Open" }
]                }
B3:
{ "Date" : "1/30/2014", "Period Ending" : "12/2013", "Estimate"; : "$1.60", "Reported"; : "--", "Surprise"; :      "--", "Time" : "--" } , { "Date" : "10/24/2013", "Period Ending" : "9/2013", "Estimate"; : "$1.76", "Reported"; : "$1.78", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.02 (1.14%)</div>", "Time" : "Before Open" } , { "Date" : "7/25/2013", "Period Ending" : "6/2013", "Estimate"; : "$1.70", "Reported"; : "$1.71", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.01 (0.59%)</div>", "Time" : "Before Open" } , { "Date" : "4/25/2013", "Period Ending" : "3/2013", "Estimate"; : "$1.65", "Reported"; : "$1.61", "Surprise"; :      "<div class=\"right neg negative neg_icon showinline down\">-0.04 (2.42%)</div>", "Time" : "Before Open" } , { "Date" : "1/24/2013", "Period Ending" : "12/2012", "Estimate"; : "$1.41", "Reported"; : "$1.41",
"Surprise"; :      "<div class=\"right pos_na showinline\">0.00 (0.00%)</div>", "Time" : "Before Open" } , { "Date" : "10/23/2012", "Period Ending" : "9/2012", "Estimate"; : "$1.66", "Reported"; : "$1.65", "Surprise"; :      "<div class=\"right neg negative neg_icon showinline down\">-0.01 (0.60%)</div>", "Time" : "Before Open" } , { "Date" : "7/26/2012", "Period Ending" : "6/2012", "Estimate"; : "$1.65", "Reported"; : "$1.66", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.01 (0.61%)</div>", "Time" : "Before Open" } , { "Date" : "4/24/2012", "Period Ending" : "3/2012", "Estimate"; : "$1.48", "Reported"; : "$1.59", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.11 (7.43%)</div>", "Time" : "Before Open" } , { "Date" : "1/26/2012", "Period Ending" : "12/2011", "Estimate"; : "$1.31", "Reported"; : "$1.35", "Surprise"; :      "<div
class=\"right pos positive pos_icon showinline up\">0.04 (3.05%)</div>", "Time" : "Before Open" } , { "Date" : "10/25/2011", "Period Ending" : "9/2011", "Estimate"; : "$1.61", "Reported"; : "$1.52", "Surprise"; :      "<div class=\"right neg negative neg_icon showinline down\">-0.09 (5.59%)</div>", "Time" : "Before Open" } , { "Date" : "7/26/2011", "Period Ending" : "6/2011", "Estimate"; : "$1.60", "Reported"; : "$1.60", "Surprise"; :      "<div class=\"right pos_na showinline\">0.00 (0.00%)</div>", "Time" : "Before Open" } , { "Date" : "4/26/2011", "Period Ending" : "3/2011", "Estimate"; : "$1.43", "Reported"; : "$1.49", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.06 (4.20%)</div>", "Time" : "Before Open" } , { "Date" : "1/25/2011", "Period Ending" : "12/2010", "Estimate"; : "$1.27", "Reported"; : "$1.27", "Surprise"; :      "<div class=\"right pos_na showinline\">0.00
(0.00%)</div>", "Time" : "Before Open" } , { "Date" : "10/28/2010", "Period Ending" : "9/2010", "Estimate"; : "$1.51", "Reported"; : "--", "Surprise"; :      "--", "Time" : "Before Open" } , { "Date" : "7/22/2010", "Period Ending" : "6/2010", "Estimate"; : "$1.48", "Reported"; : "--", "Surprise"; :      "--", "Time" : "--" } , { "Date" : "4/27/2010", "Period Ending" : "3/2010", "Estimate"; : "$1.21", "Reported"; : "--", "Surprise"; :      "--", "Time" : "Before Open" } , { "Date" : "1/28/2010", "Period Ending" : "12/2009", "Estimate"; : "$1.21", "Reported"; : "--", "Surprise"; :      "--", "Time" : "Before Open" } , { "Date" : "10/22/2009", "Period Ending" : "9/2009", "Estimate"; : "$1.17", "Reported"; : "--", "Surprise"; :      "--", "Time" : "Before Open" } , { "Date" : "7/23/2009", "Period Ending" : "6/2009", "Estimate"; : "$0.94", "Reported"; : "--", "Surprise"; :      "--",
"Time" : "Before Open" }

B4:
#VALUE!

Regards,
Jose L. Jacob

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Thursday, December 19, 2013 8:34:41 PM
Subject: Re: [smf_addin] SMF-Template-Zacks-Earnings-Announcements.xls from work in progress folder

That's the problem, since that is the definition of element 1290.

The basic extraction part of that formula:

=smfGetTagContent("http://www.zacks.com/stock/research/MMM/earnings-announcements","script",-1,"Period Ending")

...returns a string that is over 5000 characters long. I think that length is a problem for EXCEL 2003.

Where do you get an error with the following cells (these are the individual steps of the formula above):

B2: =smfGetTagContent("http://www.zacks.com/stock/research/MMM/earnings-announcements","script",-1,"Period Ending")

B3: =smfstrExtr(B2,"""data"" : [ ","]")

B4: =smfWord(","&B3,1,"}")

B5: =smfWord(B4,2,",")

B6: =smfstrExtr(B5,": ","~")

B7: =smfstrExtr(B6,"""","""")

I'm suspecting the B2 cell, since that is the long string result.

On Thu, Dec 19, 2013 at 2:38 PM, Jacob Jose <pepecan47@yahoo.ca> wrote:

>
>>> =smfstrExtr(smfstrExtr(smfWord(smfWord(","&smfstrExtr(smfGetTagContent("http://www.zacks.com/stock/research/MMM/earnings-announcements","script",-1,"Period Ending"),"""data"" : [ ","]"),1,"}"),2,","),": ","~"),"""","""")
>
>#VALUE!
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

1b

답장: [smf_addin] SMF-Template-Zacks-Earnings-Announcements.x

Fri Dec 20, 2013 8:08 am (PST) . Posted by:

"이준호" junholeelee

2013년 12월 21일(토요일) 1:04 오전에 Jacob Jose <pepecan47@yahoo.ca>님이 쓴 메시지:

Hi Randy, B4 gives me
#VALUE!

B2 :
window.app_data_earnings =            {             columns : [                    { "mDataProp"   : "Date"                    , "sTitle"      : "Date"                    , "sClass"      : "alpha"                    , "sType"       : "us_date"                    }                    ,                    {                      "mDataProp"   : "Period Ending"                    , "sTitle"      : "Period Ending"                    , "sClass"      : "alpha"                    , "sType"       : "monthYear"                    }                    ,                    {                      "mDataProp"   :
"Estimate";                    , "sTitle"      : "Estimate";                    , "sClass"      : ""                    , "sType"       : "numeric"                    , "sType"       : "numsort"                    }                    ,                    {                      "mDataProp"   : "Reported";                    , "sTitle"      : "Reported";                    , "sClass"      : ""                    , "sType"       : "numeric"                    , "sType"       : "numsort"                    }                    ,                    {                      "mDataProp"   :
"Surprise";                    , "sTitle"      : "Surprise";                    , "sClass"      : ""                    , "sType"       : "numeric"                    , "sType"       : "numsort"                    }                    ,                    {                      "mDataProp"   : "Time"                    , "sTitle"      : "Time"                    , "sClass"      : "report"                    , "sType"       : "alpha"                    , "sType"       : "html"                    , "bSortable"   : false                    }                ]            , "data" : [ { "Date" : "1/30/2014", "Period
Ending" : "12/2013", "Estimate"; : "$1.60", "Reported"; : "--", "Surprise"; :      "--", "Time" : "--" } , { "Date" : "10/24/2013", "Period Ending" : "9/2013", "Estimate"; : "$1.76", "Reported"; : "$1.78", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.02 (1.14%)</div>", "Time" : "Before Open" } , { "Date" : "7/25/2013", "Period Ending" : "6/2013", "Estimate"; : "$1.70", "Reported"; : "$1.71", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.01 (0.59%)</div>", "Time" : "Before Open" } , { "Date" : "4/25/2013", "Period Ending" : "3/2013", "Estimate"; : "$1.65", "Reported"; : "$1.61", "Surprise"; :      "<div class=\"right neg negative neg_icon showinline down\">-0.04 (2.42%)</div>", "Time" : "Before Open" } , { "Date" : "1/24/2013", "Period Ending" : "12/2012", "Estimate"; : "$1.41", "Reported"; : "$1.41", "Surprise"; :      "<div
class=\"right pos_na showinline\">0.00 (0.00%)</div>", "Time" : "Before Open" } , { "Date" : "10/23/2012", "Period Ending" : "9/2012", "Estimate"; : "$1.66", "Reported"; : "$1.65", "Surprise"; :      "<div class=\"right neg negative neg_icon showinline down\">-0.01 (0.60%)</div>", "Time" : "Before Open" } , { "Date" : "7/26/2012", "Period Ending" : "6/2012", "Estimate"; : "$1.65", "Reported"; : "$1.66", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.01 (0.61%)</div>", "Time" : "Before Open" } , { "Date" : "4/24/2012", "Period Ending" : "3/2012", "Estimate"; : "$1.48", "Reported"; : "$1.59", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.11 (7.43%)</div>", "Time" : "Before Open" } , { "Date" : "1/26/2012", "Period Ending" : "12/2011", "Estimate"; : "$1.31", "Reported"; : "$1.35", "Surprise"; :      "<div class=\"right pos positive pos_icon
showinline up\">0.04 (3.05%)</div>", "Time" : "Before Open" } , { "Date" : "10/25/2011", "Period Ending" : "9/2011", "Estimate"; : "$1.61", "Reported"; : "$1.52", "Surprise"; :      "<div class=\"right neg negative neg_icon showinline down\">-0.09 (5.59%)</div>", "Time" : "Before Open" } , { "Date" : "7/26/2011", "Period Ending" : "6/2011", "Estimate"; : "$1.60", "Reported"; : "$1.60", "Surprise"; :      "<div class=\"right pos_na showinline\">0.00 (0.00%)</div>", "Time" : "Before Open" } , { "Date" : "4/26/2011", "Period Ending" : "3/2011", "Estimate"; : "$1.43", "Reported"; : "$1.49", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.06 (4.20%)</div>", "Time" : "Before Open" } , { "Date" : "1/25/2011", "Period Ending" : "12/2010", "Estimate"; : "$1.27", "Reported"; : "$1.27", "Surprise"; :      "<div class=\"right pos_na showinline\">0.00 (0.00%)</div>", "Time" : "Before
Open" } , { "Date" : "10/28/2010", "Period Ending" : "9/2010", "Estimate"; : "$1.51", "Reported"; : "--", "Surprise"; :      "--", "Time" : "Before Open" } , { "Date" : "7/22/2010", "Period Ending" : "6/2010", "Estimate"; : "$1.48", "Reported"; : "--", "Surprise"; :      "--", "Time" : "--" } , { "Date" : "4/27/2010", "Period Ending" : "3/2010", "Estimate"; : "$1.21", "Reported"; : "--", "Surprise"; :      "--", "Time" : "Before Open" } , { "Date" : "1/28/2010", "Period Ending" : "12/2009", "Estimate"; : "$1.21", "Reported"; : "--", "Surprise"; :      "--", "Time" : "Before Open" } , { "Date" : "10/22/2009", "Period Ending" : "9/2009", "Estimate"; : "$1.17", "Reported"; : "--", "Surprise"; :      "--", "Time" : "Before Open" } , { "Date" : "7/23/2009", "Period Ending" : "6/2009", "Estimate"; : "$0.94", "Reported"; : "--", "Surprise"; :      "--", "Time" : "Before Open" }
]                }
B3:
{ "Date" : "1/30/2014", "Period Ending" : "12/2013", "Estimate"; : "$1.60", "Reported"; : "--", "Surprise"; :      "--", "Time" : "--" } , { "Date" : "10/24/2013", "Period Ending" : "9/2013", "Estimate"; : "$1.76", "Reported"; : "$1.78", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.02 (1.14%)</div>", "Time" : "Before Open" } , { "Date" : "7/25/2013", "Period Ending" : "6/2013", "Estimate"; : "$1.70", "Reported"; : "$1.71", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.01 (0.59%)</div>", "Time" : "Before Open" } , { "Date" : "4/25/2013", "Period Ending" : "3/2013", "Estimate"; : "$1.65", "Reported"; : "$1.61", "Surprise"; :      "<div class=\"right neg negative neg_icon showinline down\">-0.04 (2.42%)</div>", "Time" : "Before Open" } , { "Date" : "1/24/2013", "Period Ending" : "12/2012", "Estimate"; : "$1.41", "Reported"; : "$1.41",
"Surprise"; :      "<div class=\"right pos_na showinline\">0.00 (0.00%)</div>", "Time" : "Before Open" } , { "Date" : "10/23/2012", "Period Ending" : "9/2012", "Estimate"; : "$1.66", "Reported"; : "$1.65", "Surprise"; :      "<div class=\"right neg negative neg_icon showinline down\">-0.01 (0.60%)</div>", "Time" : "Before Open" } , { "Date" : "7/26/2012", "Period Ending" : "6/2012", "Estimate"; : "$1.65", "Reported"; : "$1.66", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.01 (0.61%)</div>", "Time" : "Before Open" } , { "Date" : "4/24/2012", "Period Ending" : "3/2012", "Estimate"; : "$1.48", "Reported"; : "$1.59", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.11 (7.43%)</div>", "Time" : "Before Open" } , { "Date" : "1/26/2012", "Period Ending" : "12/2011", "Estimate"; : "$1.31", "Reported"; : "$1.35", "Surprise"; :      "<div
class=\"right pos positive pos_icon showinline up\">0.04 (3.05%)</div>", "Time" : "Before Open" } , { "Date" : "10/25/2011", "Period Ending" : "9/2011", "Estimate"; : "$1.61", "Reported"; : "$1.52", "Surprise"; :      "<div class=\"right neg negative neg_icon showinline down\">-0.09 (5.59%)</div>", "Time" : "Before Open" } , { "Date" : "7/26/2011", "Period Ending" : "6/2011", "Estimate"; : "$1.60", "Reported"; : "$1.60", "Surprise"; :      "<div class=\"right pos_na showinline\">0.00 (0.00%)</div>", "Time" : "Before Open" } , { "Date" : "4/26/2011", "Period Ending" : "3/2011", "Estimate"; : "$1.43", "Reported"; : "$1.49", "Surprise"; :      "<div class=\"right pos positive pos_icon showinline up\">0.06 (4.20%)</div>", "Time" : "Before Open" } , { "Date" : "1/25/2011", "Period Ending" : "12/2010", "Estimate"; : "$1.27", "Reported"; : "$1.27", "Surprise"; :      "<div class=\"right pos_na showinline\">0.00
(0.00%)</div>", "Time" : "Before Open" } , { "Date" : "10/28/2010", "Period Ending" : "9/2010", "Estimate"; : "$1.51", "Reported"; : "--", "Surprise"; :      "--", "Time" : "Before Open" } , { "Date" : "7/22/2010", "Period Ending" : "6/2010", "Estimate"; : "$1.48", "Reported"; : "--", "Surprise"; :      "--", "Time" : "--" } , { "Date" : "4/27/2010", "Period Ending" : "3/2010", "Estimate"; : "$1.21", "Reported"; : "--", "Surprise"; :      "--", "Time" : "Before Open" } , { "Date" : "1/28/2010", "Period Ending" : "12/2009", "Estimate"; : "$1.21", "Reported"; : "--", "Surprise"; :      "--", "Time" : "Before Open" } , { "Date" : "10/22/2009", "Period Ending" : "9/2009", "Estimate"; : "$1.17", "Reported"; : "--", "Surprise"; :      "--", "Time" : "Before Open" } , { "Date" : "7/23/2009", "Period Ending" : "6/2009", "Estimate"; : "$0.94", "Reported"; : "--", "Surprise"; :      "--",
"Time" : "Before Open" }

B4:
#VALUE!

Regards,
Jose L. Jacob

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Thursday, December 19, 2013 8:34:41 PM
Subject: Re: [smf_addin] SMF-Template-Zacks-Earnings-Announcements.xls from work in progress folder

That's the problem, since that is the definition of element 1290.

The basic extraction part of that formula:

=smfGetTagContent("http://www.zacks.com/stock/research/MMM/earnings-announcements","script",-1,"Period Ending")

...returns a string that is over 5000 characters long. I think that length is a problem for EXCEL 2003.

Where do you get an error with the following cells (these are the individual steps of the formula above):

B2: =smfGetTagContent("http://www.zacks.com/stock/research/MMM/earnings-announcements","script",-1,"Period Ending")

B3: =smfstrExtr(B2,"""data"" : [ ","]")

B4: =smfWord(","&B3,1,"}")

B5: =smfWord(B4,2,",")

B6: =smfstrExtr(B5,": ","~")

B7: =smfstrExtr(B6,"""","""")

I'm suspecting the B2 cell, since that is the long string result.

On Thu, Dec 19, 2013 at 2:38 PM, Jacob Jose <pepecan47@yahoo.ca> wrote:

>
>>> =smfstrExtr(smfstrExtr(smfWord(smfWord(","&smfstrExtr(smfGetTagContent("http://www.zacks.com/stock/research/MMM/earnings-announcements","script",-1,"Period Ending"),"""data"" : [ ","]"),1,"}"),2,","),": ","~"),"""","""")
>
>#VALUE!
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

1c

Re: SMF-Template-Zacks-Earnings-Announcements.xls from work in progr

Fri Dec 20, 2013 1:11 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

That result makes no sense to me...I don't know what to tell you at this
point.

Typically, #VALUE at that point means it doesn't like one of the parameter
values passed to the function....

Hmmm. To verify it's a length issue, try this in cell B4 and see if you
still get #VALUE! returned:

=smfWord(","&LEFT(B3,254),1,"}")

If that works, change 254 to 1023, then to 2047, then to 4095. See at what
point #VALUE! shows up.

On Fri, Dec 20, 2013 at 9:04 AM, Jacob Jose <pepecan47@yahoo.ca> wrote:

>
> Hi Randy, B4 gives me
> #VALUE!
>
> *From:* Randy Harmelink <rharmelink@gmail.com>
> *To:* smf_addin@yahoogroups.com
> *Sent:* Thursday, December 19, 2013 8:34:41 PM
> *Subject:* Re: [smf_addin] SMF-Template-Zacks-Earnings-Announcements.xls
> from work in progress folder
>
>
> That's the problem, since that is the definition of element 1290.
>
> The basic extraction part of that formula:
>
> =smfGetTagContent("
> http://www.zacks.com/stock/research/MMM/earnings-announcements","script",-1,"Period
> Ending")
>
> ...returns a string that is over 5000 characters long. I think that length
> is a problem for EXCEL 2003.
>
> Where do you get an error with the following cells (these are the
> individual steps of the formula above):
>
> B2: =smfGetTagContent("
> http://www.zacks.com/stock/research/MMM/earnings-announcements","script",-1,"Period
> Ending")
> B3: =smfstrExtr(B2,"""data"" : [ ","]")
> B4: =smfWord(","&B3,1,"}")
> B5: =smfWord(B4,2,",")
> B6: =smfstrExtr(B5,": ","~")
> B7: =smfstrExtr(B6,"""","""")
>
> I'm suspecting the B2 cell, since that is the long string result.
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

1d

Re: SMF-Template-Zacks-Earnings-Announcements.xls from work in progr

Fri Dec 20, 2013 1:27 pm (PST) . Posted by:

"Jacob Jose" pepecan47

With 254, it works. When I change to 1023 I get #VALUE! returned.

Regards,
Jose L. Jacob

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Friday, December 20, 2013 4:11:19 PM
Subject: Re: [smf_addin] SMF-Template-Zacks-Earnings-Announcements.xls from work in progress folder

That result makes no sense to me...I don't know what to tell you at this point.

Typically, #VALUE at that point means it doesn't like one of the parameter values passed to the function....

Hmmm. To verify it's a length issue, try this in cell B4 and see if you still get #VALUE! returned:

=smfWord(","&LEFT(B3,254),1,"}")

If that works, change 254 to 1023, then to 2047, then to 4095. See at what point #VALUE! shows up.

On Fri, Dec 20, 2013 at 9:04 AM, Jacob Jose <pepecan47@yahoo.ca> wrote:

>
>Hi Randy, B4 gives me
>#VALUE!
>
>
>
>From: Randy Harmelink <rharmelink@gmail.com>
>To: smf_addin@yahoogroups.com
>Sent: Thursday, December 19, 2013 8:34:41 PM
>Subject: Re: [smf_addin] SMF-Template-Zacks-Earnings-Announcements.xls from work in progress folder
>
>
>
>
>That's the problem, since that is the definition of element 1290.
>
>The basic extraction part of that formula:
>
>=smfGetTagContent("http://www.zacks.com/stock/research/MMM/earnings-announcements","script",-1,"Period Ending")
>
>
>
>...returns a string that is over 5000 characters long. I think that length is a problem for EXCEL 2003.
>
>
>Where do you get an error with the following cells (these are the individual steps of the formula above):
>
>
>B2: =smfGetTagContent("http://www.zacks.com/stock/research/MMM/earnings-announcements","script",-1,"Period Ending")
>
>B3: =smfstrExtr(B2,"""data"" : [ ","]")
>
>B4: =smfWord(","&B3,1,"}")
>
>B5: =smfWord(B4,2,",")
>
>B6: =smfstrExtr(B5,": ","~")
>
>B7: =smfstrExtr(B6,"""","""")
>
>
>
>I'm suspecting the B2 cell, since that is the long string result.
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

1e

Re: SMF-Template-Zacks-Earnings-Announcements.xls from work in progr

Fri Dec 20, 2013 2:10 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

That means it's an EXCEL 2003 limitation that is causing the problem...

On Fri, Dec 20, 2013 at 2:27 PM, Jacob Jose <pepecan47@yahoo.ca> wrote:

>
> With 254, it works. When I change to 1023 I get #VALUE! returned.
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

2a

Re: Lowest Price

Fri Dec 20, 2013 12:59 pm (PST) . Posted by:

gphillipsuk

Thank you Randy. This worked!

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

3 TSX listed stocks

Fri Dec 20, 2013 8:25 pm (PST) . Posted by:

altaclip

First I'd like to thank Randy for all the work you do on this amazing tool. Merry Christmas to you and your family!

I continue on my quest to find websites that rchgettablecell or smfgettagcontent work for TSX listed stocks. I've had moderate success but discarded any yahoo elements since many smaller tsx stocks don't have yahoo data for certain fields.

I will share some sites that I've have moderate success. Please share any other sites/formulas that you've found to extra key tsx info. I especially would like to find current and next year estimated earnings, long term est earnings growth, forward p/e, average broker rating.

1. I've had moderate success with ex-dividend date at the official tsx site:
RCHGetTableCell("http://web.tmxmoney.com/quote.php?qm_symbol=~~~~~",1,">Ex-Div Date:")

You can also get dividend, yield, eps, p/e ratio, shares out, market cap, While earnings and broker info info is listed on pages at this site I haven't been able to extra it.
2. Reuters has tsx info too.
RCHGetTableCell("http://www.reuters.com/finance/stocks/overview?symbol=~~~~~"&".TO",1,"dividend:")

You can sub yield, p/e, eps, roe, but again I haven't had luck with much else at this site.

3. Another option for dividend info is smfGetTagContent("http://www.marketwatch.com/investing/Stock/~~~~~?countrycode=CA","p",1,"dividend")
sub in ex dividend date, yield, eps, p/e, market cap

The above formulas as used in a smfupdatedownloadtable.I would love to be able to add earnings estimates, earnings growth, broker ratings, and any other key info like I track from finviz and zacks for US listed stocks.

Thanks!
Alberta Clipper

Reply to sender . Reply to group . Reply via Web Post . All Messages (1) . Top ^

4 why am i getting 'error' for zacks rating?

Fri Dec 20, 2013 11:35 pm (PST) . Posted by:

zbgb952

For all the stock tickers on my spreadsheet, I am getting an error message when I use:
=rchgetelementnumber(F8,848), f8 has the stock ticker. i am not getting an error message when looking at other values other than the Zacks rating. It is currently 11:35 pm on Friday night on the West Coast.