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. "&$C$8&"&dataset=com/businessweek /research/ stocks/financial \
s/financials.asp?ticker= 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. > Revenues415.com/businessweek /research/ stocks/financia\
ls/financials.asp?ticker= ARMH:US&dataset= incomeStatement& period=A& curren\
cy=US%20Dollar0478.7488. 5651.1
My formula is
(=RCHGetTableCell("http://investing. businessweek. "&Ticker&"&dataset=com/businessweek /resear\
ch/stocks/financials/financials. asp?ticker= incomeStat\
ement&period=A¤cy= 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/financial s/financials. asp?ticker= ARMH:US&dataset= incomeStatement& period=A& currency= US%20Dollar
> Revenues415.0478.7488. 5651.1
> My formula is (=RCHGetTableCell("
> http://investing.businessweek. "&Ticker&"&dataset=com/businessweek /research/ stocks/financial s/financials. asp?ticker= 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.cefconne ct.com/Pricing/ ",">FundDailyPricing. aspx
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.cefconne ct.com/Pricing/ ",">FundDailyPricing. aspx
> 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?
>
Need to Reply?
Click one of the "Reply" links to respond to a specific message in the Daily Digest.
MARKETPLACE
Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Individual | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe
Tidak ada komentar:
Posting Komentar