Rabu, 15 Juni 2011

[smf_addin] Digest Number 1857

Messages In This Digest (15 Messages)

Messages

1a.

Re: OPTION QUOTES

Posted by: "smortonm" smmarder@yahoo.com   smortonm

Tue Jun 14, 2011 6:31 am (PDT)



Thank you. However is there an element number for the bid and ask quotes For example if I enter: =RCHGetOptionQuotes(A1,"l",1) and enter either an MSN or YAhoo option symbol I get the response: #NAME?

Any advice greatly appreciated
Sid

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Use the smfGetOptionQuotes() function instead.
>
> For stock/ETF/mutual fund quotes, you should be using the
> RCHGetYahooQuotes() function.
>
> On Mon, Jun 13, 2011 at 4:57 PM, smortonm <smmarder@...> wrote:
>
> > I notice that I can now import the last price of an option from MSN by
> > using element # 25. However there does not seem to be an element number for
> > MSN ask or bid price as there is in Yahoo. Do you know if such an element #
> > exists that would allow importing the bid/ask on options.
> >
>

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

Re: OPTION QUOTES

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

Tue Jun 14, 2011 8:15 am (PDT)



It's smfGetOptionQuotes(), not RCHGetOptionQuotes()...but note that you
wouldn't use an MSN or Yahoo option symbol.

