Kamis, 23 Oktober 2014

[smf_addin] Digest Number 3216[1 Attachment]

11 Messages

Digest #3216
1a
Re: 10-year Treasury rate by "Craig Passow" passow
2a
incorrect price by "Darryl Bonfessuto"
2b
Re: incorrect price [1 Attachment] by "Randy Randall" randyr_cds
2c
Re: incorrect price [1 Attachment] by "Randy Harmelink" rharmelink
2d
Re: incorrect price by "Darryl Bonfessuto"
2e
Re: incorrect price by "Darryl Bonfessuto"
3b
Re: Morningstar Upside Downside Capture by "Randy Harmelink" rharmelink
4a
Re: MSN data by tzewei_79
4b
Re: MSN data by "Randy Harmelink" rharmelink
5a
Re: Historical data of market corrections by "Randy Harmelink" rharmelink

Messages

Wed Oct 22, 2014 5:27 am (PDT) . Posted by:

"Craig Passow" passow

Yahoo tickers (quotes and historical data):
^IRX 13-week
^FVX 5-year
^TNX 10-year
^TYX 30-year

On 10/21/2014 11:45 PM, Randy Harmelink rharmelink@gmail.com [smf_addin]
wrote:
> You can grab the current rate from Yahoo's Bonds page:
>
> =RCHGetTableCell("http://finance.yahoo.com/bonds",1,">10 Year")
>
> On Tue, Oct 21, 2014 at 8:49 PM, Jose Jacob pepecan47@... wrote:
>
> Does anyone know a source for the 10-year Treasury rate?
>
>
>

Wed Oct 22, 2014 4:19 pm (PDT) . Posted by:

"Darryl Bonfessuto"

Randy

The returns are not correct. In the TLT example 120.67/101.86-1= 18.46%. I
am getting 21.17% as when I check on what smf is pulling on 12/31/2013 its
99.59 which explains the 21.17% 120.67/99.59-1= 21.17%. All cells are
incorrect. What am I doing incorrect?

Thanks Darryl

Attachment(s) from Darryl Bonfessuto
1 of 1 File(s)

Wed Oct 22, 2014 5:00 pm (PDT) . Posted by:

"Randy Randall" randyr_cds

Yahoo adjusts the historical prices to account for splits, distributions,
etc. TLT has paid out a couple of bucks in distributions since the
beginning of the year. The 99.59 is the adjusted price that Yahoo
calculates.

Do you think the closing price should be adjusted based on
dividends/splits/distributions?

Randy Randall

On Wed, Oct 22, 2014 at 7:19 PM, Darryl Bonfessuto dmb7111@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:

>
> [Attachment(s) <#1493a2894e01ad49_TopText> from Darryl Bonfessuto
> included below]
>
> Randy
>
> The returns are not correct. In the TLT example 120.67/101.86-1= 18.46%. I
> am getting 21.17% as when I check on what smf is pulling on 12/31/2013 its
> 99.59 which explains the 21.17% 120.67/99.59-1= 21.17%. All cells are
> incorrect. What am I doing incorrect?
>
>
>
> Thanks Darryl
>
>
>
>
>
>
>

Wed Oct 22, 2014 5:02 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The returns are correct. smfPricesByDates() returns adjusted closing
prices, which are what you want if you are calculating total returns. Total
returns need to include the payments of dividends.

By the way, you're retrieving the data very inefficiently.

For example, you just need one array-entered formula across cells O34:U34:

=N34/(smfPricesByDates(L34,O3:U3))-1

That will do all seven dates in one Internet access. The way you have it
set up, it would need to do seven individual Internet accesses to get the
same data.

On Wed, Oct 22, 2014 at 4:19 PM, Darryl Bonfessuto dmb7111@... wrote:

>
> The returns are not correct. In the TLT example 120.67/101.86-1= 18.46%. I
> am getting 21.17% as when I check on what smf is pulling on 12/31/2013 its
> 99.59 which explains the 21.17% 120.67/99.59-1= 21.17%. All cells are
> incorrect. What am I doing incorrect?
>
>
>

