Thanks Randy.

---In smf_addin@yahoogroups.com, <rharmelink@...> wrote:

See: http://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/13927 http://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/13927

On Mon, Nov 11, 2013 at 8:23 AM, <lawrence.leesh@... mailto:lawrence.leesh@...> wrote:

When you mouse over 2014 of http://investing.businessweek.com/research/stocks/earnings/earnings.asp?ticker=MXP:LN http://investing.businessweek.com/research/stocks/earnings/earnings.asp?ticker=MXP:LN
you will see high, low and consensus estimates. Is there a way to extract the info into excel?

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

2a

Re: What am I doing wrong in these formulas

Mon Nov 11, 2013 4:29 pm (PST) . Posted by:

"Kermit W. Prather" kermitpra

I don't see how one can compute the actual profit using adjusted price. I am going to attach the spreadsheet.
Here is what it looks like. I don't see how one determines total profit with adjusted purchase price. Hopefully, you or someone else can show me how it is done.
This stock does not have any stock splits which is why I am using it as an example.

I think I provide enough information, if not let me know what else is needed.

If you open the spreadsheet you will need to adjust the links for the SMF addin.

Thanks,
Kermit

Row
1
Cell E1 contains formula

=
IF(C5> 0,RCHGetYahooHistory(C5,YEAR(E5),MONTH(E5),DAY(E5),YEAR(E5),MONTH(E5),DAY(E5),"d","c",0,0,0)," ")

2
Cell E2 contains formula
=
RCHGetYahooHistory(C5,YEAR(E5),MONTH(E5),DAY(E5),YEAR(E5),MONTH(E5),DAY(E5),"d","c",0,0,0)

3
Column

4
C
E
Shares purchased
Unjusted purchase price
adjusted purchase price
Friday unadjusted close
Friday Adjusted price
Dividend's paid out between dates
No splits on this stock

5
AGNC
Purchase date
6/27/2008
1
17.11
-6.26
21.05
21.05
26.96

6

last date
11/8/2013
100

Can you explain for me how I calculate the total profit and percentage of profit using the adjusted price. Can you do it for 1 share and 100 shares

The way I see it I paid 17.11 to purchase the stock. I was paid 26.96 in dividends. Then I sold on Friday's closing (11/8/2013. so I figure my total profit is the selling price - purchase price plus the received dividends which is ((21.05 - 17.11) + 26.96) = 30.90 the formula is ((SP - PP) = DIV payout)

Using the adjusted price I can not figure out the total profit unless I am missing something. So what would be the formula to calculate total profit using the adjusted price. ?

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Monday, November 11, 2013 6:23 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] What am I doing wrong in these formulas

Comparing adjusted prices *would* give you total return. That's the main point of using them -- so that everything is relative to each other.
In any case, your formulas don't work because 6/29/2008 was not a trading day, so there is no data to retrieve.
smfPricesByDates() fills forward for non-trading days, so you don't need to know which days were trading days or not.

On Mon, Nov 11, 2013 at 3:42 PM, Kermit W. Prather <kermitp@tampabay.rr.com> wrote:

I am trying to retrieve the unadjusted closing price on a given date.
It is returning nothing. I have tried many difference methods to get the unadjusted price.
The reason I need the unadjusted price on the purchase date is in order to calculate the total returns including stock splits and dividends since purchase..

Row
1
Cell E1 contains formula
=
IF(C5> 0,RCHGetYahooHistory(C5,YEAR(E5),MONTH(E5),DAY(E5),YEAR(E5),MONTH(E5),DAY(E5),"d","c",0,0,0)," ")

2
Cell E2 contains formula
=
RCHGetYahooHistory(C5,YEAR(E5),MONTH(E5),DAY(E5),YEAR(E5),MONTH(E5),DAY(E5),"d","c",0,0,0)

3
Column

4
C
E

5
AGNC
6/29/2008

6

Attachments with this message:

1 of 1 File(s)

sample-getprice-using date.xlsx

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

2b

Re: What am I doing wrong in these formulas

Mon Nov 11, 2013 10:06 pm (PST) . Posted by:

rharmelink

Here's what I get on a side-by-side comparison:

