Rabu, 09 Mei 2012

[smf_addin] Digest Number 2205

Messages In This Digest (12 Messages)

Messages

1a.

Re: Problems looking up MSN data

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

Tue May 8, 2012 12:23 pm (PDT)



Randy, i got the new file aswel, but still found some numbers not working:

RCHGetElementNumber(Ticker;283)
to
RCHGetElementNumber(Ticker;292)

I think this link is changes by msn to?

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> What do you get with:
>
> =RCHGetElementNumber("Version")
> =smfGetAParms(1)
> =smfGetAParms(13862)
> =RCHGetElementNumber("MMM",13862)
> =TRIM(smfStrExtr("|"&smfGetTagContent("
> http://investing.money.msn.com/investments/stock-report?CR=1&AF=1&IH=1&AIE=1&AIR=1&FRH=1&FRK=1&ISA=1&ISQ=1&BSA=1&BSQ=1&CFA=1&CFQ=1&TYS=1&ITT=1&ITP=1&Type=Equity&Symbol=MMM
> ","div",-1,"class=""cn"">"),"|","<"))
>
> Also, can you import data from here:
>
> http://investing.money.msn.com/investments/stock-report?CR=1&AF=1&IH=1&AIE=1&AIR=1&FRH=1&FRK=1&ISA=1&ISQ=1&BSA=1&BSQ=1&CFA=1&CFQ=1&TYS=1&ITT=1&ITP=1&Type=Equity&Symbol=MMM
>
> ...using the EXCEL Web Query (it may take a while for the page to complete)?
>
> On Mon, May 7, 2012 at 4:46 PM, Pi <parisshi@...> wrote:
>
> > Hello,
> >
> > I have been trying to load MSN data. I have tried the following and I'm
> > still still having a hard time:
> > 1. Logged into Msn Money using internet explorer before opening up my
> > template
> > 2. Downloaded the new smf-elements 1.txt file from the wip folder (while
> > excel was closed).
> >
> > Still no luck. What am I missing?
> >
>

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

Re: Problems looking up MSN data

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

Tue May 8, 2012 12:57 pm (PDT)



I just tried both of those element numbers for MMM and they worked fine.
There may be some ticker symbols that don't have 10 years of data.

On Tue, May 8, 2012 at 12:23 PM, antonvanas1989 <antonvanas1989@yahoo.com>wrote:

> Randy, i got the new file aswel, but still found some numbers not working:
>
> RCHGetElementNumber(Ticker;283)
> to
> RCHGetElementNumber(Ticker;292)
>
> I think this link is changes by msn to?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)
1c.

Re: Problems looking up MSN data

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

Tue May 8, 2012 1:48 pm (PDT)



I found my problem, first it was just a number

so my formula was like =RCHGetElementNumber(Ticker;288)/1000000
but it gives a error because there is text ("Bil") in that cell now, so my fault

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I just tried both of those element numbers for MMM and they worked fine.
> There may be some ticker symbols that don't have 10 years of data.
>
> On Tue, May 8, 2012 at 12:23 PM, antonvanas1989 <antonvanas1989@...>wrote:
>
> > Randy, i got the new file aswel, but still found some numbers not working:
> >
> > RCHGetElementNumber(Ticker;283)
> > to
> > RCHGetElementNumber(Ticker;292)
> >
> > I think this link is changes by msn to?
> >
>

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

Re: Problems looking up MSN data

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

Tue May 8, 2012 2:09 pm (PDT)



The "Bil" suffix is now handled with the beta version of the add-in from
the "Works in Progress" folder.

On Tue, May 8, 2012 at 1:48 PM, antonvanas1989 <antonvanas1989@yahoo.com>wrote:

> I found my problem, first it was just a number
>
> so my formula was like =RCHGetElementNumber(Ticker;288)/1000000
> but it gives a error because there is text ("Bil") in that cell now, so my
> fault
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)
2a.

Help!!

Posted by: "gordonbruce@btopenworld.com" GordonBruce@btopenworld.com   gordonbruce@btopenworld.com

Tue May 8, 2012 2:08 pm (PDT)



As a beginner I am struggling to understand this well enough.

I do appreciate your time must be limited but I would very much appreciate any help you can provide to help me construct the =RCHGetTableCell formula to retrieve data from the "Key Stats" table on the following web page-

http://www.morningstar.co.uk/uk/snapshot/snapshot.aspx?id=VAUSA07HRO&investmenttype=SA

I think I would be able to get the rest of the data I require if you could give me an example of what I need to do to achieve one result from this table. I am able to retrieve data from the left of the web page but can't get at the data in the "Key Stats" table.

Thanks in advance.

Regards
Gordon Bruce

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

Re: Help!!

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

Tue May 8, 2012 2:12 pm (PDT)



This works for me:

=RCHGetTableCell("
http://www.morningstar.co.uk/uk/snapshot/snapshot.aspx?id=VAUSA07HRO&investmenttype=SA",2,"ABI
Sector")

...and returns:

Asia Pacific excl. Japan Equities (Pen)

On Tue, May 8, 2012 at 1:45 PM, gordonbruce@btopenworld.com <
GordonBruce@btopenworld.com> wrote:

> As a beginner I am struggling to understand this well enough.
>
> I do appreciate your time must be limited but I would very much appreciate
> any help you can provide to help me construct the =RCHGetTableCell formula
> to retrieve data from the "Key Stats" table on the following web page-
>
>
> http://www.morningstar.co.uk/uk/snapshot/snapshot.aspx?id=VAUSA07HRO&investmenttype=SA
>
> I think I would be able to get the rest of the data I require if you could
> give me an example of what I need to do to achieve one result from this
> table. I am able to retrieve data from the left of the web page but can't
> get at the data in the "Key Stats" table.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
3a.

Re: morningstar element information error

Posted by: "ryanbrown@bellsouth.net" ryanbrown@rlbrown.net   ryanbrown@bellsouth.net

Tue May 8, 2012 5:45 pm (PDT)



I don't know. I have tried reinstalling the add-in, tried a new spreadsheet, tried detect and repair on excel, emptied all my cache....I can't figure it out. If I use elements 14179 - 14189 or others (but not all) I get the display "ERROR"

Then if I use element 14310 for example on Apple, I get a year over year EPS of -0.11 for TTM period. That's not right. I can't figure it out.

Also, if I go to morningstar's webpage, I see "250" in the year 10 column instead of "--".

That can be found here: http://financials.morningstar.com/valuation/price-ratio.html?t=AAPL&region=USA&culture=en-US

I must be doing something wrong. Any suggestions to try?

Thanks for any help!

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> #13961 is "zero" on the MorningStar web page (or at least a "--", which the
> add-in sets to zero so it's numeric).
>
> #14217-#14227 and #14261-#14271 are working fine here for Apple, so I'm not
> sure what to tell you. Did you try other ticker symbols? Maybe you timed
> out on a web page retrieval from MorningStar?
>
> On Mon, May 7, 2012 at 3:52 PM, ryanbrown@... <
> ryanbrown@...> wrote:
>
> > I am another newbie so forgive me if I have a made an error here. I have
> > successfully installed installed the smf_add-in and have been able to
> > retrieve data for most items. So, I know in theory that it is "working".
> >
> > I started plugging the morningstar elements into a spreadsheet and came up
> > with different results. Specifically, elements 13962 through 13976 worked
> > as expected and delivered results, however, 13961 (valuation 10 Year)
> > returned a "0" for apple. I moved on to 14217 (key ratios revenue growth)
> > and received "ERROR" for elements 14217 - 14227 (apple as well). I then
> > tried 14261 through 14271 with apple the same results "ERROR".
> >
> > I am using excel 2007 on windows 7 64 bit. smf add-in 2.1.2012.02.02.
> >
> > anyone offer some advice?
> >
>

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

Re: morningstar element information error

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

Tue May 8, 2012 6:05 pm (PDT)



Elements #14179-#14189 have been obsoleted:

2012-02-12 RCHGetElementNumber Obsoleted a number of Morningstar
element definitions in file smf-elements-4.txt because the web pages
they were on are now dynamically generated:
-- Elements #14058-14211
-- Elements #13935-13950
#14310 is "Key Ratios -- EPS -- Year over Year -- MRQ", not TTM.

Element #13961 is retrieved from this web page:

http://quicktake.morningstar.com/stocknet/Valuation10.aspx?Symbol=AAPL

The data on the web page you're citing is dynamically generated, so
there is no data in the source code of the web page for the add-in to
extract.

On Tue, May 8, 2012 at 5:45 PM, ryanbrown@bellsouth.net
<ryanbrown@rlbrown.net <mailto:ryanbrown@rlbrown.net> > wrote:
I don't know. I have tried reinstalling the add-in, tried a new
spreadsheet, tried detect and repair on excel, emptied all my cache....I
can't figure it out. If I use elements 14179 - 14189 or others (but not
all) I get the display "ERROR"

Then if I use element 14310 for example on Apple, I get a year over
year EPS of -0.11 for TTM period. That's not right. I can't figure it
out.

Also, if I go to morningstar's webpage, I see "250" in the year 10
column instead of "--".

That can be found here:
http://financials.morningstar.com/valuation/price-ratio.html?t=AAPL&regi\
on=USA&culture=en-US
<http://financials.morningstar.com/valuation/price-ratio.html?t=AAPL&reg\
ion=USA&culture=en-US>

I must be doing something wrong. Any suggestions to try?

Thanks for any help!

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

Re: Maxing Out on Data Downloads using SmfUpdateDownloadTable

Posted by: "steven_rourk" steven_rourk@hotmail.com   steven_rourk

Tue May 8, 2012 6:59 pm (PDT)



Randy, I have spreadsheets for each co that I follow. In this spreadsheets I download the financials, the quote and a few other info. When I open one it usually takes 30-45 seconds or so.

In order to speed up the process I was following your sugestion of using smfUpdateDownloadTable for the financials, as I only have to update this every 3 months. So, I started building my table which consists of only one line, and several columns where I put the principal accounts (cash, receivables, inventory, fixed assets, etc, etc). Something like this:

5196 5946 5966 6116 6206
Ticker FY1 Cash Mark Curr A Fix A
AAPL 2011/09 9815 16137 44988 7777

Everything was working out fine, but when I reached column 202 (GT) the data stops loading. Its just blank after that column. Obviously I have not reached the 1000 web page limit. I´m even using the same source AdvFN so I´m guessing its just one web page.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> You don't need to do a full system restart. If you run the
> smfForceRecalculation macro (see "Links" area of the group for more info),
> it will "reset" everything. However, it won't allow you to get around
> retrieving over 1000 web pages at the same time, amongst all open
> workbooks. IMO, that limit should be plenty.
>
> For as many stocks as you're doing, I usually go to the
> smfUpdateDownloadTable process (again, see "Links" area). This would allow
> you to load VALUES into the workbooks instead of using formulas, so you'd
> have better control over the 1000 web page limit. For example, you don't
> really need to get financial statements data every time you open the
> workbook. If you can just update it once a week, once a month, or piecemeal
> as needed, it does a better job of limiting how much you utilize the free
> data providers. I really don't want them overloaded with people creating
> "databases" by grabbing as much free data as they want. It would be easy
> enough for them to make the data inaccessible to the add-in -- as we've
> seen by recent MorningStar changes.
>
> On Tue, Dec 6, 2011 at 11:05 AM, ro.valen <ro.valen@...> wrote:
>
> >
> > I have built a spreadsheet using Mr. Harmelink's fantastic SMF Add-in that
> > analyzes fundamental data on a couple hundred ticker symbols. However,
> > after data has been retrieved on the first hundred or so, I get an error
> > message telling me I have pretty much reached the maximum allowable
> > download for the session and I am subsequently prevented from downloading
> > further data until I do a full system restart and begin anew.
> >
> > Does anyone know how to get around this data limitation without having to
> > restart my computer?
> >
>

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

Re: Maxing Out on Data Downloads using SmfUpdateDownloadTable

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

Tue May 8, 2012 7:46 pm (PDT)



There's a hard-coded limit of 200 columns in the routine:

For iCol = 1 To 200

Since the routine was written when EXCEL had a limitation of 256 columns,
that was just an arbitrary number I chose to use.

On Tue, May 8, 2012 at 6:58 PM, steven_rourk <steven_rourk@hotmail.com>wrote:

> Randy, I have spreadsheets for each co that I follow. In this spreadsheets
> I download the financials, the quote and a few other info. When I open one
> it usually takes 30-45 seconds or so.
>
> In order to speed up the process I was following your sugestion of using
> smfUpdateDownloadTable for the financials, as I only have to update this
> every 3 months. So, I started building my table which consists of only one
> line, and several columns where I put the principal accounts (cash,
> receivables, inventory, fixed assets, etc, etc). Something like this:
>
> 5196 5946 5966 6116 6206
> Ticker FY1 Cash Mark Curr A Fix A
> AAPL 2011/09 9815 16137 44988 7777
>
> Everything was working out fine, but when I reached column 202 (GT) the
> data stops loading. Its just blank after that column. Obviously I have not
> reached the 1000 web page limit. I´m even using the same source AdvFN so
> I´m guessing its just one web page.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (9)
4c.

Re: Maxing Out on Data Downloads using SmfUpdateDownloadTable

Posted by: "steven_rourk" steven_rourk@hotmail.com   steven_rourk

Tue May 8, 2012 8:16 pm (PDT)



Is there any way around it?

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> There's a hard-coded limit of 200 columns in the routine:
>
> For iCol = 1 To 200
>
> Since the routine was written when EXCEL had a limitation of 256 columns,
> that was just an arbitrary number I chose to use.
>
> On Tue, May 8, 2012 at 6:58 PM, steven_rourk <steven_rourk@...>wrote:
>
> > Randy, I have spreadsheets for each co that I follow. In this spreadsheets
> > I download the financials, the quote and a few other info. When I open one
> > it usually takes 30-45 seconds or so.
> >
> > In order to speed up the process I was following your sugestion of using
> > smfUpdateDownloadTable for the financials, as I only have to update this
> > every 3 months. So, I started building my table which consists of only one
> > line, and several columns where I put the principal accounts (cash,
> > receivables, inventory, fixed assets, etc, etc). Something like this:
> >
> > 5196 5946 5966 6116 6206
> > Ticker FY1 Cash Mark Curr A Fix A
> > AAPL 2011/09 9815 16137 44988 7777
> >
> > Everything was working out fine, but when I reached column 202 (GT) the
> > data stops loading. Its just blank after that column. Obviously I have not
> > reached the 1000 web page limit. I´m even using the same source AdvFN so
> > I´m guessing its just one web page.
> >
>

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

Re: Maxing Out on Data Downloads using SmfUpdateDownloadTable

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

Tue May 8, 2012 9:01 pm (PDT)



Only solution would be to manually change the VBA code.

On Tue, May 8, 2012 at 8:16 PM, steven_rourk <steven_rourk@hotmail.com>wrote:

> Is there any way around it?
>
> --- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
> >
> > There's a hard-coded limit of 200 columns in the routine:
> >
> > For iCol = 1 To 200
> >
> > Since the routine was written when EXCEL had a limitation of 256 columns,
> > that was just an arbitrary number I chose to use.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (9)
Recent Activity
Visit Your Group
Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! News

Get it all here

Breaking news to

entertainment news

New web site?

Drive traffic now.

Get your business

on Yahoo! search.

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 01.18

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)