Selasa, 06 Maret 2012

[smf_addin] Digest Number 2138

Messages In This Digest (9 Messages)

Messages

1a.

Help with Morningstar Premium - Portfolio Manager

Posted by: "b.kopacz" bkopacz@gmail.com   b.kopacz

Mon Mar 5, 2012 11:18 am (PST)



I manage a few portfolios using Morningstar's Portfolio Manager. This is both a free and pay-for service that Morningstar offers.
I would like to be able to pull in the portfolio contents into an excel spreadsheet for manipulation.
The site is: https://portfolio.morningstar.com

I am trying to use the GetTable function but am having trouble. Also, when I view the source for the page it does not appear that the portfolio is nested in a table, but my noobieness might be showing there.

Any thoughts on what function I cna use to get my portfolio information into excel?

BK

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
1b.

Re: Help with Morningstar Premium - Portfolio Manager

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Mon Mar 5, 2012 12:37 pm (PST)



The table is dynamically generated, so the add-in won't be able to extract
data from an HTML table -- it doesn't exist when the source code is
delivered.

HOWEVER, the data used to create the table *is* in JavaScript code, so you
could extract the data there.

For example, if I do:

=RCHGetWebData("
http://portfolio.morningstar.com/Rtport/Reg/MyView.aspx?ViewPage=5","var
allData")

...I see this:

var allData = {"B":{"DVS":"[['3M
Co','MMM',86.77,-0.75,-0.86,'','',1000,86770,10.46,,'02-09-2012',0,4],['Consolidated
Edison,
Inc.','ED',58.38,0.07,0.12,'','',1000,58380,7.03,,'01-20-2012',0,2],...

So there is the data for my MMM and ED lines of the portfolio. Something
like this extracts the raw data fairly well for me:

=smfWord(smfWord(RCHGetWebData(…URL...,"var
allData"),…Line...,"["),…Column...,",")

...where "...URL..." is the URL for the M* portfolio page, "...Line..."
varies from 3 to n, and "...Column..." varies from 1 to n:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 *3* '3M Co' 'MMM' 86.77 -0.75 -0.86 ''
'' 1000 86770 10.46 '02-09-2012' 0 4]

On Mon, Mar 5, 2012 at 12:18 PM, b.kopacz <bkopacz@gmail.com> wrote:

> I manage a few portfolios using Morningstar's Portfolio Manager. This is
> both a free and pay-for service that Morningstar offers.
> I would like to be able to pull in the portfolio contents into an excel
> spreadsheet for manipulation.
> The site is: https://portfolio.morningstar.com
>
> I am trying to use the GetTable function but am having trouble. Also,
> when I view the source for the page it does not appear that the portfolio
> is nested in a table, but my noobieness might be showing there.
>
> Any thoughts on what function I cna use to get my portfolio information
> into excel?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
2a.

GetHTMLTable

Posted by: "Steven" stevenletzer@yahoo.com   stevenletzer

Mon Mar 5, 2012 1:38 pm (PST)



At this website:
http://www.bloomberg.com/markets/rates-bonds/government-bonds/us/
I am trying to capture the third of 3 tables on the webpage

In an 8x8 array I entered this formula:
RCHGetHTMLTabe("http://www.bloomberg.com/markets/rates-bonds/government-bonds/us/","Municipal Bonds",1,"",1)

The formula will only capture the U.S. Treasuries table (the first on on the web page).

I tried several variations, none that worked,

Any Ideas?

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)
2b.

Re: GetHTMLTable

Posted by: "Randy H" rharmelink@gmail.com   rharmelink

Mon Mar 5, 2012 2:20 pm (PST)



The problem is your search term -- the phrase "Municipal Bonds" is used
as a description in three places on the web page before it's used as a
header before the table. Try:

=RCHGetHTMLTable("http://www.bloomberg.com/markets/rates-bonds/governmen\
t-bonds/us/",">Municipal Bonds",1,"",1)

The ">" ending portion of the previous HTML tag allows the search
string to ignore casual uses of the search string within descriptions,
titles, and meta tags.

On Mon, Mar 5, 2012 at 2:38 PM, Steven <stevenletzer@yahoo.com> wrote:
At this website:
http://www.bloomberg.com/markets/rates-bonds/government-bonds/us/
<http://www.bloomberg.com/markets/rates-bonds/government-bonds/us/>
I am trying to capture the third of 3 tables on the webpage

In an 8x8 array I entered this formula:
RCHGetHTMLTabe("http://www.bloomberg.com/markets/rates-bonds/government-\
bonds/us/
<http://www.bloomberg.com/markets/rates-bonds/government-bonds/us/>
","Municipal Bonds",1,"",1)

The formula will only capture the U.S. Treasuries table (the first on on
the web page).

I tried several variations, none that worked,

Any Ideas?

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)
3a.

SMF-Morningstar-Holding-Style-Example.xls

Posted by: "kurtboesche@att.net" kurtboesche@att.net   kurtboesche@att.net

Mon Mar 5, 2012 11:26 pm (PST)



Running Windows7 and MS Excel 2003 (11.8342.8341) SP3
When I open this spreadsheet and update the link to the SMF_Addin, all
holdings show as #value!. I'm not exactly sure what the problem is.
The
smfGetTagContent("http://portfolios.morningstar.com/fund/summary?t="&Tic\
ker,"script",1,">Holdings Style") seems to work fine. If I break it out
separately it returns all the necessary information.
The smfStrExtr() I think is what's giving me trouble. When I get to the
highlighted portion of
smfStrExtr(smfGetTagContent("http://portfolios.morningstar.com/fund/summ\
ary?t="&Ticker,"script",1,">Holdings Style"),"Order\"":1","}") I can't
figure out what to do with the lone quote between Order\ and :1. I've
tried enclosing it in single quotes, double quotes and even tried
char(34) and &char(34)& but they were all no goes.
Any thoughts would be appreciated. I could probably nest 4 or 5
smfStrExtr() functions to get the "Holdings" value but I would rather
not as you seemed to have had it working. I should also state that I'm
a little rusty with Excel as I haven't used it much in the last few
years.
I'm a new member btw, and just wanted to say the smf_addin is awesome.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...>
wrote:
>
> I would think you'd be more interested in the scores in the "Holdings
Sytle"
> box here then:
>
> http://portfolios.morningstar.com/fund/summary?t=TEPLX
>
> Take a look at this example I just uploaded to the "Requested Samples"
> folder in the files area:
>
> SMF-Morningstar-Holding-Style-Example.xls
>
> Here's what it shows for TEPLX:
>
> *TEPLX* Holdings Style Large Value Large Value
40%
> Large Blend 30% Large Growth 24% Mid Value 2% Mid
Blend 3%
> Mid Growth 1% Small Value 0% Small Blend 0% Small
Growth
> 0%
> However, IMO, the percentages above SHOULD give the stock a "Large
Blend"
> holdings style. Although "Large Value" has the highest score, it's
obvious
> that the holdings are really a blend of both Value and Growth and not

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (12)
3b.

Re: SMF-Morningstar-Holding-Style-Example.xls

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Mon Mar 5, 2012 11:39 pm (PST)



The two side-by-side double-quotes will evaluate into a single
double-quote, which needs to be there as part of the search string. You
shouldn't need to do anything with them. I just opened up the workbook and
it works fine for me.

It's just trying to extract data from this web page data:

{\"Order\":1,\"BColor\":\"#7c87a7\", \"FColor\":\"#FFFFFF\",\"Label\":\"29
\"}

Could the problem be the ticker symbol you're using? You would get a
#VALUE! error if it can't extract a number, because it divides it by 100 to
convert the integer value into a percentage.

On Tue, Mar 6, 2012 at 12:25 AM, kurtboesche@att.net <kurtboesche@att.net>wrote:

>
> Running Windows7 and MS Excel 2003 (11.8342.8341) SP3
>
> When I open this spreadsheet and update the link to the SMF_Addin, all
> holdings show as #value!. I'm not exactly sure what the problem is.
>
> The smfGetTagContent("http://portfolios.morningstar.com/fund/summary?t="&Ticker,"script",1,">Holdings
> Style") seems to work fine. If I break it out separately it returns all the
> necessary information.
>
> The smfStrExtr() I think is what's giving me trouble. When I get to the
> highlighted portion of smfStrExtr(smfGetTagContent("
> http://portfolios.morningstar.com/fund/summary?t="&Ticker,"script",1,">Holdings
> Style"),"Order\"":1","}") I can't figure out what to do with the lone
> quote between Order\ and :1. I've tried enclosing it in single quotes,
> double quotes and even tried char(34) and &char(34)& but they were all no
> goes.
>
> Any thoughts would be appreciated. I could probably nest 4 or 5
> smfStrExtr() functions to get the "Holdings" value but I would rather not
> as you seemed to have had it working. I should also state that I'm a
> little rusty with Excel as I haven't used it much in the last few years.
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (12)
3c.

Re: SMF-Morningstar-Holding-Style-Example.xls

Posted by: "kurt boesche" kurtboesche@att.net   kurtboesche@att.net

Mon Mar 5, 2012 11:58 pm (PST)



I used the ticker you provided and several other well known funds such as FAIRX,
SEQUX and DODFX and as I said, when I break out the smfGetTagContent() function
on it's own I get valid return data.

When I was troubleshooting this I inserted the smfStrExtr() using the Excel
function wizard and no matter what I tried to enter for the PStart argument it
would give me "invalid" or else wouldn't return any data. I also tried
inserting the smfStrExtr() function manually and typing in the arguments exactly
as you have them but still no go.

>
>From: Randy Harmelink <rharmelink@gmail.com>
>To: smf_addin@yahoogroups.com
>Sent: Tue, March 6, 2012 1:39:52 AM
>Subject: Re: [smf_addin] SMF-Morningstar-Holding-Style-Example.xls
>
>
>The two side-by-side double-quotes will evaluate into a single double-quote,
>which needs to be there as part of the search string. You shouldn't need to do
>anything with them. I just opened up the workbook and it works fine for me.
>
>It's just trying to extract data from this web page data:
>
>{\"Order\":1,\"BColor\":\"#7c87a7\", \"FColor\":\"#FFFFFF\",\"Label\":\"29\"}
>
>Could the problem be the ticker symbol you're using? You would get a #VALUE!
>error if it can't extract a number, because it divides it by 100 to convert the
>integer value into a percentage.
>
>
>On Tue, Mar 6, 2012 at 12:25 AM, kurtboesche@att.net <kurtboesche@att.net>
>wrote:
>
>
>>
>>Running Windows7 and MS Excel 2003 (11.8342.8341) SP3
>>
>>
>>When I open this spreadsheet and update the link to the SMF_Addin, all holdings
>>show as #value!. I'm not exactly sure what the problem is.
>>
>>
>>The
>> smfGetTagContent("http://portfolios.morningstar.com/fund/summary?t="&Ticker,"script",1,">Holdings
>> Style") seems to work fine. If I break it out separately it returns all the
>>necessary information.
>>
>>
>>The smfStrExtr() I think is what's giving me trouble. When I get to the
>>highlighted portion of
>>smfStrExtr(smfGetTagContent("http://portfolios.morningstar.com/fund/summary?t="&Ticker,"script",1,">Holdings
>> Style"),"Order\"":1","}") I can't figure out what to do with the lone quote
>>between Order\ and :1. I've tried enclosing it in single quotes, double quotes
>>and even tried char(34) and &char(34)& but they were all no goes.
>>
>>
>>Any thoughts would be appreciated. I could probably nest 4 or 5 smfStrExtr()
>>functions to get the "Holdings" value but I would rather not as you seemed to
>>have had it working. I should also state that I'm a little rusty with Excel as
>>I haven't used it much in the last few years.
>>
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (12)
3d.

Re: SMF-Morningstar-Holding-Style-Example.xls

Posted by: "Randy H" rharmelink@gmail.com   rharmelink

Tue Mar 6, 2012 12:28 am (PST)



Hmmm. Does this function work:

=smfStrExtr("""FColor\"":\""#FFFFFF\"",\""Label\"":\""29\""}","Label\"":\
\""","\")

It should return 29.

The only thing I can think of is that you're not pairing up your
double-quotes properly. You need one at the start, one at the end, and a
pair of them for each one that is actually within the searched-for
string.

Otherwise, breaking it out should be:

B1:
=smfGetTagContent("http://portfolios.morningstar.com/fund/summary?t=VFIN\
X","script",1,">Holdings Style")
B2: =smfStrExtr(B1,"Order\"":1","}")
B3: =smfStrExtr(B2,"Label\"":\""","\")

All of the double-quotes are the same character -- you're not using
something that displays them as a left double-quote and a right
double-quote, right? EXCEL doesn't allow the left and right
double-quotes to be used as string delimiters.

On Tue, Mar 6, 2012 at 12:58 AM, kurt boesche <kurtboesche@att.net>
wrote:

I used the ticker you provided and several other well known funds such
as FAIRX, SEQUX and DODFX and as I said, when I break out the
smfGetTagContent() function on it's own I get valid return data.
When I was troubleshooting this I inserted the smfStrExtr() using the
Excel function wizard and no matter what I tried to enter for the PStart
argument it would give me "invalid" or else wouldn't return any data. I
also tried inserting the smfStrExtr() function manually and typing in
the arguments exactly as you have them but still no go.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (12)
4a.

Re: Trend Values for http://www.masterdata.com/

Posted by: "smithjhhic" smithjhhic@yahoo.com   smithjhhic

Tue Mar 6, 2012 2:07 am (PST)



Randy,
Thank you very much for your help. I notice for some symbols (ie. EWZ) data returned is "Down&nbsp" vs. "Down". Is that something I'm doing wrong part of the web data? Is there a way to remove the &nbsp?
v/r,
Jeff

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Looks straight-forward to me -- just a slight complication in creating the
> URL. For example, to get the first current trend value if the ticker symbol
> is in cell C4:
>
> =RCHGetTableCell("http://www.masterdata.com/ComponentFrames/"&LEFT(C4,1)&"/"&C4&".htm",1,">Current
> Trend")
>
> ...should get the Daily value. Just change the "1" parameter to 2 thru 4 to
> get the other time periods.
>
> On Sun, Mar 4, 2012 at 2:39 PM, smithjhhic <smithjhhic@...> wrote:
>
> >
> > I would like to utilize the "Current Trend", "Trend Life (Periods)" and
> > "Trend Begin Date" from:
> >
> > http://www.masterdata.com/ComponentFrames/E/EWZ.htm
> > http://www.masterdata.com/ComponentFrames/B/BND.htm
> >
> > My difficulties are:
> > - The url seems to change based on the symbol. For example, the "/E/"
> > with EWZ and the "/B/" with BND. Is there a way to code to take this into
> > account?
> >
> > - Uncertain how to construct the formulas to show the fields listed above
> > (ie. "Current Trend") for the four values "Daily", "Weekly", "Monthly" and
> > "Quarterly".
> >
> > Any help would be greatly appreciated.
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
Recent Activity
Visit Your Group
Ads on Yahoo!

Learn more now.

Reach customers

searching for you.

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! News

Get it all here

Breaking news to

entertainment news

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web
Diposting oleh EXCEL FEED LEARNING di 02.07

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)