Date Activity Amount Purchase
Price Shares
Owned Unadjusted
price As of
Value ROI Adjusted
Prices ROI 2008-06-27 Start -- $17.11 1.0000 $17.11 $17.11 -- $6.26 -- 2008-06-30 Dividend $0.31 $17.11 1.0185 $16.64 $16.95 -1.0% $6.20 -1.0% 2008-09-25 Dividend $1.00 $19.53 1.0734 $18.23 $19.57 14.4% $7.16 14.4% 2008-12-29 Dividend $1.20 $21.60 1.1366 $20.64 $23.46 37.1% $8.58 37.1% 2009-03-31 Dividend $0.85 $17.81 1.1935 $17.11 $20.42 19.4% $7.47 19.3% 2009-06-30 Dividend $1.50 $23.75 1.2740 $22.97 $29.26 71.0% $10.70 70.9% 2009-09-30 Dividend $1.40 $30.28 1.3357 $28.45 $38.00 122.1% $13.90 122.0% 2009-12-29 Dividend $1.40 $28.49 1.4048 $27.09 $38.06 122.4% $13.92 122.4% 2010-03-29 Dividend $1.40 $27.70 1.4795 $26.02 $38.50 125.0% $14.08 124.9% 2010-06-28 Dividend $1.40 $28.97 1.5547 $26.97 $41.93 145.1% $15.33 144.9% 2010-09-24 Dividend $1.40 $29.27 1.6328 $28.19 $46.03 169.0% $16.83 168.8% 2010-12-29 Dividend $1.40 $29.57 1.7139 $28.75 $49.28 188.0% $18.02 187.9% 2011-03-21 Dividend $1.40 $30.67 1.7959 $29.10 $52.26 205.4% $19.11 205.3% 2011-06-21 Dividend $1.40 $29.80 1.8844 $28.50 $53.71 213.9% $19.64 213.7% 2011-09-21 Dividend $1.40 $29.06 1.9798 $27.12 $53.69 213.8% $19.64 213.7% 2011-12-20 Dividend $1.40 $28.96 2.0804 $28.05 $58.35 241.1% $21.34 240.9% 2012-03-05 Dividend $1.25 $31.17 2.1673 $30.28 $65.63 283.6% $24.00 283.4% 2012-06-19 Dividend $1.25 $33.61 2.2510 $32.49 $73.14 327.4% $26.75 327.3% 2012-09-19 Dividend $1.25 $36.40 2.3311 $34.84 $81.21 374.7% $29.70 374.4% 2012-12-24 Dividend $1.25 $31.15 2.4285 $29.92 $72.66 324.7% $26.57 324.4% 2013-03-18 Dividend $1.25 $33.12 2.5238 $31.98 $80.71 371.7% $29.52 371.6% 2013-06-26 Dividend $1.05 $23.21 2.6433 $22.41 $59.24 246.2% $21.66 246.0% 2013-09-26 Dividend $0.80 $24.04 2.7343 $22.90 $62.62 266.0% $22.90 265.8% 2013-11-11 End $20.68 $20.68 2.7343 $20.69 $56.57 230.6% $20.69 230.5%
For example, when the first dividend was paid, you would have been able to reinvest and buy this many shares:

=$0.31/($17.11-$0.31)

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

3a

What am I doing wrong in these formulas [1 Attachment]

Mon Nov 11, 2013 4:38 pm (PST) . Posted by:

"Kermit W. Prather" kermitpra

]

[Attachment(s) from Kermit W. Prather included below]

I don't see how one can compute the actual profit using adjusted price. I am going to attach the spreadsheet.
Here is what it looks like. I don't see how one determines total profit with adjusted purchase price. Hopefully, you or someone else can show me how it is done.
This stock does not have any stock splits which is why I am using it as an example.

I think I provide enough information, if not let me know what else is needed.

If you open the spreadsheet you will need to adjust the links for the SMF addin.

Thanks,
Kermit

Row
1
Cell E1 contains formula

=
IF(C5> 0,RCHGetYahooHistory(C5,YEAR(E5),MONTH(E5),DAY(E5),YEAR(E5),MONTH(E5),DAY(E5),"d","c",0,0,0)," ")

2
Cell E2 contains formula
=
RCHGetYahooHistory(C5,YEAR(E5),MONTH(E5),DAY(E5),YEAR(E5),MONTH(E5),DAY(E5),"d","c",0,0,0)

3
Column

4
C
E
Shares purchased
Unjusted purchase price
adjusted purchase price
Friday unadjusted close
Friday Adjusted price
Dividend's paid out between dates
No splits on this stock

5
AGNC
Purchase date
6/27/2008
1
17.11
-6.26
21.05
21.05
26.96

6

last date
11/8/2013
100

Can you explain for me how I calculate the total profit and percentage of profit using the adjusted price. Can you do it for 1 share and 100 shares

