Rabu, 20 Maret 2013

[smf_addin] Digest Number 2554

15 New Messages

Digest #2554
1a
Re: EPS (ttm) = Error by "freefaller6" freefaller6
1b
Re: EPS (ttm) = Error by "freefaller6" freefaller6
1c
Re: EPS (ttm) = Error by "Randy Harmelink" rharmelink
1d
Re: EPS (ttm) = Error by "freefaller6" freefaller6
2a
Re: tmxmoney.com by "Jim" buckleca
2b
Re: tmxmoney.com by "Randy Harmelink" rharmelink
2c
Re: tmxmoney.com by "Jim" buckleca
2d
Re: tmxmoney.com by "Randy Harmelink" rharmelink
2e
Re: tmxmoney.com by "Jim" buckleca
2f
Re: tmxmoney.com by "Randy Harmelink" rharmelink
2g
Re: tmxmoney.com by "Jim" buckleca
5
XMLHTTP and Refresh by "tar044" tar044

Messages

Wed Mar 20, 2013 10:37 am (PDT) . Posted by:

"freefaller6" freefaller6

Ok...now I am really confused. Element # 13883 does correspond to the Price/Cash Flow, but the corresponding row in Excel is 13888. Likewise, for EPS (ttm), the element # is 13878, but the Excel row is 13883. If I am building a spreadsheet, and am interested in EPS (ttm), I need to refer to row 13883, right? Thanks.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Element #13883 is coming up fine here. But it's the Price/Cash Flow from
> Zacks, not an EPS (ttm) field...
>
> On Tue, Mar 19, 2013 at 4:24 PM, freefaller6 <freefaller6@...> wrote:
>
> > Hi Randy, this value is coming up error; cell reference is 13883. Would
> > you please look into this? Thanks.
> >
>

Wed Mar 20, 2013 1:24 pm (PDT) . Posted by:

"freefaller6" freefaller6

please disregard this last question, I have it correctly in my spreadsheets. But...I am still getting an error for EPS (ttm), and other Zack's numbers. I have no idea why. Any thoughts? Thanks in advance.

--- In smf_addin@yahoogroups.com, "freefaller6&quot; <freefaller6@...> wrote:
>
> Ok...now I am really confused. Element # 13883 does correspond to the Price/Cash Flow, but the corresponding row in Excel is 13888. Likewise, for EPS (ttm), the element # is 13878, but the Excel row is 13883. If I am building a spreadsheet, and am interested in EPS (ttm), I need to refer to row 13883, right? Thanks.
>
> --- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@> wrote:
> >
> > Element #13883 is coming up fine here. But it's the Price/Cash Flow from
> > Zacks, not an EPS (ttm) field...
> >
> > On Tue, Mar 19, 2013 at 4:24 PM, freefaller6 <freefaller6@> wrote:
> >
> > > Hi Randy, this value is coming up error; cell reference is 13883. Would
> > > you please look into this? Thanks.
> > >
> >
>

Wed Mar 20, 2013 2:04 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Element # 13878 works fine for me:

=RCHGetElementNumber("MMM";,13878)

Does that work for you? Does this web page render OK under the EXCEL Web
Query dialog?

http://www.zacks.com/stock/quote/MMM

On Wed, Mar 20, 2013 at 1:24 PM, freefaller6 <freefaller6@yahoo.com> wrote:

> please disregard this last question, I have it correctly in my
> spreadsheets. But...I am still getting an error for EPS (ttm), and other
> Zack's numbers. I have no idea why. Any thoughts? Thanks in advance.
>

Wed Mar 20, 2013 2:45 pm (PDT) . Posted by:

"freefaller6" freefaller6

