Kamis, 04 April 2013

[smf_addin] Digest Number 2571

EXCEL Stock Market Functions Add-in

8 New Messages

Digest #2571

Messages

Wed Apr 3, 2013 12:32 pm (PDT) . Posted by:

"rr76012" rr76012

Hi Randy,
You are like the rain man. I have no idea how you come up with these
great solutions. You must have an IQ north of 150..
For your example below, could I replace "GOOG" with &A1, to reference
cell A1 and use it as a reference for a stock symbol input cell, to
automate the process?
example replace GOOG with &A1,
(
=smfGetCSVFile("http://financials.morningstar.com/ajax/exportKR2CSV.html
?t=&A1")
I tried it and it did not work for me, maybe I am doing something wrong?
and also,
...or:
>
>
=smfGetCSVFile("http://financials.morningstar.com/ajax/ReportProcess4CSV\
\
>
.html?t=&A1&reportType=is&period=12&dataType=A&order=asc&columnYear=10&\
> rounding=3&denominatorView=raw"
I tried it but cannot get it to work right for me?
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>
>

Wed Apr 3, 2013 4:20 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

You just need to concatenate the cell reference TO the strings, not include
it WITHIN the string. For example:

=smfGetCSVFile("http://financials.morningstar.com/ajax/exportKR2CSV.html?t=
"&A1)

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

On Wed, Apr 3, 2013 at 12:32 PM, rr76012 <rr76012@yahoo.com> wrote:

>
> For your example below, could I replace "GOOG" with &A1, to reference
>
> cell A1 and use it as a reference for a stock symbol input cell, to
>
> automate the process?
>
> example replace GOOG with &A1,
>
> (
>
> =smfGetCSVFile("http://financials.morningstar.com/ajax/exportKR2CSV.html
>
> ?t=&*A1*")
>
> I tried it and it did not work for me, maybe I am doing something wrong?
>
> and also,
>
> ...or:
> >
> > =smfGetCSVFile("
> http://financials.morningstar.com/ajax/ReportProcess4CSV\
> > .html?t=*&A1*
> &reportType=is&period=12&dataType=A&order=asc&columnYear=10&\
> > rounding=3&denominatorView=raw"
>
> I tried it but cannot get it to work right for me?
>
>

Wed Apr 3, 2013 1:13 pm (PDT) . Posted by:

"Kermit W. Prather" kermitpra

I, also, have problems with MSN elements. On my Vista machine it almost
never works except when I use the SMFUpdatedownloadtable-sample workbook.
Then It works one day and not the next.

On win7 it works most of the time.

I reported the problem with MSN months ago but it was deemed to be my
machine somehow. I gave up trying to fix it.

I'm not even sure where to start but I stepped thru the code below are my
findings. Not sure they help.

Randy, I stepped thru the VBA code until I got the error. I'd really like to
work on figuring out where the problem is if you can direct me thru the
debugging

This is the smfparms from the RCHGetElementNumber-Element-Definitions.xls
file

MSN;Last
Price;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=~~~~~;1;STOCK ACTIVITY;LAST PRICE; ; ;0;0;0;0

This is the pURL on entry

"http://investing.money.msn.com/investments/stock-report?CR=1
<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&Ty
pe=Equity&Symbol=AINV>
&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=AINV";

At entry to failing function

Watch : : RCHGetElementNumber : Empty : Variant/Empty :
modGetElementNumber.RCHGetElementNumber
Watch : : pURL : <Out of context> : Empty : modUtilities.RCHGetURLData
Watch : : aData : Empty : Empty : modUtilities.smfGetWebPage
Watch : : iData : Empty : Variant/Empty : modUtilities.smfGetWebPage

At exit of failing function

: aData(2,1) :
"0:http://investing.money.msn.com/investments/stock-report?CR=1&AF=1&IH=1&AI
E=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=AGNC"; :
: aData(2,2) : "Error" : String : modUtilities.smfGetWebPage

Public Function smfGetWebPage(ByVal pURL As String, _
Optional ByVal pUseIE As Integer = 0, _
Optional ByVal pConvType As Integer = 0) As String

'----------------------------------------------------------
--------------------------------*
' 2011.02.16 -- Add routine
' 2011.04.27 -- Add HTML codes &#48; thru &#57;

'----------------------------------------------------------
--------------------------------*
For iData = 1 To kPages
Select Case True
Case aData(iData, 1) = ""
s2 = RCHGetURLData(pURL, pUseIE) none if this code was
ever executed
Select Case pConvType
Case 0
s2 = Replace(s2, "&amp;", "&")
s2 = Replace(s2, "&nbsp;<b>", "<b> ")
s2 = Replace(s2, "&nbsp;", " ")
s2 = Replace(s2, Chr(9), " ")
s2 = Replace(s2, Chr(10), "")
s2 = Replace(s2, Chr(13), "")
s2 = Replace(s2, "&#48;", "0")
s2 = Replace(s2, "&#49;", "1")
s2 = Replace(s2, "&#50;", "2")
s2 = Replace(s2, "&#51;", "3")
s2 = Replace(s2, "&#52;", "4")
s2 = Replace(s2, "&#53;", "5")
s2 = Replace(s2, "&#54;", "6")
s2 = Replace(s2, "&#55;", "7")
s2 = Replace(s2, "&#56;", "8")
s2 = Replace(s2, "&#57;", "9")
s2 = Replace(s2, "&#150;", Chr(150))
s2 = Replace(s2, "&#151;", "-")
s2 = Replace(s2, "&mdash;", "-")
s2 = Replace(s2, "&#160;", " ")
s2 = Replace(s2, Chr(160), " ")
s2 = Replace(s2, "<TH", "<td")
s2 = Replace(s2, "</TH", "</td")
s2 = Replace(s2, "<th", "<td")
s2 = Replace(s2, "</th", "</td")
Case 1
s2 = Replace(s2, Chr(10), Chr(13))
End Select
Select Case pURL
Case "http://finance.yahoo.com/advances"
s2 = Replace(s2, "<sup>1</sup>", "")
End Select
aData(iData, 1) = pUseIE & ":" & pURL
aData(iData, 2) = s2
smfGetWebPage = s2
Exit Function
Case aData(iData, 1) = pUseIE & ":" & pURL this was the
exit point from the function
smfGetWebPage = aData(iData, 2)
Exit Function
Case iData = kPages
smfGetWebPage = "Error -- Too many web page retrievals"
Exit Function
End Select
Next iData
smfGetWebPage = "Error"
End Function

Public Function smfGetAData(p1 As Integer, p2 As Integer)
smfGetAData = Left(aData(p1, p2), 32767)
End Function
Public Sub smfFixLinks()

'----------------------------------------------------------
--------------------------------*
' 2012.01.02 -- Expand to do all sheets in workbook

'----------------------------------------------------------
--------------------------------*
Dim Sht As Worksheet
For Each Sht In Worksheets
Sht.Cells.Replace _
What:="'*\RCH_Stock_Market_Functions.xla'!", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Next Sht
End Sub

Function IfError(formula As Variant, show As String)

On Error GoTo ErrorHandler

If IsError(formula) Then
IfError = show
Else
IfError = formula
End If

Exit Function

ErrorHandler:
Resume Next

End Function


-----Original Message-----
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf
Of rr76012
Sent: Tuesday, April 02, 2013 4:35 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: Error with RCHGetElementNumber() 50/50 hit and miss
with stocks

Yes, Like you, I like to use RCHgetelementnumber (), but it seems like a
50/50 hit and miss, with the different stocks that I am trying to analyze to
buy.

Everyday, I feel like it is just me, having this problem with MSN element
numbers. I am glad to hear someone else is facing my same problem. I
understand your disappointment and frustration.

Randy is the best, he is so patient and helpful. I hope you get your problem
resolved.

Best Wishes,
rr76012

--- In <mailto:smf_addin@yahoogroups.com> smf_addin@yahoogroups.com,
अनन्त: < <mailto:1998chemie@...> 1998chemie@...> wrote:
>
> Can you please give the link to fix this issue, I went to the links
> section but could not find anything.
> I would really appreciate your help
> Au
>
>
> On Fri, Mar 29, 2013 at 1:47 PM, Randy Harmelink < <mailto:rharmelink@...>
rharmelink@...>wrote:
>
> > **
> >
> >
> > Without examples of ticker symbols and element numbers, it's hard to
help.
> > Could be a website is down, or has changed the format of their web page.
> >
> > If AdvFN elements, you may need the fix mentioned in the LINKS area
> > of the group...
> >
> >
> > On Fri, Mar 29, 2013 at 10:29 AM, foolishblisterbutt <
<mailto:fgschack@...> fgschack@...>wrote:
> >
> >>
> >> I have used the RCHGetElementNumber() heavily, but today it started
> >> failing at most stocks, not all of them, but most? It just shows
> >> "Error" in the field.
> >>
> >> I use the latest version of the SMF plugin.
> >>
> >> Anybody else having the same problem?
> >>
> >>
> >
> >
>




------------------------------------

Yahoo! Groups Links


(Yahoo! ID required)

<mailto:smf_addin-fullfeatured@yahoogroups.com>
smf_addin-fullfeatured@yahoogroups.com


Wed Apr 3, 2013 2:04 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

That "aData(2,2)" result basically says it wasn't able to retrieve the web
page. So any items you attempt to extract from that web page will return
"Error" -- there is nothing to extract.

On Wed, Apr 3, 2013 at 1:13 PM, Kermit W. Prather
<kermitp@tampabay.rr.com>wrote:

> At exit of failing function
>
> : aData(2,1) : "0:
> 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=AGNC"
> :****
>
> : aData(2,2) : "Error" : String : modUtilities.smfGetWebPage****
>
>
>

Wed Apr 3, 2013 4:13 pm (PDT) . Posted by:

"Kermit W. Prather" kermitpra

That, I understand.

What I am asking you is to help me find out why there is no webpage. There certainly should be one.

What can I do to track down the cause of the error? I can find my way around the VBA code but I need you to guide me to zero in on what’s causing the error.

I would really like to retrieve MSN information.

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Wednesday, April 03, 2013 5:05 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: Error with RCHGetElementNumber() 50/50 hit and miss with stocks


That "aData(2,2)" result basically says it wasn't able to retrieve the web page. So any items you attempt to extract from that web page will return "Error" -- there is nothing to extract.
On Wed, Apr 3, 2013 at 1:13 PM, Kermit W. Prather <kermitp@tampabay.rr.com> wrote:
At exit of failing function
: aData(2,1) : "0:http://investing.money.msn.com/investments/stock-report?CR=1 <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=AGNC> &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=AGNC"; :
: aData(2,2) : "Error" : String : modUtilities.smfGetWebPage


Wed Apr 3, 2013 5:04 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Unfortunately, I don't have any way to determine that. It could be a
timeout. It could be something MSN is doing. It could be a faulty Internet
connection. Something intermittent, possibly specific to your computer
setup. Even time of day that you're accessing MSN.

I just tried a "stress test", grabbing an element for each of the 500
stocks in the S&P 500 index. It took a LONG time, because that web page for
each company contains a LOT of data. But I didn't get a single error.

Your best bet would be to put a breakpoint on the VBA code that sets the
error in the RCHGetURLData1 module. Then you'd need to examine the XMLHTTP
object to see if there is anything there that tells you why an error
occurred.

On Wed, Apr 3, 2013 at 4:13 PM, Kermit W. Prather
<kermitp@tampabay.rr.com>wrote:

>
>
> What I am asking you is to help me find out why there is no webpage. There
> certainly should be one. ****
>
> ** **
>
> What can I do to track down the cause of the error? I can find my way
> around the VBA code but I need you to guide me to zero in on what’s causing
> the error.****
>
> ** **
>
> I would really like to retrieve MSN information.
>

Wed Apr 3, 2013 4:08 pm (PDT) . Posted by:

"a jackson" alexjacksonpass

Hello Randy,

I am a new user and I'm wanting to use the 5396 AdvFN-A Annual Income Statement -- EBITDA -- FY1 function with both CDN and US tickers. URS exists both on the TSX and NYSE exchanges and I want to use the NYSE company URS corp however I can't figure out how the ticker is to be differentiated by the CDN URS which is Urastar Gold corp which the function is currently returning values for.
Thanks for your help

Wed Apr 3, 2013 4:24 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

For example, you can use any of:

URS
NYSE:URS
TSXV:URS

What you get for the first one will depend on what "default" region AdvFN
sets for you. So, if you don't want your default Canadian ticker, you'll
need to use that "NYSE:" prefix with the ticker symbol.

On Wed, Apr 3, 2013 at 4:06 PM, a jackson <alex@rockethouse.com> wrote:

>
> I am a new user and I'm wanting to use the 5396 AdvFN-A Annual Income
> Statement -- EBITDA -- FY1 function with both CDN and US tickers. URS
> exists both on the TSX and NYSE exchanges and I want to use the NYSE
> company URS corp however I can't figure out how the ticker is to be
> differentiated by the CDN URS which is Urastar Gold corp which the function
> is currently returning values for.
>
Diposting oleh EXCEL FEED LEARNING di 02.21

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)