Sabtu, 06 April 2013

[smf_addin] Digest Number 2574

EXCEL Stock Market Functions Add-in

15 New Messages

Digest #2574
1b
1d
4a
4b
Re: more than 5 quarterly financial statements by "अनन्त:" ritchemie
4d
Re: more than 5 quarterly financial statements by "Randy Harmelink" rharmelink
5a
morningstar efficiency ratio by "weekeewawa" weekeewawa
5b
Re: morningstar efficiency ratio by "Randy Harmelink" rharmelink
5c
Re: morningstar efficiency ratio by "weekeewawa" weekeewawa
5d
Re: morningstar efficiency ratio by "weekeewawa" weekeewawa

Messages

Sat Apr 6, 2013 3:20 am (PDT) . Posted by:

"mg6116" mg6116

The following statement in Excel:

=RCHGetTableCell("http://finance.yahoo.com/q?s=QQQ130622P00065000";1;"Bid:") gives me "#N/A"

while this statement:

=RCHGetTableCell("http://finance.yahoo.com/q?s=QQQ130622P00065000";1;"Prev Close:") gives me the correct value.

The statements only differ in the find parameter. When I look at the source html on this web site, the values are there.

It should work this way for the first statement too (I suppose) since it does for the second one.

Can anyone help? What am I doing wrong?

Or is there another way to do the same?

Greatfull for any assistance!

Sat Apr 6, 2013 3:24 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Both formulas work fine for me.

I suspect you have either an IE or an add-in web page "caching" problem.
Take a look at the two entries in the LINKS area of the group related to
the smfForceRecalculation macro.

On Sat, Apr 6, 2013 at 3:08 AM, mg6116 <Marc.Goemaere@weerde.be> wrote:

> The following statement in Excel:
>
> =RCHGetTableCell("http://finance.yahoo.com/q?s=QQQ130622P00065000";1;"Bid:")
> gives me "#N/A"
>
> while this statement:
>
> =RCHGetTableCell("http://finance.yahoo.com/q?s=QQQ130622P00065000";1;"Prev
> Close:") gives me the correct value.
>
> The statements only differ in the find parameter. When I look at the
> source html on this web site, the values are there.
>
> It should work this way for the first statement too (I suppose) since it
> does for the second one.
>
> Can anyone help? What am I doing wrong?
>
> Or is there another way to do the same?
>
> Greatfull for any assistance!
>

Sat Apr 6, 2013 5:44 am (PDT) . Posted by:

"mg6116" mg6116

Thanks!

I assumed wrongly that there was kind of an automatic refresh. I ran the smfForceRecalculation macro and bingo!

Do I have to do that every time a want to update quotes in my spreadsheet? Or has anyone found a way to do this on fixed interval (like once every 15 minutes or so)?

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Both formulas work fine for me.
>
> I suspect you have either an IE or an add-in web page "caching" problem.
> Take a look at the two entries in the LINKS area of the group related to
> the smfForceRecalculation macro.
>
> On Sat, Apr 6, 2013 at 3:08 AM, mg6116 <Marc.Goemaere@...> wrote:
>
> > The following statement in Excel:
> >
> > =RCHGetTableCell("http://finance.yahoo.com/q?s=QQQ130622P00065000";1;"Bid:")
> > gives me "#N/A"
> >
> > while this statement:
> >
> > =RCHGetTableCell("http://finance.yahoo.com/q?s=QQQ130622P00065000";1;"Prev
> > Close:") gives me the correct value.
> >
> > The statements only differ in the find parameter. When I look at the
> > source html on this web site, the values are there.
> >
> > It should work this way for the first statement too (I suppose) since it
> > does for the second one.
> >
> > Can anyone help? What am I doing wrong?
> >
> > Or is there another way to do the same?
> >
> > Greatfull for any assistance!
> >
>

Sat Apr 6, 2013 2:00 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

You can write a VBA routine to do a timed update.

I have it on a toolbar button, so it's pretty easy to hit when I want to do
an update. But I'm not monitoring things a lot during the day either.

On Sat, Apr 6, 2013 at 5:44 AM, mg6116 <Marc.Goemaere@weerde.be> wrote:

>
> I assumed wrongly that there was kind of an automatic refresh. I ran the
> smfForceRecalculation macro and bingo!
>
> Do I have to do that every time a want to update quotes in my spreadsheet?
> Or has anyone found a way to do this on fixed interval (like once every 15
> minutes or so)?
>

Sat Apr 6, 2013 9:07 am (PDT) . Posted by:

"joshdramsey" joshdramsey



Hi Randy,

Oops, you're right, 877 (not 887) is the element number I was using that returned a valid response.

Thanks for the link to the message thread about the CSV files. I'll check it out.

Thanks again for the help!

Josh

Sat Apr 6, 2013 9:11 am (PDT) . Posted by:

"weekeewawa" weekeewawa

hello randy,

excellent stuff

but i have a question regarding to the code. i see that the codes point to google symbol GOOG

however, i would like it to point to a specific cell within excel eg Cell A1 where i key in the symbol instead

what will be the code like?

thanks

--- In smf_addin@yahoogroups.com, "Randy H" <rharmelink@...> wrote:
>
> The add-in can't do it, because the web page is generated dynamically.
>
> I can give you alternatives though, based on the "Export" button on the
> page:
>
> =smfGetCSVFile("http://financials.morningstar.com/ajax/exportKR2CSV.html\
> ?t=GOOG")
>
> ...or:
>
> =smfGetCSVFile("http://financials.morningstar.com/ajax/ReportProcess4CSV\
> .html?t=GOOG&reportType=is&period=12&dataType=A&order=asc&columnYear=10&\
> rounding=3&denominatorView=raw")
>
> ...where:
>
> -- "reportType&quot; can be "is", "bs", or "cf" (which statement you want).
> -- "period" can be 3 or 12 (quarterly or annual).
> -- "dataType"; can be "A" or "R" (As Reported or Restated).
> -- "order" can be "asc" or "desc" (order of the years/quarters going
> across).
> -- "columnYear&quot; can be 5 or 10 (number of columns of data).
> -- "denominatorView" can be "raw" or "percentage&quot;
>
> So you could do any of those in an individual worksheet and then
> reference the items you want to present.
>
> On Thu, Aug 30, 2012 at 3:16 PM, leiliao78@...
> <liaolei78@... <mailto:liaolei78@...> > wrote:
>
> Do you know how to extract the income statement information from
> Morningstar into excel?
>
> Such as the revenue for Google from 2002-2011 shown on this link below:
> http://financials.morningstar.com/income-statement/is.html?t=GOOG&region\
> =USA&culture=en-us
> <http://financials.morningstar.com/income-statement/is.html?t=GOOG&regio\
> n=USA&culture=en-us>
>

Sat Apr 6, 2013 12:13 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Already answered in this thread...

On Sat, Apr 6, 2013 at 9:10 AM, weekeewawa <weekeewawa@yahoo.com> wrote:

>
> but i have a question regarding to the code. i see that the codes point to
> google symbol GOOG
>
> however, i would like it to point to a specific cell within excel eg Cell
> A1 where i key in the symbol instead
>
> what will be the code like?
>

Sat Apr 6, 2013 9:34 am (PDT) . Posted by:

"weekeewawa" weekeewawa

hello randy,

i was wondering, do you happen to know any free sources where i can get like 8 quarters or more financial statements such as income statement, balance sheet and cash flow?

morningstar has 5 and 10 (which needs to paid to see)

thanks

Sat Apr 6, 2013 10:07 am (PDT) . Posted by:

"अनन्त:" ritchemie

Advfn gives your 20 quarters for free.

On Sat, Apr 6, 2013 at 12:34 PM, weekeewawa <weekeewawa@yahoo.com> wrote:

> **
>
>
> hello randy,
>
> i was wondering, do you happen to know any free sources where i can get
> like 8 quarters or more financial statements such as income statement,
> balance sheet and cash flow?
>
> morningstar has 5 and 10 (which needs to paid to see)
>
> thanks
>
>
>

Sat Apr 6, 2013 8:20 pm (PDT) . Posted by:

"weekeewawa" weekeewawa

hello randy

for example, when i key in stocks such as IBM and asian stocks such as or LFUGY or even brazilian stocks such as BFRE12 in advfn, every single results extracted from the element number in the quarter and annual financial statements shows error. is there any reason to it?

--- In smf_addin@yahoogroups.com, अनन्त: <1998chemie@...> wrote:
>
> Advfn gives your 20 quarters for free.
>
>
>
> On Sat, Apr 6, 2013 at 12:34 PM, weekeewawa <weekeewawa@...> wrote:
>
> > **
> >
> >
> > hello randy,
> >
> > i was wondering, do you happen to know any free sources where i can get
> > like 8 quarters or more financial statements such as income statement,
> > balance sheet and cash flow?
> >
> > morningstar has 5 and 10 (which needs to paid to see)
> >
> > thanks
> >
> >
> >
>

Sat Apr 6, 2013 9:24 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Just because you have a ticker symbol doesn't mean that AdvFN has data for
it. You'll need to go to the web site and check on "unusual" companies. I,
myself, stick strictly to US companies, so I have no idea how non-US
companies are handled unless I check -- which is what you need to do.

Now, if you're having problems with IBM, I suspect you need to apply the
fix from the LINKS area of the group.

On Sat, Apr 6, 2013 at 8:20 PM, weekeewawa <weekeewawa@yahoo.com> wrote:

>
> for example, when i key in stocks such as IBM and asian stocks such as or
> LFUGY or even brazilian stocks such as BFRE12 in advfn, every single
> results extracted from the element number in the quarter and annual
> financial statements shows error. is there any reason to it?
>

Sat Apr 6, 2013 10:51 am (PDT) . Posted by:

"weekeewawa" weekeewawa

hello randy

in the key ratio section of morningstar, there are in fact 5 sections of data which are the profitability, growth, cash flow, financial health and efficiency ratio.

in the rchelement excel sheet, there are only a few elements numbers that i can extract

however, i want all the data in all 5 sections to be extracted into excel

is there any codes which enable me to do it?

Sat Apr 6, 2013 12:12 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

No codes, since they are now dynamically generated pages. You just have
what is available on the CSV file solution.

On Sat, Apr 6, 2013 at 10:51 AM, weekeewawa <weekeewawa@yahoo.com> wrote:

>
> in the key ratio section of morningstar, there are in fact 5 sections of
> data which are the profitability, growth, cash flow, financial health and
> efficiency ratio.
>
> in the rchelement excel sheet, there are only a few elements numbers that
> i can extract
>
> however, i want all the data in all 5 sections to be extracted into excel
>
> is there any codes which enable me to do it?
>

Sat Apr 6, 2013 8:12 pm (PDT) . Posted by:

"weekeewawa" weekeewawa

oh,

would there be any plan to add in the key ratios into the list of element numbers?

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> No codes, since they are now dynamically generated pages. You just have
> what is available on the CSV file solution.
>
> On Sat, Apr 6, 2013 at 10:51 AM, weekeewawa <weekeewawa@...> wrote:
>
> >
> > in the key ratio section of morningstar, there are in fact 5 sections of
> > data which are the profitability, growth, cash flow, financial health and
> > efficiency ratio.
> >
> > in the rchelement excel sheet, there are only a few elements numbers that
> > i can extract
> >
> > however, i want all the data in all 5 sections to be extracted into excel
> >
> > is there any codes which enable me to do it?
> >
>

Sat Apr 6, 2013 8:53 pm (PDT) . Posted by:

"weekeewawa" weekeewawa

hello randy

i was looking thru at the key ratios section of morningstar, there is apparently some issues i would like to highlight

1) when i key in the rchgetelement number of all key ratios and valuation ratios into excel, every single one gives me an error

however, in another post here i saw, there is this code which u gave

=smfGetCSVFile("http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t="&A1&"&reportType=is&period=12&dataType=A&order=asc&columnYear=10&rounding=3&denominatorView=raw")

in which it extracts the income statement of a certain company linking to a specific cell A1.

you are also mentioning that if the report type would to be change from is to cf or bs, it would extract the data which it indeed works

so as i was browsing thru the morningstar key ratios website, it apparently allows ''export&#39;' of key ratios to excel format

the question is, can i and how do i tweak the code to extract the key ratios just like i do for cf, bs and is?

thanks

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> No codes, since they are now dynamically generated pages. You just have
> what is available on the CSV file solution.
>
> On Sat, Apr 6, 2013 at 10:51 AM, weekeewawa <weekeewawa@...> wrote:
>
> >
> > in the key ratio section of morningstar, there are in fact 5 sections of
> > data which are the profitability, growth, cash flow, financial health and
> > efficiency ratio.
> >
> > in the rchelement excel sheet, there are only a few elements numbers that
> > i can extract
> >
> > however, i want all the data in all 5 sections to be extracted into excel
> >
> > is there any codes which enable me to do it?
> >
>

Tidak ada komentar:

Posting Komentar