something is going on with my wireless router. I cannot access zacks through my home internet connection, but can just fine through my cell network. So, the problem's on me, sorry for your trouble.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Element # 13878 works fine for me:
>
> =RCHGetElementNumber("MMM";,13878)
>
> Does that work for you? Does this web page render OK under the EXCEL Web
> Query dialog?
>
> http://www.zacks.com/stock/quote/MMM
>
> On Wed, Mar 20, 2013 at 1:24 PM, freefaller6 <freefaller6@...> wrote:
>
> > please disregard this last question, I have it correctly in my
> > spreadsheets. But...I am still getting an error for EPS (ttm), and other
> > Zack's numbers. I have no idea why. Any thoughts? Thanks in advance.
> >
>

Wed Mar 20, 2013 11:24 am (PDT) . Posted by:

"Jim" buckleca



Hi Randy,

I assume the same thing is happening with this code:

=rchGetTableCell("http://web.tmxmoney.com/company.php?qm_symbol=AAV",1,">High:")

Returns "error" in all the functions

If so, I was wondering if there's a workaround since this page appears fine in Firefox and I/E?

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Hmmm. I can get it into the Web Query dialog now too. But when the XMLHTTP
> protocol tries to retrieve the web page for the add-in, it gets some
> cryptic error message numbers (large negative integers). So if it can't
> retrieve the source code of the web page, none of the add-in functions are
> going to work.
>
> I wonder if the PHP routine has some way to identify what is going after
> the data?
>
> On Thu, Feb 2, 2012 at 8:54 AM, Mike McQuaid <mikemcq802@...> wrote:
>
> > **
> >
> > Randy, I can see that site just fine in Excel Web Query (and IE). In
> > fact, I used W/Q to place that whole web page in my worksheet and I saw it
> > fine - including sector and industry.
> >
> > But, I can't get the TableCell or TagContent to work either - not sure why
> > - I don't usually have trouble with those.
> >
>

Wed Mar 20, 2013 1:58 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Why are you using that URL? There's no high listed on that web page. Which
is why you get "Error" returned. Instead, try:

=RCHGetTableCell("http://web.tmxmoney.com/quote.php?qm_symbol=AAV
",1,">High:")

On Wed, Mar 20, 2013 at 11:24 AM, Jim <buckleca@yahoo.com> wrote:

>
> I assume the same thing is happening with this code:
>
> =rchGetTableCell("http://web.tmxmoney.com/company.php?qm_symbol=AAV
> ",1,">High:")
>
> Returns "error" in all the functions
>
> If so, I was wondering if there's a workaround since this page appears
> fine in Firefox and I/E?
>

Wed Mar 20, 2013 6:48 pm (PDT) . Posted by:

"Jim" buckleca



Sorry, I didn't give you the right example. Like your example this works:

=smfGetTagContent("http://web.tmxmoney.com/quote.php?qm_symbol=GE:US","span",1,">change:")

Returns: 0.140 (0.60%)

But I'd like to use the historical data and it also contains the same information at the top, so it should return the same.

=smfGetTagContent("http://web.tmxmoney.com/pricehistory.php?qm_symbol=GE:US","span",1,">change:")

Returns: error

The page is basically the same except I'd like to get this historical table, but nothing on this page gets captured.

I think you commented before that this page is returning strange characters.

Do you think there's a workaround as it opens the page no problem in Firefox or I/E.

Maybe I'm missing something..

Thanks

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Why are you using that URL? There's no high listed on that web page. Which
> is why you get "Error" returned. Instead, try:
>
> =RCHGetTableCell("http://web.tmxmoney.com/quote.php?qm_symbol=AAV
> ",1,">High:")
>
> On Wed, Mar 20, 2013 at 11:24 AM, Jim <buckleca@...> wrote:
>
> >
> > I assume the same thing is happening with this code:
> >
> > =rchGetTableCell("http://web.tmxmoney.com/company.php?qm_symbol=AAV
> > ",1,">High:")
> >
> > Returns "error" in all the functions
> >
> > If so, I was wondering if there's a workaround since this page appears
> > fine in Firefox and I/E?
> >
>

Wed Mar 20, 2013 7:07 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

XMLHTTP, what the add-in uses to get web pages, won't even retrieve the
http://web.tmxmoney.com/quote.php?qm_symbol=GE:US web page.

On Wed, Mar 20, 2013 at 6:48 PM, Jim <buckleca@yahoo.com> wrote:

>
> Sorry, I didn't give you the right example. Like your example this works:
>
> =smfGetTagContent("http://web.tmxmoney.com/quote.php?qm_symbol=GE:US
> ","span",1,">change:")
>
> Returns: 0.140 (0.60%)
>
> But I'd like to use the historical data and it also contains the same
> information at the top, so it should return the same.
>
> =smfGetTagContent("
> http://web.tmxmoney.com/pricehistory.php?qm_symbol=GE:US
> ","span",1,">change:")
>
> Returns: error
>
> The page is basically the same except I'd like to get this historical
> table, but nothing on this page gets captured.
>
> I think you commented before that this page is returning strange
> characters.
>
> Do you think there's a workaround as it opens the page no problem in
> Firefox or I/E.
>
> Maybe I'm missing something..
>

Wed Mar 20, 2013 7:14 pm (PDT) . Posted by:

"Jim" buckleca



So they are doing something to block it?

Or is this a limitation of XMLHTTP?

I assume it's beyond the scope of the functions and I'm hooped.

Or do you like a challenge? :-)

Thanks for confirming that it just wasn't me!!

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> XMLHTTP, what the add-in uses to get web pages, won't even retrieve the
> http://web.tmxmoney.com/quote.php?qm_symbol=GE:US web page.
>
> On Wed, Mar 20, 2013 at 6:48 PM, Jim <buckleca@...> wrote:
>
> >
> > Sorry, I didn't give you the right example. Like your example this works:
> >
> > =smfGetTagContent("http://web.tmxmoney.com/quote.php?qm_symbol=GE:US
> > ","span",1,">change:")
> >
> > Returns: 0.140 (0.60%)
> >
> > But I'd like to use the historical data and it also contains the same
> > information at the top, so it should return the same.
> >
> > =smfGetTagContent("
> > http://web.tmxmoney.com/pricehistory.php?qm_symbol=GE:US
> > ","span",1,">change:")
> >
> > Returns: error
> >
> > The page is basically the same except I'd like to get this historical
> > table, but nothing on this page gets captured.
> >
> > I think you commented before that this page is returning strange
> > characters.
> >
> > Do you think there's a workaround as it opens the page no problem in
> > Firefox or I/E.
> >
> > Maybe I'm missing something..
> >
>

Wed Mar 20, 2013 7:41 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Not a clue as to why it won't get it...

XMLHTTP just gives a system error. Google search seems to imply some type
of encoding error of the web page versus the data request. The stated
workaround is to use the byte-by-byte response to build the web page. No
thanks.

On Wed, Mar 20, 2013 at 7:13 PM, Jim <buckleca@yahoo.com> wrote:

>
> So they are doing something to block it?
>
> Or is this a limitation of XMLHTTP?
>
> I assume it's beyond the scope of the functions and I'm hooped.
>
> Or do you like a challenge? :-)
>
> Thanks for confirming that it just wasn't me!!
>

Wed Mar 20, 2013 8:50 pm (PDT) . Posted by:

"Jim" buckleca

Thanks Randy for looking at this and I appreciate that it's not worth the hassle. Your add-in is great and I can get the data from other sources.

I was just interested to understand why if the web page loads OK in I/E that it's not the same via Excel. We'll move on.

Thanks again

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Not a clue as to why it won't get it...
>
> XMLHTTP just gives a system error. Google search seems to imply some type
> of encoding error of the web page versus the data request. The stated
> workaround is to use the byte-by-byte response to build the web page. No
> thanks.
>
> On Wed, Mar 20, 2013 at 7:13 PM, Jim <buckleca@...> wrote:
>
> >
> > So they are doing something to block it?
> >
> > Or is this a limitation of XMLHTTP?
> >
> > I assume it's beyond the scope of the functions and I'm hooped.
> >
> > Or do you like a challenge? :-)
> >
> > Thanks for confirming that it just wasn't me!!
> >
>

Wed Mar 20, 2013 11:31 am (PDT) . Posted by:

"rr76012" rr76012

Hi Randy,

I have learned from my past, that it is a best practice you ask your advise before I fool around with my smf spreadsheets. I am trying to get the last 5 years range of eps, dividends, and book value per share.

For eps Yr 1: =RCHGetElementNumber($B$4,5886)

For dividends Yr1: =RCHGetElementNumber($B$4,5896)

For book value Yr1: =RCHGetElementNumber($B$4,7686)

All are getting an "Error" message

Should I replace my addin or download and update the txt files? what advise would you give me? thanks...

I don't want to replace my add in or txt files without first asking you.

Thank you.

Happy Easter to you and yours. :+)

Wed Mar 20, 2013 2:00 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Those elements work fine for me. You may need to apply the "fix" for AdvFN
found in the LINKS area of the Yahoo group...

On Wed, Mar 20, 2013 at 11:31 AM, rr76012 <rr76012@yahoo.com> wrote:

>
> I have learned from my past, that it is a best practice you ask your
> advise before I fool around with my smf spreadsheets. I am trying to get
> the last 5 years range of eps, dividends, and book value per share.
>
> For eps Yr 1: =RCHGetElementNumber($B$4,5886)
>
> For dividends Yr1: =RCHGetElementNumber($B$4,5896)
>
> For book value Yr1: =RCHGetElementNumber($B$4,7686)
>
> All are getting an "Error" message
>
> Should I replace my addin or download and update the txt files? what
> advise would you give me? thanks...
>
> I don't want to replace my add in or txt files without first asking you.
>

Wed Mar 20, 2013 1:46 pm (PDT) . Posted by:

"rr76012" rr76012

Hi Randy,

I have learned from my past, that it is a best practice you ask your advise before I fool around with my smf spreadsheets. I am trying to get the last 5 years info on of eps, dividends, dividends, book value, dividends by changing the element number for each of the 5 years. example yr 1:

eps Yr 1-5: =RCHGetElementNumber($B$4,5886) etc..

dividends Yr 1-5: =RCHGetElementNumber($B$4,5896) etc..

book value Yr 1-5: =RCHGetElementNumber($B$4,7686) etc..

ROE value Yr 1-5: =RCHGetElementNumber($B$4,7296) etc..

All 4 of these are getting an "Error" message.

Should I replace my addin or download and update the txt files? what advise would you give me? thanks...

I don't want to replace my addin or txt files without first asking you.

Thank you.

Happy Easter to you and yours. :+)

Wed Mar 20, 2013 10:30 pm (PDT) . Posted by:

"tar044" tar044

Randy,

Maybe this info will be useful, maybe not. You probably already know
it.

I was having trouble with my spreadsheet not updating, using your
smfForceRecalculation and even manually. I could see the info on the
website had newer and different data than the spreadsheet. After many
hours of debugging I traced it to the XMLHTTP not making a new request
to the website, but rather pulling some (microsoft) cached data from
somewhere (I think we all know where).

After many many more hours googling this I found a solution here:
https://groups.google.com/forum/?hl=en&fromgroups=#!topic/microsoft.publ\
ic.excel.programming/oRQeipGKCzM

I implemeted this by moding a line in your "RCHGetURLData1" function
(for my case) as follows:

Original:
oHTTP.Open pType, pURL, False

Modified:
oHTTP.Open pType, pURL & "&" & Format(Time, "hhmmss"), False

It now updates everytime I run your "smfForceRecalculation" macro.

If you know of how to modify these magical microsoft cache settings I
would be interested. I could not find that after many additional hours
of googling.

Note: I am a poor googler.

Tony

Tidak ada komentar:

Posting Komentar