Sabtu, 24 Desember 2011

[smf_addin] Digest Number 2056

Messages In This Digest (8 Messages)

Messages

1a.

Re: Calling ADVfn data using =RCHGetElementNumber(Ticker,Element#)

Posted by: "dan-carroll" dan-carroll@yahoo.com   dan-carroll

Fri Dec 23, 2011 4:19 am (PST)



Pete,

I built out and extended my models using a spreadsheet example from the member uploads section. You can download it yourself:
10 Year Financial Summary.xlsx
10 Year Financial Summary - ver 2007 Excel using AdvFN
By mrpatmcginnis Aug 26, 2010

It calls dozens and dozens and dozens of AdvFN elements.
It worked for me for over a year. Now all elements produce "error" for any ticker that I enter.
Specifically MCD, MSFT, TMS and dozens of others.

I am about to try the fixes that Randy suggested above.

--- In smf_addin@yahoogroups.com, "Pete" <petekoch@...> wrote:
>
> I opened a workbook which uses hundreds of AdvFN elements with no problems. You need to be more specific, e.g., the ticker(s) and the specific element number(s) before someone can help you.
>
> --- In smf_addin@yahoogroups.com, "dan-carroll" <dan-carroll@> wrote:
> >
> > I have built a number of spreadsheets that use the ADVfn data to help me model out companies. They pull historical data using the format
> > =RCHGetElementNumber(Ticker,Element#)
> > Where the Element numbers are taken from the definition list.
> > I have the latest copy of the add-in and have updated all the definition files.
> >
> > Everything has been working really well for over a year, and then it stopped working about a week ago - all my sheet, all the data pulls from ADVfn produce "Error".
> > Any data that is pulled by RCHGetElementNumber from element numbers corresponding to Reuters or other providers work just fine.
> >
> > I've tried the fix indicated on the Links page, but it doesn't have any effect.
> > I am using Excel 2007 on Windows XP.
> > Did something change a couple of weeks ago, are there other fixes I should try?
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)
1b.

Re: Calling ADVfn data using =RCHGetElementNumber(Ticker,Element#)

Posted by: "dan-carroll" dan-carroll@yahoo.com   dan-carroll

Fri Dec 23, 2011 4:44 am (PST)



Randy,

I did close and reopen both IE and Excel after applying the IE fix - to no avail.
I tried the smfForceRecalculation macro also.
I guess what surprises me is that it was all working until a week ago, and yet your other members seem not to be having a problem with it, so it sounds like an error on my part.

Otherwise, when you go to this web page in the EXCEL Web Query process, > does the URL in the address bar change from what you enter? That is, did > you get redirected to a different web page?
http://www.advfn.com/p.php?pid=financials&mode=annual_reports&symbol=MMM
REsult: Went to the page OK, was not redirected.

The three web queries you suggest, when put into a blank cell on a new sheet produce, in order:
"<html lang='en-GB'>"
year end date
2006/12

As I said before, the Elements corresponding to non-ADVFn elements seem to be working fine.
It seems like the most likely explanation is that I have done something strange.
I will probably try a full reinstall using the lastest beta version of the SMF add-in

Sorry to bug you with this.
Your work has been an absolute Godsend to me, but now that my sheets don't work I'm realizing how vital it is to me!
--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Did you run the smfForceRecalculation macro (see "Links" area of group) or
> exit and re-enter EXCEL after applying the IE fix? Otherwise, you'd just be
> extracting the data from the same saved web pages.
>
> Otherwise, when you go to this web page in the EXCEL Web Query process,
> does the URL in the address bar change from what you enter? That is, did
> you get redirected to a different web page?
>
> http://www.advfn.com/p.php?pid=financials&mode=annual_reports&symbol=MMM
>
> And, after that, do you get "Error" with any of:
>
> =RCHGetWebData("http://www.advfn.com/p.php?pid=financials&mode=annual_reports&symbol=MMM",1,20)
> =RCHGetTableCell("
> http://www.advfn.com/p.php?pid=financials&mode=annual_reports&symbol=MMM",0,">Year
> end date")
> =RCHGetTableCell("
> http://www.advfn.com/p.php?pid=financials&mode=annual_reports&symbol=MMM",1,">Year
> end date")
>
> On Thu, Dec 22, 2011 at 12:48 PM, dan-carroll <dan-carroll@...> wrote:
>
> > I have built a number of spreadsheets that use the ADVfn data to help me
> > model out companies. They pull historical data using the format
> > =RCHGetElementNumber(Ticker,Element#)
> > Where the Element numbers are taken from the definition list.
> > I have the latest copy of the add-in and have updated all the definition
> > files.
> >
> > Everything has been working really well for over a year, and then it
> > stopped working about a week ago - all my sheet, all the data pulls from
> > ADVfn produce "Error".
> > Any data that is pulled by RCHGetElementNumber from element numbers
> > corresponding to Reuters or other providers work just fine.
> >
> > I've tried the fix indicated on the Links page, but it doesn't have any
> > effect.
> > I am using Excel 2007 on Windows XP.
> > Did something change a couple of weeks ago, are there other fixes I should
> > try?
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)
1c.

Re: Calling ADVfn data using =RCHGetElementNumber(Ticker,Element#)

Posted by: "dan-carroll" dan-carroll@yahoo.com   dan-carroll

Fri Dec 23, 2011 6:19 am (PST)



OK, it appears to be working now.

I reversed and then redid the ADVfn fix, closed IE and Excel again, rebooted the computer and then re-ran the smfForceRecalculation macro (I now have it set up as a shortcut, same as you).

Not sure which of these did the job, or why they didn't do it the first time (almost definitely operator error), but it appears to be working again.

Once again, Randy, thanks a million for the work you do here. This is a huge benefit to me in the work I do, and I literally could not function without it. Many thanks and Happy Holidays

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Did you run the smfForceRecalculation macro (see "Links" area of group) or
> exit and re-enter EXCEL after applying the IE fix? Otherwise, you'd just be
> extracting the data from the same saved web pages.
>
> Otherwise, when you go to this web page in the EXCEL Web Query process,
> does the URL in the address bar change from what you enter? That is, did
> you get redirected to a different web page?
>
> http://www.advfn.com/p.php?pid=financials&mode=annual_reports&symbol=MMM
>
> And, after that, do you get "Error" with any of:
>
> =RCHGetWebData("
> http://www.advfn.com/p.php?pid=financials&mode=annual_reports&symbol=MMM
> ",1,20)
> =RCHGetTableCell("
> http://www.advfn.com/p.php?pid=financials&mode=annual_reports&symbol=MMM",0,">Year
> end date")
> =RCHGetTableCell("
> http://www.advfn.com/p.php?pid=financials&mode=annual_reports&symbol=MMM",1,">Year
> end date")
>
> On Thu, Dec 22, 2011 at 12:48 PM, dan-carroll <dan-carroll@...> wrote:
>
> > I have built a number of spreadsheets that use the ADVfn data to help me
> > model out companies. They pull historical data using the format
> > =RCHGetElementNumber(Ticker,Element#)
> > Where the Element numbers are taken from the definition list.
> > I have the latest copy of the add-in and have updated all the definition
> > files.
> >
> > Everything has been working really well for over a year, and then it
> > stopped working about a week ago - all my sheet, all the data pulls from
> > ADVfn produce "Error".
> > Any data that is pulled by RCHGetElementNumber from element numbers
> > corresponding to Reuters or other providers work just fine.
> >
> > I've tried the fix indicated on the Links page, but it doesn't have any
> > effect.
> > I am using Excel 2007 on Windows XP.
> > Did something change a couple of weeks ago, are there other fixes I should
> > try?
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)
2a.

RCHGetYahooQuotes Returning N/A for Symbol "INDU"

Posted by: "joe_mo37" joe_mo37@yahoo.com   joe_mo37

Fri Dec 23, 2011 11:30 am (PST)



I've been using the symbol INDU as a proxy for ^DJI (Dow Jones Industrial Average)with the RCHGetYahooQuotes function in a spreadsheet that I haven't used for awhile. When I tried to use this spreadsheet today it returns N/A for this symbol. I have another 22 symbols in the sheet that are all returning correct values. If I change INDU to ^DJI I get nothing returned. Is there another symbol I should be using to return the Dow Jones Industrial Average?

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
2b.

Re: RCHGetYahooQuotes Returning N/A for Symbol "INDU"

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

Fri Dec 23, 2011 12:13 pm (PST)



Can you use DIA instead? With the legal issues surrounding the DJIA quotes,
I'm not sure they are something to rely upon into the future. Or you could
always use the RCHGetTableCell() function and get the quotes directly from
the web page instead.

On Fri, Dec 23, 2011 at 12:30 PM, joe_mo37 <joe_mo37@yahoo.com> wrote:

> I've been using the symbol INDU as a proxy for ^DJI (Dow Jones Industrial
> Average)with the RCHGetYahooQuotes function in a spreadsheet that I haven't
> used for awhile. When I tried to use this spreadsheet today it returns N/A
> for this symbol. I have another 22 symbols in the sheet that are all
> returning correct values. If I change INDU to ^DJI I get nothing returned.
> Is there another symbol I should be using to return the Dow Jones
> Industrial Average?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
2c.

Re: RCHGetYahooQuotes Returning N/A for Symbol "INDU"

Posted by: "Joe Mo" joe_mo37@yahoo.com   joe_mo37

Fri Dec 23, 2011 1:44 pm (PST)



Randy,
Thanks for your suggestion. I compared DIA adjusted close to ^DJI adjusted close over the past two years and there is good enough correlation (correlation coefficient = 0.9865) that DIA will suit my purposes quite well.

Best wishes to you for a good hoilday season and New Year. And thank you for the outstanding work you do that benefits so many of us!
Joe

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Friday, December 23, 2011 2:13 PM
Subject: Re: [smf_addin] RCHGetYahooQuotes Returning N/A for Symbol "INDU"


 
Can you use DIA instead? With the legal issues surrounding the DJIA quotes, I'm not sure they are something to rely upon into the future. Or you could always use the RCHGetTableCell() function and get the quotes directly from the web page instead.

On Fri, Dec 23, 2011 at 12:30 PM, joe_mo37 <joe_mo37@yahoo.com> wrote:

I've been using the symbol INDU as a proxy for ^DJI (Dow Jones Industrial Average)with the RCHGetYahooQuotes function in a spreadsheet that I haven't used for awhile. When I tried to use this spreadsheet today it returns N/A for this symbol. I have another 22 symbols in the sheet that are all returning correct values. If I change INDU to ^DJI I get nothing returned. Is there another symbol I should be using to return the Dow Jones Industrial Average?
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
3a.

Re: get ^VIX options quote

Posted by: "jr105_" john.richards21@comcast.net   jr105_

Fri Dec 23, 2011 12:10 pm (PST)



Has anyone figured this out yet?

I would like to get this quote (the last quote for Jan 2012 VIX $25 call option) and cannot get it to work:

=smfGetOptionQuotes("^VIX Jan 2012 $25 Call", "l")

It returns "Error" to the cell.

Randy's solution did not work.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> The problem is that they use a non-standard monthly option expiration date.
> Try specifying the exact date:
>
> ^VIX 12/22 2010 $18 Call
>
> Also, note that Yahoo's mixture of the BVZ and VIX options on the same page
> may cause issues. The add-in just looks up things by date, strike, and type
> of call, so it currently has no way to differentiate between the two on
> Yahoo.
>
> On Sun, Dec 12, 2010 at 1:35 PM, Leandro <lean2708@...> wrote:
>
> >
> > I am trying to use smfGetOptionQuotes to retrieve a ^VIX option quote but
> > run into a problem. It seems to work fine if I determine the strike as
> > OTM1, OTM2, etc, in the ticker, however when I put the actual strike number,
> > i.e. 18, it brings Error. Any way around this that you could share?
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
3b.

Re: get ^VIX options quote

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

Fri Dec 23, 2011 12:28 pm (PST)



My solution, specifying the exact expiration date, is currently working for
me:

=smfGetOptionQuotes("VIX 1/18 2012 $25 Call", "l")

"Jan 2012" is going to have the function look up the standard monthly
expiration date for Jan 2012, which is not used by VIX.

On Fri, Dec 23, 2011 at 12:29 PM, jr105_ <john.richards21@comcast.net>wrote:

> Has anyone figured this out yet?
>
> I would like to get this quote (the last quote for Jan 2012 VIX $25 call
> option) and cannot get it to work:
>
> =smfGetOptionQuotes("^VIX Jan 2012 $25 Call", "l")
>
> It returns "Error" to the cell.
>
> Randy's solution did not work.
>
> --- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
> >
> > The problem is that they use a non-standard monthly option expiration
> date.
> > Try specifying the exact date:
> >
> > ^VIX 12/22 2010 $18 Call
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
Recent Activity
Visit Your Group
New business?

Get new customers.

List your web site

in Yahoo! Search.

Yahoo! News

Get it all here

Breaking news to

entertainment news

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

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.

Diposting oleh EXCEL FEED LEARNING di 02.06

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)