The way I see it I paid 17.11 to purchase the stock. I was paid 26.96 in dividends. Then I sold on Friday's closing (11/8/2013. so I figure my total profit is the selling price - purchase price plus the received dividends which is ((21.05 - 17.11) + 26.96) = 30.90 the formula is ((SP - PP) = DIV payout)

Using the adjusted price I can not figure out the total profit unless I am missing something. So what would be the formula to calculate total profit using the adjusted price. ?

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Monday, November 11, 2013 6:23 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] What am I doing wrong in these formulas

Comparing adjusted prices *would* give you total return. That's the main point of using them -- so that everything is relative to each other.
In any case, your formulas don't work because 6/29/2008 was not a trading day, so there is no data to retrieve.
smfPricesByDates() fills forward for non-trading days, so you don't need to know which days were trading days or not.

On Mon, Nov 11, 2013 at 3:42 PM, Kermit W. Prather <kermitp@tampabay.rr.com> wrote:

I am trying to retrieve the unadjusted closing price on a given date.
It is returning nothing. I have tried many difference methods to get the unadjusted price.
The reason I need the unadjusted price on the purchase date is in order to calculate the total returns including stock splits and dividends since purchase..

Row
1
Cell E1 contains formula
=
IF(C5> 0,RCHGetYahooHistory(C5,YEAR(E5),MONTH(E5),DAY(E5),YEAR(E5),MONTH(E5),DAY(E5),"d","c",0,0,0)," ")

2
Cell E2 contains formula
=
RCHGetYahooHistory(C5,YEAR(E5),MONTH(E5),DAY(E5),YEAR(E5),MONTH(E5),DAY(E5),"d","c",0,0,0)

3
Column

4
C
E

5
AGNC
6/29/2008

6

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

3b

Re: What am I doing wrong in these formulas

Mon Nov 11, 2013 9:20 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Take a look at this file in the "Requested Samples" folder of the FILES
area:

AGNC-Comparison-of-Returns-Adjusted-Prices.xls

On Mon, Nov 11, 2013 at 5:38 PM, Kermit W. Prather
<kermitp@tampabay.rr.com>wrote:

>
> I don't see how one can compute the actual profit using adjusted price. I
> am going to attach the spreadsheet.
>
> Here is what it looks like. I don't see how one determines total profit
> with adjusted purchase price. Hopefully, you or someone else can show me
> how it is done.
>
> This stock does not have any stock splits which is why I am using it as an
> example.
>

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

4a

need help randy, Grab Whole Table

Mon Nov 11, 2013 7:38 pm (PST) . Posted by:

sri.yanto

dear randy, sorry bothering you again :D,

Here i a wanna grab this table from the this web, because i cannot retraive again with common excel retrieve, meanwhile, if i use RCHGetTable, there so many unique character that always changes after retrieve, so most logical, I try to use, RCHgetHTMLtable...i have tried but, it's return blank,

I want to grab table that contain the all target Prices

The Site :
https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu#targetprices https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu#targetprices

then i try to use the Rchgethtml table :
=RCHGetHTMLTable("https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu#targetprices https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu#targetprices","Target Price",-1,"",1)

but it's Return blank

Thanks a lot randy

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

4b

Re: need help randy, Grab Whole Table

Mon Nov 11, 2013 7:51 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

The problem is that the string "Target Price" is all over the web page, not
just in that one table. So you need a better search string.

Instead, try:

=RCHGetHTMLTable("
https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu",">Firm
Name",-1,"",1)

On Mon, Nov 11, 2013 at 8:38 PM, <sri.yanto@yahoo.com> wrote:

>
> Here i a wanna grab this table from the this web, because i cannot
> retraive again with common excel retrieve, meanwhile, if i use RCHGetTable,
> there so many unique character that always changes after retrieve, so most
> logical, I try to use, RCHgetHTMLtable...i have tried but, it's return
> blank,
>
> I want to grab table that contain the all target Prices
>
> The Site :
>
> https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu#targetprices
>
> then i try to use the Rchgethtml table :
> =RCHGetHTMLTable("
> https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu#targetprices
> ","Target Price",-1,"",1)
>
> but it's Return blank
>
>

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

4c

Re: need help randy, Grab Whole Table

Mon Nov 11, 2013 10:37 pm (PST) . Posted by:

sri.yanto

okey randy, that works good..thanks bro

---In smf_addin@yahoogroups.com, <rharmelink@...> wrote:

The problem is that the string "Target Price" is all over the web page, not just in that one table. So you need a better search string.

Instead, try:

=RCHGetHTMLTable("https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu",">Firm Name",-1,"",1)

On Mon, Nov 11, 2013 at 8:38 PM, <sri.yanto@... mailto:sri.yanto@...> wrote:

Here i a wanna grab this table from the this web, because i cannot retraive again with common excel retrieve, meanwhile, if i use RCHGetTable, there so many unique character that always changes after retrieve, so most logical, I try to use, RCHgetHTMLtable...i have tried but, it's return blank,

I want to grab table that contain the all target Prices

The Site :
https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu#targetprices https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu#targetprices

then i try to use the Rchgethtml table :
=RCHGetHTMLTable("https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu#targetprices https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu#targetprices","Target Price",-1,"",1)

but it's Return blank

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

4d

Re: need help randy, Grab Whole Table

Mon Nov 11, 2013 10:55 pm (PST) . Posted by:

sri.yanto

sory bro...but i have one more to ask,

i want to have this RCH become refresh-able to other stock..
i'd use this string..

=RCHGetHTMLTable("https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu&frmStockCode=INTP#targetprices",">Firm Name",-1,"",1)

little bit change form your former string.

but why it return only first row
"
Date Firm Name Suggestion Target Price

, :D thanks you very much randy

---In smf_addin@yahoogroups.com, <rharmelink@...> wrote:

The problem is that the string "Target Price" is all over the web page, not just in that one table. So you need a better search string.

Instead, try:

=RCHGetHTMLTable("https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu",">Firm Name",-1,"",1)

On Mon, Nov 11, 2013 at 8:38 PM, <sri.yanto@... mailto:sri.yanto@...> wrote:

Here i a wanna grab this table from the this web, because i cannot retraive again with common excel retrieve, meanwhile, if i use RCHGetTable, there so many unique character that always changes after retrieve, so most logical, I try to use, RCHgetHTMLtable...i have tried but, it's return blank,

I want to grab table that contain the all target Prices

The Site :
https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu#targetprices https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu#targetprices

then i try to use the Rchgethtml table :
=RCHGetHTMLTable("https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu#targetprices https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu#targetprices","Target Price",-1,"",1)

but it's Return blank

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

4e

Re: need help randy, Grab Whole Table

Mon Nov 11, 2013 11:18 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Just remove the "#targetprices" positioning directive suffix you have on
the URL. MicroSoft's XMLHTTP processing doesn't work well with it. I don't
know why.

On Mon, Nov 11, 2013 at 11:55 PM, <sri.yanto@yahoo.com> wrote:

> i want to have this RCH become refresh-able to other stock..
>
> i'd use this string..
>
> =RCHGetHTMLTable("
> https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu&frmStockCode=INTP#targetprices
> ",">Firm Name",-1,"",1)
>
> little bit change form your former string.
>
> but why it return only first row
>
>

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

4f

Re: need help randy, Grab Whole Table

Mon Nov 11, 2013 11:34 pm (PST) . Posted by:

sri.yanto

he..he.. okey bro, it's work..thanks randy

---In smf_addin@yahoogroups.com, <rharmelink@...> wrote:

Just remove the "#targetprices" positioning directive suffix you have on the URL. MicroSoft's XMLHTTP processing doesn't work well with it. I don't know why.

On Mon, Nov 11, 2013 at 11:55 PM, <sri.yanto@... mailto:sri.yanto@...> wrote:
i want to have this RCH become refresh-able to other stock.. i'd use this string..
=RCHGetHTMLTable("https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu&frmStockCode=INTP#targetprices https://www.ipotnews.com/index.php?level2=marketdata&menu=stockdetilmenu&frmStockCode=INTP#targetprices",">Firm Name",-1,"",1)
little bit change form your former string.
but why it return only first row

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

5a

How to grab financials from either google finance or yahoo finance?

Mon Nov 11, 2013 9:21 pm (PST) . Posted by:

chaingangritesh_1210

How shall i grab atleast 5 year and 6 quarterly financials from either yahoo/google finance?

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

5b

Re: How to grab financials from either google finance or yahoo finan

Mon Nov 11, 2013 9:27 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Use the three templates from the FILES area of the groups. For example:

SMF-Template-Google-Balance-Sheet-Tables.xls

However, Google doesn't always have 5 years and 6 quarters.

On Mon, Nov 11, 2013 at 10:11 PM, <ritesh.bhagwati@gmail.com> wrote:

>
> How shall i grab atleast 5 year and 6 quarterly financials from either
> yahoo/google finance?
>