No element number. In fact, I've considered getting rid of element #25. It
shouldn't be used to get a current quote. I added it because I was adding
almost everything they had available on the web page (which didn't include
bid and ask simply because it wasn't a current quotes web page). The
RCHGetElementNumber() function is meant for more stable data items.

On Tue, Jun 14, 2011 at 6:31 AM, smortonm <smmarder@yahoo.com> wrote:

> Thank you. However is there an element number for the bid and ask quotes
> For example if I enter: =RCHGetOptionQuotes(A1,"l",1) and enter either an
> MSN or YAhoo option symbol I get the response: #NAME?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
2a.

Re: How to extract competitor's ticker symbols from this link

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

Tue Jun 14, 2011 8:12 am (PDT)



I'd suggest breaking the formula down into its component steps, to see which
function is failing. You might also try visiting the web page using the
EXCEL Web Query process in EXCEL 2003. Maybe it uses a different version of
IE and needs to have something set up in a cookie.

On Tue, Jun 14, 2011 at 5:13 AM, Au <uanant@ymail.com> wrote:

> You suggested this following line to get the competitor's data from
> google finance.
> =SUBSTITUTE(smfWord(smfStrExtr(smfWord(RCHGetWebData("
> http://www.google.com/finance?client=ig&q=NASDAQ:PACR
> ","related:"),row,"values:"),"[","]"),column,""","""),"""","")
>
> This line worked great with excel 2007 but its not working with excel 2003,
> do you think there could be any problem. I downloaded the lasted add-in from
> work in progress as you said in your e-mail. Put Row=2 and Column = 1
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (18)
2b.

Re: How to extract competitor's ticker symbols from this link

Posted by: "Au" uanant@ymail.com   uanant@ymail.com

Tue Jun 14, 2011 8:27 am (PDT)



Randy,
I am very new to this, I barely managed to a point where I know how to use 
=RCHGetElementNumber(ticker,number) 
using excel 2003 I tried web query and it did not show the link, I had to take the word related out to see the page but there was no selection to make and pull the data as we do in case of importing a web-table. 
I mean it is working with excel 2007, I can get by with it for now.
Thanks for all your help
Aku

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Tuesday, June 14, 2011 11:12 AM
Subject: Re: [smf_addin] How to extract competitor's ticker symbols from this link

 
I'd suggest breaking the formula down into its component steps, to see which function is failing. You might also try visiting the web page using the EXCEL Web Query process in EXCEL 2003. Maybe it uses a different version of IE and needs to have something set up in a cookie.

On Tue, Jun 14, 2011 at 5:13 AM, Au <uanant@ymail.com> wrote:

You suggested this following line to get the competitor's data from google finance. 
>=SUBSTITUTE(smfWord(smfStrExtr(smfWord(RCHGetWebData("http://www.google.com/finance?client=ig&q=NASDAQ:PACR","related:"),row,"values:"),"[","]"),column,""","""),"""","")
>
>
>
>This line worked great with excel 2007 but its not working with excel 2003, do you think there could be any problem. I downloaded the lasted add-in from work in progress as you said in your e-mail. Put Row=2 and Column = 1
>

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

Re: How to extract competitor's ticker symbols from this link

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

Tue Jun 14, 2011 9:23 am (PDT)



Here's what I mean by breaking the formula up into components:

D3: =SUBSTITUTE(smfWord(smfStrExtr(smfWord(RCHGetWebData("
http://www.google.com/finance?client=ig&q=NASDAQ:PACR
","related:"),3,"values:"),"[","]"),1,""","""),"""","")
D4: =RCHGetWebData("http://www.google.com/finance?client=ig&q=NASDAQ:PACR
","related:")
D5: =smfWord(D4,3,"values:")
D6: =smfStrExtr(D5,"[","]")
D7: =smfWord(D6,1,""",""")
D8: =SUBSTITUTE(D7,"""","")

D3 is the full formula.
D4 is the first step, which extracts the data starting at the "related:"
string.
D5 breaks out the 3rd company data from D4.
D6 restricts D5 to all the data for a single company.
D7 extracts the 1st word from D6.
D8 removes any extraneous double quotes.

D4 through D8 are just doing everything in D3, but just step by step.

So, the question is -- at what point is EXCEL 2003 failing?

On Tue, Jun 14, 2011 at 8:24 AM, Au <uanant@ymail.com> wrote:

> I am very new to this, I barely managed to a point where I know how to
> use
> =RCHGetElementNumber(ticker,number)
> using excel 2003 I tried web query and it did not show the link, I had to
> take the word related out to see the page but there was no selection to make
> and pull the data as we do in case of importing a web-table.
> I mean it is working with excel 2007, I can get by with it for now.
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (18)
2d.

Re: How to extract competitor's ticker symbols from this link

Posted by: "Au" uanant@ymail.com   uanant@ymail.com

Tue Jun 14, 2011 9:45 am (PDT)



Randy,
I tried your suggestions, in 2003 it failed at D3, Here are the outputs. 
D4 = long string almost a page long
D5 = ["HUBG","Hub Group, Inc.","NASDAQ","USD","36.00","+1.01","chg","2.89","1.22","29.61","1.35B","0.00","0.00","7.23","11.90","3.81","2.37","658968","HUBG"]},{id:"661019",

D6 = "HUBG","Hub Group, Inc.","NASDAQ","USD","36.00","+1.01","chg","2.89","1.22","29.61","1.35B","0.00","0.00","7.23","11.90","3.81","2.37","658968","HUBG"

D7 = "HUBG

D8 = HUBG

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Tuesday, June 14, 2011 12:22 PM
Subject: Re: [smf_addin] How to extract competitor's ticker symbols from this link

 
Here's what I mean by breaking the formula up into components:

D3: =SUBSTITUTE(smfWord(smfStrExtr(smfWord(RCHGetWebData("http://www.google.com/finance?client=ig&q=NASDAQ:PACR","related:"),3,"values:"),"[","]"),1,""","""),"""","")
D4: =RCHGetWebData("http://www.google.com/finance?client=ig&q=NASDAQ:PACR","related:")
D5: =smfWord(D4,3,"values:")
D6: =smfStrExtr(D5,"[","]")
D7: =smfWord(D6,1,""",""")
D8: =SUBSTITUTE(D7,"""","")

D3 is the full formula.
D4 is the first step, which extracts the data starting at the "related:" string.
D5 breaks out the 3rd company data from D4.
D6 restricts D5 to all the data for a single company.
D7 extracts the 1st word from D6.
D8 removes any extraneous double quotes.

D4 through D8 are just doing everything in D3, but just step by step.

So, the question is -- at what point is EXCEL 2003 failing?

On Tue, Jun 14, 2011 at 8:24 AM, Au <uanant@ymail.com> wrote:

I am very new to this, I barely managed to a point where I know how to use 
>=RCHGetElementNumber(ticker,number) 
>using excel 2003 I tried web query and it did not show the link, I had to take the word related out to see the page but there was no selection to make and pull the data as we do in case of importing a web-table. 
>I mean it is working with excel 2007, I can get by with it for now.
>

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

Re: How to extract competitor's ticker symbols from this link

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

Tue Jun 14, 2011 10:02 am (PDT)



Hmmm. D3 is just a combination of D4 thru D8. It should work if D4 thru D8
are working...

I actually created D4 thru D8 *from* D3.

On Tue, Jun 14, 2011 at 9:45 AM, Au <uanant@ymail.com> wrote:

> I tried your suggestions, in 2003 it failed at D3, Here are the outputs.
> D4 = long string almost a page long
> D5 = ["HUBG","Hub Group,
> Inc.","NASDAQ","USD","36.00","+1.01","chg","2.89","1.22","29.61","1.35B","0.00","0.00","7.23","11.90","3.81","2.37","658968","HUBG"]},{id:"661019",
>
> D6 = "HUBG","Hub Group,
> Inc.","NASDAQ","USD","36.00","+1.01","chg","2.89","1.22","29.61","1.35B","0.00","0.00","7.23","11.90","3.81","2.37","658968","HUBG"
>
> D7 = "HUBG
>
> D8 = HUBG
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (18)
2f.

Re: How to extract competitor's ticker symbols from this link

Posted by: "Au" uanant@ymail.com   uanant@ymail.com

Tue Jun 14, 2011 10:16 am (PDT)



Randy
I created this and kept it in 
D4 =RCHGetWebData("http://www.google.com/finance?client=ig&q="&Exchange1&":"&Ticker,"related:")
and reference everything to this cell and it worked like a charm, a way around but it works with much hassle. 

Thanks a lot for your help
Aku

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Tuesday, June 14, 2011 1:02 PM
Subject: Re: [smf_addin] How to extract competitor's ticker symbols from this link

 
Hmmm. D3 is just a combination of D4 thru D8. It should work if D4 thru D8 are working...

I actually created D4 thru D8 *from* D3.

On Tue, Jun 14, 2011 at 9:45 AM, Au <uanant@ymail.com> wrote:

I tried your suggestions, in 2003 it failed at D3, Here are the outputs. 
>D4 = long string almost a page long
>D5 = ["HUBG","Hub Group, Inc.","NASDAQ","USD","36.00","+1.01","chg","2.89","1.22","29.61","1.35B","0.00","0.00","7.23","11.90","3.81","2.37","658968","HUBG"]},{id:"661019",
>
>
>D6 = "HUBG","Hub Group, Inc.","NASDAQ","USD","36.00","+1.01","chg","2.89","1.22","29.61","1.35B","0.00","0.00","7.23","11.90","3.81","2.37","658968","HUBG"
>
>
>D7 = "HUBG
>
>
>D8 = HUBG
>

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

Re: List of ADRs & their countries

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

Tue Jun 14, 2011 9:46 am (PDT)



Thanks Farhan!

--- In smf_addin@yahoogroups.com, Farhan Ansari <farhan86a@...> wrote:
>
> TopForeignStocks.com (http://topforeignstocks.com/foreign-adrs-list/) can be
> precisely what you are looking for
>
> Cheers!
> Farhan
>
> On Mon, Jun 13, 2011 at 6:37 PM, malramsay <malramsay@...> wrote:
>
> >
> >
> > Hi all,
> >
> > I require a list of ADRs on the NYSE & NASDAQ & their HQ countries.
> >
> > I can't find this information in smf_addin. I've found some places on the
> > 'net that have this information but nowhere that is easily accessible (i.e.
> > I'd like one large list preferably).
> >
> > Can anyone point out a source?
> >
> > Thanks,
> > Mal
> >
> >
> >
>

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

Re: In RCHGetTableCell, need space for <BR>

Posted by: "Charley Kyd" kyd@exceluser.com   charleykyd

Tue Jun 14, 2011 11:58 am (PDT)



Ahhh...

I seldom wrap text, so I didn't notice the effect of your CHAR(10).

Thanks!

Charley

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Actually, the <br> is replaced with a form feed. If you format that cell so
> that it wraps, your result should actually be:
>
> Text
> Text
>
> ...which is what the <br> is intended to do.
>
> On Mon, Jun 13, 2011 at 10:22 PM, Charley Kyd <kyd@...> wrote:
>
> > RCHGetTableCell is returning data from a cell that has: Text<br>Text.
> > Because you strip out tags, the function is returning TextText.
> >
> > Just as you replace Chr(9) with a space, you need to replace <BR> and </BR>
> > with a space. If you add too many spaces, that's easier to correct in Excel
> > than it is to insert spaces to separate run-together words.
> >
>

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

Re: RCHGetYahooHistory, MA slope

Posted by: "Jacob Jose" pepecan47@yahoo.ca   pepecan47

Tue Jun 14, 2011 5:31 pm (PDT)



Randy, is there any way to get the last 51 days of data and do the AVERAGE of 
day 2 to 51? I don't see how to automate my workbook if I have to provide the
dates hard coded.
 Regards,
Jose L. Jacob

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Sun, June 12, 2011 9:42:42 PM
Subject: Re: [smf_addin] RCHGetYahooHistory, MA slope

 
The last two parameters are telling the function how many rows and columns of
data to return. Since all you've asked for is a single data item, the second
column will be filled with blanks. An average of 100 items, where 50 are blanks,
would give you the same result as an average of only the original 50 items.

You'd need to pass the date parameters to get the previous 50 days of data. For
example, hard-coding it could be:

=AVERAGE(RCHGetYahooHistory($A$3,2011,1,1,2011,6,9,,"C",0,1,,50,1))

The "2011,1,1" are just arbitrary values I chose to use -- as long as it is more
than 50 trading days ago, everything would work fine.

However, if you're doing a lot of this kind of thing, you'd be better off just
retrieving the array of data yourself and extracting it from there. Otherwise,
you're doing a new Internet access of each invocation of the
RCHGetYahooHistory() function.

On Sun, Jun 12, 2011 at 6:25 PM, Jacob Jose <pepecan47@yahoo.ca> wrote:

Another question: I am trying to work with the slope of the moving averages, in
order to identfy tops/bottoms.
>
>What I need to do is to substract the current moving average to the previos
>period MA.
>
>
>I tried the prvious period with the formula:
>=AVERAGE(RCHGetYahooHistory(A3,,,,,,,,"C",0,1,,50,2))
>
>but it gives me the same results as
>=AVERAGE(RCHGetYahooHistory(A3,,,,,,,,"C",0,1,,50,1))
>
>Maybe I don't understand what is the meaning of the last parameter.
>
>My question is, how can I get the MA of the previous period, i.e yesterday
>

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

Re: RCHGetYahooHistory, MA slope

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

Tue Jun 14, 2011 6:00 pm (PDT)



Not using the same method. There are times I would like to use the OFFSET()
function to extract data from the function, but OFFSET() only accepts a
range as input. However, INDEX() does allow either a range or an array as
an input, so you could do it this way:

=INDEX(smfTech(RCHGetYahooHistory("MMM",,,,,,,,,,1,1,100,6),"SMA",50),100)
=INDEX(smfTech(RCHGetYahooHistory("MMM",,,,,,,,,,1,1,100,6),"SMA",50),99)

The first gets you the current 50-day SMA. The second gets you the previous
50-day SMA.

Another option would be to mathematically compute it. Today's 50-day SMA and
yesterday's 50-day SMA have 49 common days that went into their calculation.
So, really, you're just looking at the change between today's closing price
and the the closing price from 51 days ago, and then dividing that
difference by 50. So:

=(RCHGetYahooHistory("MMM",,,,,,,,"A",0,,,1,1)-INDEX(RCHGetYahooHistory("MMM",,,,,,,,"A",0,,,51,1),51))/50

I would just drop the division by 50 and call it a 50-day rate-of-change.

On Tue, Jun 14, 2011 at 5:31 PM, Jacob Jose <pepecan47@yahoo.ca> wrote:

>
> Randy, is there any way to get the last 51 days of data and do the
> AVERAGE of day 2 to 51? I don't see how to automate my workbook if I have
> to provide the dates hard coded.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (27)
6a.

Re: Incomplete Update and Missing Values

Posted by: "Bob" bbockjr@yahoo.com   bbockjr

Tue Jun 14, 2011 5:36 pm (PDT)



Randy,

As you suggested, I created a new worksheet from scratch, entered the formula, saved it, and reopened it and I did not get the error. That was promising.

Then, I opened the spreadsheet with the one formula, got the error, deleted the cell, saved it, reopened it and did not get the error. So it's probably the formula.

I tried creating a new workbook and copying each of the sheets in my original workbook to it and I got a "NAME!" error in all of the cells containing the formula (because the add-in at the time was in c:\Program Files\SMF Add-In instead of c:\Users\Bob\SMF_Add-In). The link address was contained in all of the formula cells. I used the Replace function to remove it from all cells, saved the file, but when reopening, the error again occurred.

I seem to be able to copy smaller parts of a worksheet to a new blank one and I don't get the error, but when I add more sections containing the formula, or additional sheets, I get the error again. I'm still stumped.

I can send you two files. The one called RCHTestDate1.xlsx is the one that gives me the link error. RCHTestDate2.xlsx contains the same formula but does not. Can you supply your e-mail address or let me know how to send them to you?

I'd like to see if you get the same error. If you need files that have the add-in located in C:\Program Files\SMF Add-In, I can move mine back to there and recreate these files.

If you can find any difference or have any other suggestions for me to try, please let me know.

Thanks for all your help.

Bob



--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> It sounds to me like you have a link error unrelated to the add-in. If all
> of your formulas are resolving the links and removing the hard-coded path,
> then the message is not for those cells.
>
> Do you get the error if you create a new worksheet from scratch, save it,
> and re-open it?
>
> And, just out of curiosity, if you remove that last formula, save it, and
> reopen it, do you STILL get the error (that would confirm the error is not
> from add-in formula usage)?
>
> If nothing else works, can you send me the workbook you have with just a
> single formula? Maybe it'll be something obvious if I'm looking at it.
>
> On Sat, Jun 11, 2011 at 7:26 AM, Bob <bbockjr@...> wrote:
>
> > I don't have any path in the cells in my workbook. Each cell contains this
> > formula with varying range addresses:
> > =RCHGetYahooQuotes(A33:A232,"l1")
> >
> > The only way I can see a hard-coded path is if I deactivate the add-in.
> > Then the formula appears as:
> > ='C:\Program Files\SMF
> > Add-In\RCH_Stock_Market_Functions.xla'!RCHGetYahooQuotes(A33:A232,"l1")
> > If I remove this path, save the file with a cell formula that only contains
> > the function, I get the error again.
> >
> > If I reactivate the add-in, the path disappears in the cell, indicating to
> > me that it has found the add-in. But if I close the file, then reopen it, I
> > get the error.
> >
> > I have tried selectively removing the cells that contain the add-in, saving
> > the file with a new name, closing it, then opening it again, and I still get
> > the error.
> >
> > I removed everything but one cell with the formula:
> > =RCHGetYahooQuotes("KO","d1")
> > saved the file and I still get the error when I open it.
> >
> > I have redownloaded the zip file and reinstalled the add-in. No difference.
> >
> > I deleted the SMF Add-In folder in C:\Program Files and reinstalled it in
> > my user folder thinking maybe it was a rights issue (although I have
> > administrator rights as the only user of this computer), then reactivated
> > the add-in in its new location. No difference.
> >
> > I'm out of ideas.
> >
>

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

Excel 2010

Posted by: "North" nlee1988@yahoo.com   nlee1988

Tue Jun 14, 2011 11:31 pm (PDT)



I try to install the plugin but it doesn't seem to work with excel 2010.It is compatible with it??

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

Re: Excel 2010

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

Tue Jun 14, 2011 11:34 pm (PDT)



Others have reported successfully using the add-in with EXCEL 2010...

On Tue, Jun 14, 2011 at 10:47 PM, North <nlee1988@yahoo.com> wrote:

> I try to install the plugin but it doesn't seem to work with excel 2010.It
> is compatible with it??
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (9)
Recent Activity
Visit Your Group
Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! News

Fashion News

What's the word on

fashion and style?

Search Ads

Get new customers.

List your web site

in Yahoo! Search.

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
Diposting oleh EXCEL FEED LEARNING di 04.35

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)