Wed Oct 22, 2014 5:57 pm (PDT) . Posted by:

"Darryl Bonfessuto"

Thanks Randy

I appreciate the information and help,Yes I do for a Total return calculations, but would like separate the returns.

Darryl

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Wednesday, October 22, 2014 8:01 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] incorrect price

Yahoo adjusts the historical prices to account for splits, distributions, etc. TLT has paid out a couple of bucks in distributions since the beginning of the year. The 99.59 is the adjusted price that Yahoo calculates.

Do you think the closing price should be adjusted based on dividends/splits/distributions?

Randy Randall

On Wed, Oct 22, 2014 at 7:19 PM, Darryl Bonfessuto dmb7111@gmail.com <mailto:dmb7111@gmail.com> [smf_addin] <smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> > wrote:

[Attachment(s) from Darryl Bonfessuto included below]

Randy

The returns are not correct. In the TLT example 120.67/101.86-1= 18.46%. I am getting 21.17% as when I check on what smf is pulling on 12/31/2013 its 99.59 which explains the 21.17% 120.67/99.59-1= 21.17%. All cells are incorrect. What am I doing incorrect?

Thanks Darryl

Wed Oct 22, 2014 6:07 pm (PDT) . Posted by:

"Darryl Bonfessuto"

Thank You

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Wednesday, October 22, 2014 8:03 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] incorrect price

The returns are correct. smfPricesByDates() returns adjusted closing prices, which are what you want if you are calculating total returns. Total returns need to include the payments of dividends.

By the way, you're retrieving the data very inefficiently.

For example, you just need one array-entered formula across cells O34:U34:

=N34/(smfPricesByDates(L34,O3:U3))-1

That will do all seven dates in one Internet access. The way you have it set up, it would need to do seven individual Internet accesses to get the same data.

On Wed, Oct 22, 2014 at 4:19 PM, Darryl Bonfessuto dmb7111@... wrote:

The returns are not correct. In the TLT example 120.67/101.86-1= 18.46%. I am getting 21.17% as when I check on what smf is pulling on 12/31/2013 its 99.59 which explains the 21.17% 120.67/99.59-1= 21.17%. All cells are incorrect. What am I doing incorrect?

Wed Oct 22, 2014 4:36 pm (PDT) . Posted by:

joelfp2000

Randy,

I am trying to determine the upside / downside capture ratios using the Morningstar website. I am currently using the following formula to obtain other information from Morningstar but I can't for the life of me determine how to grab information behind the "ratings and risk" tab. Any ideas?


=RCHGetTableCell("http://portfolios.morningstar.com/fund/summary?t="&$A2,1,"Average Weighted Price")


Thanks in advance for your help and I love being the benefactor of all of your hard work!



Joel

Wed Oct 22, 2014 5:16 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The "Ratings & Risk" tab is a dynamically generated web page, so data
cannot be extracted from it using the add-in. However, MorningStar builds
that web page from other static web pages, which the add-in can extract
data from. In this case, the Upside / Downside ratios are on a page such as:

http://performance.morningstar.com/RatingRisk/fund/updownside-capture.action?t=VFINX

To get the first items for VFINX, I'd do:

=smfConvertData(smfstrExtr(smfGetTagContent("
http://performance.morningstar.com/RatingRisk/fund/updownside-capture.action?t=VFINX
","td",1,">VFINX"),"~","<"))

=smfConvertData(smfstrExtr(smfGetTagContent("
http://performance.morningstar.com/RatingRisk/fund/updownside-capture.action?t=VFINX
","td",1,">VFINX"),"/>","<"))

On Wed, Oct 22, 2014 at 4:36 PM, joel.andrews@lpl.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> I am trying to determine the upside / downside capture ratios using the
> Morningstar website. I am currently using the following formula to obtain
> other information from Morningstar but I can't for the life of me determine
> how to grab information behind the "ratings and risk" tab. Any ideas?
>
> =RCHGetTableCell("http://portfolios.morningstar.com/fund/summary?t="&$A2,1,"Average
> Weighted Price")
>
> Thanks in advance for your help and I love being the benefactor of all of
> your hard work!
>
>

Thu Oct 23, 2014 1:59 am (PDT) . Posted by:

tzewei_79

Hi Guys....

I have not look at the table for a long time and today I realized we have this problem because MSN changed the whole design.


Before I start to find some other sources, I have a big matrix using I am using Excel 2007. I'm using this function using examples like: "=RCHGetTableCell("http://investing.money.msn.com/investments/company-report?symbol="&A13,1,"Industry:")". I have a few hundred cells using this function with different references. I think this is the causing excel to pop up the Windows Security Warnings saying "This page is accessing information that is no t under its control. This poses a security risk. Do you want to continue?" When I clicked either Yes or No, it will still pop up the next warning and I can't work with my excel to change the invalid cells now. Anyone experience this before? What did you do to stop this?



Thu Oct 23, 2014 2:06 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

The web page you're using no longer exists. It just redirects you to the
main MSN web page. It is probably those redirections that are causing the
messages to appear.

On Thu, Oct 23, 2014 at 1:59 AM, tzewei_79@...wrote:

>
> I have not look at the table for a long time and today I realized we have
> this problem because MSN changed the whole design.
>
> Before I start to find some other sources, I have a big matrix using I am
> using Excel 2007. I'm using this function using examples like:
> "=RCHGetTableCell("
> http://investing.money.msn.com/investments/company-report?symbol="&A13,1,"Industry:")".
> I have a few hundred cells using this function with different references. I
> think this is the causing excel to pop up the Windows Security Warnings
> saying "This page is accessing information that is no t under its control.
> This poses a security risk. Do you want to continue?" When I clicked either
> Yes or No, it will still pop up the next warning and I can't work with my
> excel to change the invalid cells now. Anyone experience this before? What
> did you do to stop this?
>

Thu Oct 23, 2014 2:16 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Not sure if this is what you're after, but if the prices are in cells
H4:H4573, this should tell you the row of the first price that is 5% above
what is in cell H4753:

=MAX(IF($H$4:$H4752>1.05*$H4753,ROW($H$4:$H4752),0))

Likewise for the first price under 95% of cell H4753:

=MAX(IF($H$4:$H4752<0.95*$H4753,ROW($H$4:$H4752),0))

I think when I attempted something like this, I had to choose a direction
at the start -- either bull or bear -- so I could work from there. You
could use the above formulas to find out whether the first move from the
start is 5% up or 5% down.

In a bull trend, ratchet the price up from the 105% mark, up to a point
where the nearest 5% drop occurs from that ratcheted price.

On Tue, Oct 21, 2014 at 5:18 PM, steven_rourk@...wrote:

> An Excel related question, not Addin related... sorry, dont know any
> where else to ask.
>
> So Im trying to set up an Excel sheet where I have a column with the daily
> closing price of S&P500 for the last 10 years. I want to determine all the
> market corrections (5% ups and downs from the peaks and the bottoms) with a
> moving formula. The formula should do the following:
>
> 1. validate a 5% or more move from a starting point to validate a trend
>
> 2. if for example an upward trend is validated then it has to determine
> which is the top, as this will be the new starting point to determine a
> downward trend.
>
> 3. With the new starting point validate a 5% move downward to validate
> the trend, once validated, determine which is the bottom,
>
> 4. Calculate the % difference between the top and bottom
>
> 5. Etc
>
> Its easy with a chart, you just have to look at the ups and downs, and
> determine whether there has been a larger than 5% move up or down to
> validate the trend. But Im having problems determining the right formula to
> use in Excel in order to do this automatically. Im attaching the
> spreadsheet. As you can see I started doing it manually (column F and G).
> You can see I´ve been playing around with different formulas without luck
> (columns H, I, J and K). Any help of suggestion will be much appreciated.
>
>
>
> PD: I cant attach the file...
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 02.29

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)