Jumat, 09 September 2011

[smf_addin] Digest Number 1948

Messages In This Digest (10 Messages)

1a.
Re: Return 0 instead of error From: wu_ming_sg
2a.
Slight data differences From: wu_ming_sg
2b.
Re: Slight data differences From: Randy Harmelink
3a.
Re: Nuveen Closed End funds From: msimmsx
3b.
Re: Nuveen Closed End funds From: Randy Harmelink
3c.
Re: Nuveen Closed End funds From: msimmsx
3d.
Re: Nuveen Closed End funds From: msimmsx
3e.
Re: Nuveen Closed End funds From: msimmsx
4a.
Spreadsheet Auto-Update From: Mark
4b.
Re: Spreadsheet Auto-Update From: Randy Harmelink

Messages

1a.

Re: Return 0 instead of error

Posted by: "wu_ming_sg" nghongchong@hotmail.com   wu_ming_sg

Thu Sep 8, 2011 4:46 am (PDT)



thanks! I got it!
--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...>
wrote:
>
> Check the documentation. One of the parameters determines what to
return for
> an error, but defaults to a value of "Error".
>
> On Thu, Sep 8, 2011 at 1:26 AM, wu_ming_sg nghongchong@... wrote:
>
> >
> > I have one formula below.
> >
> > =RCHGetTableCell("
> >
http://investing.businessweek.com/businessweek/research/stocks/financial\
s/financials.asp?ticker=
"&$C$8&"&dataset=incomeStatement&period=A&curren\
cy=US%20Dollar",1,">interest
> > expense")
> >
> > Sometimes , Interest Expense does not exist in the statement. So the
> > formula would return "Error".
> >
> > How do I modify it so that it can return "0" instead?
> >
>

2a.

Slight data differences

Posted by: "wu_ming_sg" nghongchong@hotmail.com   wu_ming_sg

Thu Sep 8, 2011 5:06 am (PDT)



Hi,
I experienced the below data having slight diferences.
http://investing.businessweek.com/businessweek/research/stocks/financial\
s/financials.asp?ticker=ARMH:US&dataset=incomeStatement&period=A&currenc\
y=US%20Dollar

<http://investing.businessweek.com/businessweek/research/stocks/financia\
ls/financials.asp?ticker=ARMH:US&dataset=incomeStatement&period=A&curren\
cy=US%20Dollar
> Revenues415.0478.7488.5651.1
My formula is
(=RCHGetTableCell("http://investing.businessweek.com/businessweek/resear\
ch/stocks/financials/financials.asp?ticker=
"&Ticker&"&dataset=incomeStat\
ement&period=A&currency=US%20Dollar",1,">total revenues")).

My template pulled 414.9 478.6 488.3 651.

I have tried refreshing the website and closing and reopening the
template but the slight difference is still there.
Any idea?
2b.

Re: Slight data differences

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

Thu Sep 8, 2011 8:50 am (PDT)



I'm going to dub it a website problem. If I go to that web page in FireFox
and refresh the page several times, I get slightly different numbers with
each refresh. Same thing using the add-in -- if I run the
smfForceRecalculation macro to have it get a fresh copy of the web page, the
numbers also change slightly.

On Thu, Sep 8, 2011 at 5:06 AM, wu_ming_sg <nghongchong@hotmail.com> wrote:

>
> I experienced the below data having slight diferences.
>
>
> http://investing.businessweek.com/businessweek/research/stocks/financials/financials.asp?ticker=ARMH:US&dataset=incomeStatement&period=A&currency=US%20Dollar
> Revenues415.0478.7488.5651.1
> My formula is (=RCHGetTableCell("
> http://investing.businessweek.com/businessweek/research/stocks/financials/financials.asp?ticker="&Ticker&"&dataset=incomeStatement&period=A&currency=US%20Dollar",1,">total
> revenues")).
>
> My template pulled 414.9 478.6 488.3 651.
>
>
> I have tried refreshing the website and closing and reopening the template
> but the slight difference is still there.
>
> Any idea?
>
3a.

Re: Nuveen Closed End funds

Posted by: "msimmsx" marksimms@verizon.net   msimmsx

Thu Sep 8, 2011 8:59 am (PDT)



--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> But in order to refresh the query table, it needs to have a bookmarkable
> URL. It doesn't pull the HTML from that copy of IE. It's just used to create
> the query table settings (URL, which table(s), etc).
>
> I just tried it, and what's happening is that all the check boxes are
> "sticky". They must store the check box settings in a cookie. So if you do a
> query, you'll get the settings based on that combination of check boxes.

Yes Randy, I discovered that as well....but it's OK just to pull down everything (600+ funds) with all of the checkboxes turned on.

Now my problem is that GetHTMLTable fails to retrieve anything. I tried tons of different search arguments and forward/backward search settings. Something in the HTML appears to "trip-up" this function. It runs thru a few rows and then just exits the loop.

3b.

Re: Nuveen Closed End funds

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

Thu Sep 8, 2011 9:04 am (PDT)



This got me all 636 funds when I had all check boxes selected:

=RCHGetHTMLTable("http://www.cefconnect.com/Pricing/DailyPricing.aspx",">Fund
Name",-1,"",1)

On Thu, Sep 8, 2011 at 8:59 AM, msimmsx <marksimms@verizon.net> wrote:

> --- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
> >
> > But in order to refresh the query table, it needs to have a bookmarkable
> > URL. It doesn't pull the HTML from that copy of IE. It's just used to
> create
> > the query table settings (URL, which table(s), etc).
> >
> > I just tried it, and what's happening is that all the check boxes are
> > "sticky". They must store the check box settings in a cookie. So if you
> do a
> > query, you'll get the settings based on that combination of check boxes.
>
> Yes Randy, I discovered that as well....but it's OK just to pull down
> everything (600+ funds) with all of the checkboxes turned on.
>
> Now my problem is that GetHTMLTable fails to retrieve anything. I tried
> tons of different search arguments and forward/backward search settings.
> Something in the HTML appears to "trip-up" this function. It runs thru a few
> rows and then just exits the loop.
>
3c.

Re: Nuveen Closed End funds

Posted by: "msimmsx" marksimms@verizon.net   msimmsx

Thu Sep 8, 2011 10:57 am (PDT)



Wow - thanks so much Randy. I still don't fully understand all of the various strategies required in setting the parameters for this beast. It is quite powerful, but also quite tricky.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> This got me all 636 funds when I had all check boxes selected:
>
> =RCHGetHTMLTable("http://www.cefconnect.com/Pricing/DailyPricing.aspx",">Fund
> Name",-1,"",1)
>

3d.

Re: Nuveen Closed End funds

Posted by: "msimmsx" marksimms@verizon.net   msimmsx

Thu Sep 8, 2011 12:59 pm (PDT)



Strangely I received only 10 rows back....as if there were only 1 checkbox indicated...which is the default. However, when I login via IE 6 browser, I automatically get all 600+ funds...everything is checked-off.
I also tried passing this parameter: ?AspxAutoDetectCookieSupport=1
And that made no difference as well.
I guess this is a cookies problem...and it appears the IE automation API is not picking them up for some reason.

3e.

Re: Nuveen Closed End funds

Posted by: "msimmsx" marksimms@verizon.net   msimmsx

Thu Sep 8, 2011 1:06 pm (PDT)



Nevermind.....my "duh"...forgot to override the pDim1, pDim2 parameters.
It's working great now !

--- In smf_addin@yahoogroups.com, "msimmsx" <marksimms@...> wrote:
>
> Strangely I received only 10 rows back....as if there were only 1 checkbox indicated...which is the default. However, when I login via IE 6 browser, I automatically get all 600+ funds...everything is checked-off.
> I also tried passing this parameter: ?AspxAutoDetectCookieSupport=1
> And that made no difference as well.
> I guess this is a cookies problem...and it appears the IE automation API is not picking them up for some reason.
>

4a.

Spreadsheet Auto-Update

Posted by: "Mark" drklein01@gmail.com   drmark27

Thu Sep 8, 2011 12:13 pm (PDT)



I'm new here so let me first give thanks to you, Randy, for sharing your work with us and continuing to offer your time in monitoring and responding to this group.

What I want to do is maintain an historical Excel database that I can easily auto-update at the end of each day. I have a starting date in mind so what I have done thus far is size the 3-column array accordingly so as to have the starting date at the top and the most recent date at the bottom.

I suspect this is not the best setup for auto-updating the database, however. I actually have several ticker symbols that I wish to update every day in the same size array. The formula I have been using is =RCHGetYahooHistory("XYZ",,,,,,,,"TDA",,,1) since all I care about is the ticker, date, and adjusted close and I want the desired starting date to be at the top. Tomorrow morning if I wish to add today's close, though, it appears I will have to click on the upper-left cell, highlight the entire array plus one additional row, and ctrl-shift-enter . That will be relatively cumbersome to do with several tickers in non-consecutive columns of one spreadsheet.

Is there a way I can have the arrays automatically fill from the desired start date through the most recent day's quote upon every opening of the spreadsheet?

Thanks!

4b.

Re: Spreadsheet Auto-Update

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

Thu Sep 8, 2011 12:16 pm (PDT)



No reason to auto-update at the end of each day, or maintain a database.
When you open the workbook, the formulas will get the currently available
data from the Internet. There should be no reason for you to be re-entering
the formulas -- perhaps you have calculation set to manual?

On Thu, Sep 8, 2011 at 11:47 AM, Mark <drklein01@gmail.com> wrote:

> I'm new here so let me first give thanks to you, Randy, for sharing your
> work with us and continuing to offer your time in monitoring and responding
> to this group.
>
> What I want to do is maintain an historical Excel database that I can
> easily auto-update at the end of each day. I have a starting date in mind
> so what I have done thus far is size the 3-column array accordingly so as to
> have the starting date at the top and the most recent date at the bottom.
>
> I suspect this is not the best setup for auto-updating the database,
> however. I actually have several ticker symbols that I wish to update every
> day in the same size array. The formula I have been using is
> =RCHGetYahooHistory("XYZ",,,,,,,,"TDA",,,1) since all I care about is the
> ticker, date, and adjusted close and I want the desired starting date to be
> at the top. Tomorrow morning if I wish to add today's close, though, it
> appears I will have to click on the upper-left cell, highlight the entire
> array plus one additional row, and ctrl-shift-enter . That will be
> relatively cumbersome to do with several tickers in non-consecutive columns
> of one spreadsheet.
>
> Is there a way I can have the arrays automatically fill from the desired
> start date through the most recent day's quote upon every opening of the
> spreadsheet?
>
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
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.


A Bad Credit Score is 600 or Below. Your Score? Find out at freecreditscore.com.

Tidak ada komentar:

Posting Komentar