Messages In This Digest (25 Messages)
- 1a.
- Re: Suitable template for downloading from Reuters From: Ego L.
- 1b.
- Re: Suitable template for downloading from Reuters From: Randy Harmelink
- 2a.
- Re: smfGetOptionQuotes not working for some datacodes From: Ashish Mukerji
- 2b.
- Re: smfGetOptionQuotes not working for some datacodes From: Randy Harmelink
- 3a.
- Re: adding a specific element number to smflite From: David
- 3b.
- Re: adding a specific element number to smflite From: Mike McQuaid
- 3c.
- Re: adding a specific element number to smflite From: Randy Harmelink
- 3d.
- Re: adding a specific element number to smflite From: Dave Charlaff
- 3e.
- Re: adding a specific element number to smflite From: Dave Charlaff
- 3f.
- Re: adding a specific element number to smflite From: Randy Harmelink
- 3g.
- Re: adding a specific element number to smflite From: David
- 3h.
- Re: adding a specific element number to smflite From: Mike McQuaid
- 3i.
- Re: adding a specific element number to smflite From: David
- 3j.
- Re: adding a specific element number to smflite From: Randy Harmelink
- 3k.
- Re: adding a specific element number to smflite From: Mike McQuaid
- 3l.
- Re: adding a specific element number to smflite From: David
- 4a.
- Re: 52w L & H From: V. Shankar
- 4b.
- Re: 52w L & H From: Randy Harmelink
- 5a.
- Re: Fw: Re: Yahoo Finance "Last Trade"cell From: randy tofsrud
- 5b.
- Re: Fw: Re: Yahoo Finance "Last Trade"cell From: Randy Harmelink
- 6a.
- Re: Version Difference?? From: kim54blake
- 7a.
- Re: Don't Know What Happened From: codyklein
- 7b.
- Re: Don't Know What Happened From: Mike McQuaid
- 7c.
- Re: Don't Know What Happened From: Ron Daring
- 7d.
- Re: [Bulk] Re: [smf_addin] Don't Know What Happened From: Mike McQuaid
Messages
- 1a.
-
Re: Suitable template for downloading from Reuters
Posted by: "Ego L." ego_1230@yahoo.de ego_1230
Mon Jan 16, 2012 4:38 am (PST)
Hi,
I'd also be interested in extracting Reuters quarterly financial data, but I encounter the same problems.
I've tried your link after logging in at Reuters with IE, but at best it gives me this: [In Millions of U.S. Dollars
(except for per share items)] .
Most of the time it delivers empty cells, not even an error message.
What am I doing wrong?
- 1b.
-
Re: Suitable template for downloading from Reuters
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Mon Jan 16, 2012 4:42 am (PST)
Sounds like you're putting the formula into a single cell, instead of
array-entering it over a range. From the documentation for the function:
*This function returns an array of data (the HTML table), so it needs to be
array-entered. To array-enter a formula in EXCEL, first highlight the range
of cells where you would like the returned data to appear -- the number of
rows and columns for the range will depend on the size of the table you are
retrieving and how much of that table you want to see. Next, enter your
formula and then press Ctrl-Shift-Enter.
*
On Mon, Jan 16, 2012 at 2:11 AM, Ego L. <ego_1230@yahoo.de > wrote:
>
> I've tried your link after logging in at Reuters with IE, but at best it
> gives me this: [In Millions of U.S. Dollars
> (except for per share items)] .
> Most of the time it delivers empty cells, not even an error message.
>
> What am I doing wrong?
>
- 2a.
-
Re: smfGetOptionQuotes not working for some datacodes
Posted by: "Ashish Mukerji" ashishkm@yahoo.com ashishkm
Mon Jan 16, 2012 7:13 am (PST)
Hello Randy ... thanks ... it certainly works as you described ... given that i barely hold my own with EXCEL and have no knowledge whatsoever of VBA, would it have been possible for me to find this mismatch of datacodes with current yahoo format? ... just wondering if i can learn a little bit more? ....
Ashish
>____________________ _________ ___
> From: Randy Harmelink <rharmelink@gmail.com >
>To: smf_addin@yahoogroups.com
>Sent: Monday, January 16, 2012 1:05 AM
>Subject: Re: [smf_addin] smfGetOptionQuotes not working for some datacodes
>
>
>
>Nothing wrong with the j and k data items -- N/A is what Yahoo currently has on their web pages.
>
>The c, u, and t data items stopped working because Yahoo changed the format of their quotes page and have dropped the "Last Trade:" and "Trade Time:" table items. However, I was able to pick up the data items from the "flash quote" at the top of the page to replace them. I just hope the changes are done with...
>
>I just uploaded version 2.1.2012.01.15 of the add-in to the "Works in Progress" folder with the changes. To upgrade, you need to exit EXCEL, download the new ZIP, extract its files into the SMF add-in folder, replacing files as needed. When you restart EXCEL, it should be running the new version. You can double check with:
>
>=RCHGetElementNumber("Version" )
>
>
>On Sun, Jan 15, 2012 at 9:52 PM, Ashish <ashishkm@yahoo.com > wrote:
>
>Hi Randy .... Thanks for such a big help with your brilliant product ... I am now totally dependent upon it for all my option transactions ... I have been using smfGetOptionQuotes successfully as a cell reference to obtain Option information on a daily basis ... for some reason the datacodes c,u,t,j and k stopped working; getting "error" and "N/A" result for all of 10 different option symbols ... the datacodes l,b,a,g,h,s are working fine .... I do not know if j and k ever worked ... I added them only today ... example of the format I am using is smfGetOptionQuotes($B4,J$2) .... Thanks in appreciation of your help ...
>>
>
>
>
> - 2b.
-
Re: smfGetOptionQuotes not working for some datacodes
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Mon Jan 16, 2012 7:32 am (PST)
Not really. It was some hard-coded data extractions in the VBA code.
Part of the reason I knew what to look for was someone else used to extract
the "Last Trade:" data on stocks and asked why it stopped working the other
day. So I knew Yahoo had changed the quotes web page. It just never
occurred to me to check on options as well...
On Mon, Jan 16, 2012 at 8:13 AM, Ashish Mukerji <ashishkm@yahoo.com > wrote:
>
> Hello Randy ... thanks ... it certainly works as you described ... given
> that i barely hold my own with EXCEL and have no knowledge whatsoever of
> VBA, would it have been possible for me to find this mismatch of datacodes
> with current yahoo format? ... just wondering if i can learn a little bit
> more? ....
>
- 3a.
-
Re: adding a specific element number to smflite
Posted by: "David" davecharlaff@hotmail.com copthornelabs
Mon Jan 16, 2012 7:27 am (PST)
Randy,
I cannot get this to work for me.
Could you set up a small spreadsheet say with AAPL,MSFT, C,ORCL and show me where I am going wrong?
#NAME? this is what I get using the RCHGettablecell function you outlined below.
Thanks.
Dave C.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Most elements are just hard-coded versions of the RCHGetTableCell()
> function. In this case:
>
> =RCHGetTableCell("http://finance. yahoo.com/ "&Ticker,1,"q/ks?s= ENTERPRISE
> VALUE/EBITDA")
>
> On Sun, Jan 15, 2012 at 3:09 PM, David <davecharlaff@...> wrote:
>
> > Could you suggest a format that would get that element number (949 Yahoo)
> > for me for my watchlist and put it into column B alonside the symbols in
> > col A?
> > Just looking for a starting format.
> >
>
- 3b.
-
Re: adding a specific element number to smflite
Posted by: "Mike McQuaid" mikemcq802@yahoo.com mikemcq802
Mon Jan 16, 2012 7:31 am (PST)
Did you define the name "ticker"?
Try:
=RCHGetTableCell(" <http://finance. yahoo.com/ >q/ks?s=
http://finance.yahoo.com/ ",1,"ENTERPRISE VALUE/EBITDA"q/ks?s=AAPL )
_____
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf
Of David
Sent: Monday, January 16, 2012 10:27 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: adding a specific element number to smflite
Randy,
I cannot get this to work for me.
Could you set up a small spreadsheet say with AAPL,MSFT, C,ORCL and show me
where I am going wrong?
#NAME? this is what I get using the RCHGettablecell function you outlined
below.
Thanks.
Dave C.
--- In smf_addin@yahoogroups.com <mailto:smf_addin%40yahoogro ups.com> ,
Randy Harmelink <rharmelink@...> wrote:
>
> Most elements are just hard-coded versions of the RCHGetTableCell()
> function. In this case:
>
> =RCHGetTableCell("http://finance. yahoo.com/ "&Ticker,1,"q/ks?s= ENTERPRISE
> VALUE/EBITDA")
>
> On Sun, Jan 15, 2012 at 3:09 PM, David <davecharlaff@...> wrote:
>
> > Could you suggest a format that would get that element number (949
Yahoo)
> > for me for my watchlist and put it into column B alonside the symbols in
> > col A?
> > Just looking for a starting format.
> >
>
- 3c.
-
Re: adding a specific element number to smflite
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Mon Jan 16, 2012 7:34 am (PST)
#NAME? usually means you are trying to use a function that doesn't exist.
Do you have the add-in installed, or do you just have the "Lite" functions
available? The "Lite" workbooks I've created in the past were typically for
only a single add-in function, not the entire add-in. I don't think I've
ever created a "Lite" version of RCHGetTableCell.
On Mon, Jan 16, 2012 at 8:27 AM, David <davecharlaff@hotmail.com > wrote:
>
> I cannot get this to work for me.
> Could you set up a small spreadsheet say with AAPL,MSFT, C,ORCL and show
> me where I am going wrong?
>
> #NAME? this is what I get using the RCHGettablecell function you outlined
> below.
>
- 3d.
-
Re: adding a specific element number to smflite
Posted by: "Dave Charlaff" davecharlaff@hotmail.com copthornelabs
Mon Jan 16, 2012 8:05 am (PST)
I just checked and it appears the add-in is no longer installed!!! I work with the Lite for getting daily prices and it appears that somewhere along the road of automatic upgrades perhaps the add-in (along with other stat add-ins I infrequently use) went by-bye.
I will reinstall and see how I go from there.
Thanks for the heads up.
By the way….perhaps a LITE version of RCHGetTableCell where I can array enter my list of symbols in col A and get specific element no returns in columns alongside , a-la- the SMFLITE that you have out there would be very useful for me.
Thanks for all your help Randy.
Dave C.
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf Of Randy Harmelink
Sent: Monday, January 16, 2012 10:35 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: adding a specific element number to smflite
#NAME? usually means you are trying to use a function that doesn't exist. Do you have the add-in installed, or do you just have the "Lite" functions available? The "Lite" workbooks I've created in the past were typically for only a single add-in function, not the entire add-in. I don't think I've ever created a "Lite" version of RCHGetTableCell.
On Mon, Jan 16, 2012 at 8:27 AM, David <davecharlaff@hotmail.com > wrote:
I cannot get this to work for me.
Could you set up a small spreadsheet say with AAPL,MSFT, C,ORCL and show me where I am going wrong?
#NAME? this is what I get using the RCHGettablecell function you outlined below.
- 3e.
-
Re: adding a specific element number to smflite
Posted by: "Dave Charlaff" davecharlaff@hotmail.com copthornelabs
Mon Jan 16, 2012 8:26 am (PST)
Addin reinstalled and it now works.
My kids are correct… I am an official doofus brain.
There was a post where you explained how to get the function to get the symbol to reference by referring &Ticker to another cell? Can you point me at that?
Is there a way to array enter this function so that I don't have to copy and change the symbol every time?
Thx
Dave C.
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf Of Randy Harmelink
Sent: Monday, January 16, 2012 10:35 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: adding a specific element number to smflite
#NAME? usually means you are trying to use a function that doesn't exist. Do you have the add-in installed, or do you just have the "Lite" functions available? The "Lite" workbooks I've created in the past were typically for only a single add-in function, not the entire add-in. I don't think I've ever created a "Lite" version of RCHGetTableCell.
On Mon, Jan 16, 2012 at 8:27 AM, David <davecharlaff@hotmail.com > wrote:
I cannot get this to work for me.
Could you set up a small spreadsheet say with AAPL,MSFT, C,ORCL and show me where I am going wrong?
#NAME? this is what I get using the RCHGettablecell function you outlined below.
- 3f.
-
Re: adding a specific element number to smflite
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Mon Jan 16, 2012 8:33 am (PST)
=RCHGetTableCell("http://finance. yahoo.com/ "&Ticker,1,"q/ks?s= ENTERPRISE
VALUE/EBITDA")
"Ticker" is a named cell reference, but it could be a normal cell reference
such as A1 or C3.
If using a cell reference, you don't need to edit the formula each time.
There's no reason to array-enter it, because it only returns a single item.
In general, I only resort to array-entered formulas where a single Internet
retrieval returns numerous data items, or a CSV file.
On Mon, Jan 16, 2012 at 9:26 AM, Dave Charlaff <davecharlaff@hotmail.com >wrote:
>
>
> There was a post where you explained how to get the function to get the
> symbol to reference by referring &Ticker to another cell? Can you point me
> at that?****
>
> Is there a way to array enter this function so that I don't have to copy
> and change the symbol every time?
>
- 3g.
-
Re: adding a specific element number to smflite
Posted by: "David" davecharlaff@hotmail.com copthornelabs
Mon Jan 16, 2012 8:43 am (PST)
That is what I thought.
AAPL Error
ADP Error
ADS Error
AET Error
This is what I get when I replace &Ticker with a cell reference like A1?
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> =RCHGetTableCell("http://finance. yahoo.com/ "&Ticker,1,"q/ks?s= ENTERPRISE
> VALUE/EBITDA")
>
> "Ticker" is a named cell reference, but it could be a normal cell reference
> such as A1 or C3.
>
> If using a cell reference, you don't need to edit the formula each time.
>
> There's no reason to array-enter it, because it only returns a single item.
> In general, I only resort to array-entered formulas where a single Internet
> retrieval returns numerous data items, or a CSV file.
>
> On Mon, Jan 16, 2012 at 9:26 AM, Dave Charlaff <davecharlaff@...>wrote:
>
> >
> >
> > There was a post where you explained how to get the function to get the
> > symbol to reference by referring &Ticker to another cell? Can you point me
> > at that?****
> >
> > Is there a way to array enter this function so that I don’t have to copy
> > and change the symbol every time?
> >
>
- 3h.
-
Re: adding a specific element number to smflite
Posted by: "Mike McQuaid" mikemcq802@yahoo.com mikemcq802
Mon Jan 16, 2012 8:51 am (PST)
You must leave the & in place - it's a concatenation operator
You can replace ticker with A1
_____
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf Of David
Sent: Monday, January 16, 2012 11:44 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: adding a specific element number to smflite
That is what I thought.
AAPL Error
ADP Error
ADS Error
AET Error
This is what I get when I replace &Ticker with a cell reference like A1?
--- In smf_addin@yahoogroups.com <mailto:smf_addin%40yahoogro ups.com> , Randy Harmelink <rharmelink@ ...> wrote:
>
> =RCHGetTableCell("http://finance. yahoo.com/ "&Ticker,1,"q/ks?s= ENTERPRISE
> VALUE/EBITDA")
>
> "Ticker" is a named cell reference, but it could be a normal cell reference
> such as A1 or C3.
>
> If using a cell reference, you don't need to edit the formula each time.
>
> There's no reason to array-enter it, because it only returns a single item.
> In general, I only resort to array-entered formulas where a single Internet
> retrieval returns numerous data items, or a CSV file.
>
> On Mon, Jan 16, 2012 at 9:26 AM, Dave Charlaff <davecharlaff@...>wrote:
>
> >
> >
> > There was a post where you explained how to get the function to get the
> > symbol to reference by referring &Ticker to another cell? Can you point me
> > at that?****
> >
> > Is there a way to array enter this function so that I don’t have to copy
> > and change the symbol every time?
> >
>
- 3i.
-
Re: adding a specific element number to smflite
Posted by: "David" davecharlaff@hotmail.com copthornelabs
Mon Jan 16, 2012 9:01 am (PST)
I understand.
Tried that first but I still get an error.
It works when I replace &TICKER with the symbol eg AAPL
could you give it a try and show me an example... perhaps
--- In smf_addin@yahoogroups.com , "Mike McQuaid" <mikemcq802@...> wrote:
>
> You must leave the & in place - it's a concatenation operator
>
> You can replace ticker with A1
>
> _____
>
> From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf Of David
> Sent: Monday, January 16, 2012 11:44 AM
> To: smf_addin@yahoogroups.com
> Subject: [smf_addin] Re: adding a specific element number to smflite
>
>
>
>
> That is what I thought.
> AAPL Error
> ADP Error
> ADS Error
> AET Error
>
> This is what I get when I replace &Ticker with a cell reference like A1?
> --- In smf_addin@yahoogroups.com <mailto:smf_addin%40yahoogro ups.com> , Randy Harmelink <rharmelink@ > wrote:
> >
> > =RCHGetTableCell("http://finance. yahoo.com/ "&Ticker,1,"q/ks?s= ENTERPRISE
> > VALUE/EBITDA")
> >
> > "Ticker" is a named cell reference, but it could be a normal cell reference
> > such as A1 or C3.
> >
> > If using a cell reference, you don't need to edit the formula each time.
> >
> > There's no reason to array-enter it, because it only returns a single item.
> > In general, I only resort to array-entered formulas where a single Internet
> > retrieval returns numerous data items, or a CSV file.
> >
> > On Mon, Jan 16, 2012 at 9:26 AM, Dave Charlaff <davecharlaff@>wrote:
> >
> > >
> > >
> > > There was a post where you explained how to get the function to get the
> > > symbol to reference by referring &Ticker to another cell? Can you point me
> > > at that?****
> > >
> > > Is there a way to array enter this function so that I don’t have to copy
> > > and change the symbol every time?
> > >
> >
>
- 3j.
-
Re: adding a specific element number to smflite
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Mon Jan 16, 2012 9:08 am (PST)
It shouldn't work with such a replacement, unless you are putting AAPL
inside of the quotes. The "&Ticker" is OUTSIDE of the literal string, not
part of it. Just replace "&Ticker" with "&A1", or whatever cell reference
you want.
=RCHGetTableCell("http://finance. yahoo.com/ "&A1,1,"ENTERPRISEq/ks?s=
VALUE/EBITDA")
This is EXCEL processing, not add-in processing.
On Mon, Jan 16, 2012 at 10:01 AM, David <davecharlaff@hotmail.com > wrote:
>
> I understand.
> Tried that first but I still get an error.
> It works when I replace &TICKER with the symbol eg AAPL
>
> could you give it a try and show me an example... perhaps
>
- 3k.
-
Re: adding a specific element number to smflite
Posted by: "Mike McQuaid" mikemcq802@yahoo.com mikemcq802
Mon Jan 16, 2012 9:11 am (PST)
Any string can be created with this technique anywhere - not just SMF
functions
"Some string like a url="&A1
where a1 is cell containing AAPL, for example
which is equivalent to:
"Some string like a url=AAPL"
Send your exact formula and we can tell you what's wrong
_____
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf
Of David
Sent: Monday, January 16, 2012 12:02 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: adding a specific element number to smflite
I understand.
Tried that first but I still get an error.
It works when I replace &TICKER with the symbol eg AAPL
could you give it a try and show me an example... perhaps
--- In smf_addin@yahoogroups.com <mailto:smf_addin%40yahoogro ups.com> ,
"Mike McQuaid" <mikemcq802@...> wrote:
>
> You must leave the & in place - it's a concatenation operator
>
> You can replace ticker with A1
>
> _____
>
> From: smf_addin@yahoogroups.com <mailto:smf_addin%40yahoogro ups.com>
[mailto:smf_addin@yahoogroups.com <mailto:smf_addin%40yahoogro ups.com> ] On
Behalf Of David
> Sent: Monday, January 16, 2012 11:44 AM
> To: smf_addin@yahoogroups.com <mailto:smf_addin%40yahoogro ups.com>
> Subject: [smf_addin] Re: adding a specific element number to smflite
>
>
>
>
> That is what I thought.
> AAPL Error
> ADP Error
> ADS Error
> AET Error
>
> This is what I get when I replace &Ticker with a cell reference like A1?
> --- In smf_addin@yahoogroups.com <mailto:smf_addin%40yahoogro ups.com>
<mailto:smf_addin%40yahoogro ups.com> , Randy Harmelink <rharmelink@ > wrote:
> >
> > =RCHGetTableCell("http://finance. yahoo.com/ "&Ticker,1,"q/ks?s= ENTERPRISE
> > VALUE/EBITDA")
> >
> > "Ticker" is a named cell reference, but it could be a normal cell
reference
> > such as A1 or C3.
> >
> > If using a cell reference, you don't need to edit the formula each time.
> >
> > There's no reason to array-enter it, because it only returns a single
item.
> > In general, I only resort to array-entered formulas where a single
Internet
> > retrieval returns numerous data items, or a CSV file.
> >
> > On Mon, Jan 16, 2012 at 9:26 AM, Dave Charlaff <davecharlaff@>wrote:
> >
> > >
> > >
> > > There was a post where you explained how to get the function to get
the
> > > symbol to reference by referring &Ticker to another cell? Can you
point me
> > > at that?****
> > >
> > > Is there a way to array enter this function so that I don’t
have to copy
> > > and change the symbol every time?
> > >
> >
>
- 3l.
-
Re: adding a specific element number to smflite
Posted by: "David" davecharlaff@hotmail.com copthornelabs
Mon Jan 16, 2012 9:29 am (PST)
Thanks. I had one too many " in the expression and in the wrong place.
Appreciate all your help.
Dave C.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> It shouldn't work with such a replacement, unless you are putting AAPL
> inside of the quotes. The "&Ticker" is OUTSIDE of the literal string, not
> part of it. Just replace "&Ticker" with "&A1", or whatever cell reference
> you want.
>
> =RCHGetTableCell("http://finance. yahoo.com/ "&A1,1,"ENTERPRISEq/ks?s=
> VALUE/EBITDA")
>
> This is EXCEL processing, not add-in processing.
>
> On Mon, Jan 16, 2012 at 10:01 AM, David <davecharlaff@...> wrote:
>
> >
> > I understand.
> > Tried that first but I still get an error.
> > It works when I replace &TICKER with the symbol eg AAPL
> >
> > could you give it a try and show me an example... perhaps
> >
>
- 4a.
-
Re: 52w L & H
Posted by: "V. Shankar" tarakayan@yahoo.com tarakayan
Mon Jan 16, 2012 7:53 am (PST)
1. smfPricesBetween() function works well for 52w L&H. In the same function, is the Prev Close figure coming out correct?
As I was not sure, I ran for 2 consecutive dates, 1/13/12 (close) & 1/12/12 (previous close) for AMZN:
The actual close prices were: $175.93 (1/13) & $178.42 (1/12).
Using the smfPB function I get: $175.93 (correct) & 178.90
2. For extracting figures from barchart.com, which function to be used? RCHGET...?
http://www.barchart.com/detailedquo te/stocks/ MMM
>____________________ _________ ___
> From: Randy Harmelink <rharmelink@gmail.com >
>To: smf_addin@yahoogroups.com
>Sent: Sunday, January 15, 2012 10:26 AM
>Subject: Re: [smf_addin] 52w L & H
>
>
>
>Not that I'm aware of. You could get the dates if you extracted them from the barchart.com link I gave.
>
>Or, take a look at the smfPricesBetween() function.
>
>But both of those methods will be require one Internet access per ticker symbol, which could be slow if you need them for a lot of equities.
>
>
>On Sat, Jan 14, 2012 at 9:46 PM, V. Shankar <tarakayan@yahoo.com > wrote:
>
>
>>>
>>>You can use the RCHGetYahooQuotes() function to get them from Yahoo.
>>>
>>>Is there a code to get dates associated with "j" & "k" in excel?
>>>
>
>
>
> - 4b.
-
Re: 52w L & H
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Mon Jan 16, 2012 8:14 am (PST)
On Mon, Jan 16, 2012 at 8:53 AM, V. Shankar <tarakayan@yahoo.com > wrote:
>
> 1. smfPricesBetween() function works well for 52w L&H. In the same
> function, is the Prev Close figure coming out correct?
>
> As I was not sure, I ran for 2 consecutive dates, 1/13/12 (close) &
> 1/12/12 (previous close) for AMZN:
>
> The actual close prices were: $175.93 (1/13) & $178.42 (1/12).
> Using the smfPB function I get: $175.93 (correct) & 178.90
>
With those two dates, the prices between starts from the opening price on
1/12/12 and ends with the closing price on 1/13/12. So the "previous" close
would be from 1/11/12.
2. For extracting figures from barchart.com, which function to be used?
> RCHGET...?
> http://www.barchart.com/detailedquo te/stocks/ MMM
>
RCHGetTableCell() should do the trick. For example:
=RCHGetTableCell("http://www.barchart .com/detailedquo te/stocks/ MMM
",1,"12-Month")
...would return:
98.19 on 07/07/2011
And you could use the smfStrExtr() function to extract out the amount and
date:
=0+smfStrExtr("|"&C5,"| "," ")
=0+smfStrExtr(C5&"|"," on ","|")
>
> --------------------- ---------
> *From:* Randy Harmelink <rharmelink@gmail.com >
> *To:* smf_addin@yahoogroups.com
> *Sent:* Sunday, January 15, 2012 10:26 AM
> *Subject:* Re: [smf_addin] 52w L & H
>
>
> Not that I'm aware of. You could get the dates if you extracted them from
> the barchart.com link I gave.
>
> Or, take a look at the smfPricesBetween() function.
>
> But both of those methods will be require one Internet access per ticker
> symbol, which could be slow if you need them for a lot of equities.
>
> On Sat, Jan 14, 2012 at 9:46 PM, V. Shankar <tarakayan@yahoo.com > wrote:
>
>
> You can use the RCHGetYahooQuotes() function to get them from Yahoo.
> Is there a code to get dates associated with "j" & "k" in excel?
>
>
>
>
>
>
>
- 5a.
-
Re: Fw: Re: Yahoo Finance "Last Trade"cell
Posted by: "randy tofsrud" rst66@yahoo.com rst66
Mon Jan 16, 2012 9:02 am (PST)
=0+smfGetTagContent("http://finance. yahoo.com/ "&OTEX,"span"q?s= ,-1,"yfs_ l84_otex" )
I have tried removing one and then the other "OTEX" ticker symbols but it still doesn't work.
I would like to be able to use the correct formula in 2 different spreadsheets in which I formerly used the "Last Trade" cell.
Thank You,
--- On Sun, 1/15/12, Randy Harmelink <rharmelink@gmail.com > wrote:
From: Randy Harmelink <rharmelink@gmail.com >
Subject: Re: [smf_addin] Fw: Re: Yahoo Finance "Last Trade"cell
To: smf_addin@yahoogroups.com
Date: Sunday, January 15, 2012, 11:31 PM
Can you cite the exact formula you are using, please?
On Sun, Jan 15, 2012 at 7:29 AM, randy tofsrud <rst66@yahoo.com > wrote:
I am having problems with this.
I have replaced "ticker"with the stock ticker and have inserted the stock ticker after 184_ but it still doesn't work.
Is there something else that I should be doing? Do I need to download something other than the SMF Add-In?
- 5b.
-
Re: Fw: Re: Yahoo Finance "Last Trade"cell
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Mon Jan 16, 2012 9:13 am (PST)
Sigh.
The "&" operator is a concatenation symbol. It is used to concatenate a
string to the contents of a worksheet cell, or multiple strings. If you are
going to use a literal value, you need to make it a string. This is an
EXCEL process, not an add-in one.
As you have it, it is looking for a worksheet cell named OTEX.
On Mon, Jan 16, 2012 at 10:02 AM, randy tofsrud <rst66@yahoo.com > wrote:
>
> =0+smfGetTagContent("http://finance. yahoo.com/ q?s=
> "&OTEX,"span",-1,"yfs_ l84_otex" )
>
> I have tried removing one and then the other "OTEX" ticker symbols but it
> still doesn't work.
> I would like to be able to use the correct formula in 2 different
> spreadsheets in which I formerly used the "Last Trade" cell.
>
- 6a.
-
Re: Version Difference??
Posted by: "kim54blake" kblake@rogers.com kim54blake
Mon Jan 16, 2012 9:34 am (PST)
Thanks Randy and Mike, I am not sure what happened at my end but I removed the Add-in and reinstalled and everything seems okay now???
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> As with Mike, your formula is working fine here...
>
> Maybe you just got a bad web page? Did you try running
> smfForceRecalculation? Did you exit and re-enter EXCEL after updating the
> add-in, to make sure it loaded the new version (it loads add-in when it
> starts).
>
> On Sun, Jan 15, 2012 at 9:58 PM, kim54blake <kblake@...> wrote:
>
> > Hi Randy hope you have been keeping well. I recently updated to the latest
> > version 2011.12.01 from 2010.08.02. Now this formula no longer works:
> > =RCHGetTableCell("
> > http://www.pgatour.com/.element/ ",$A$5,"Y-T-ssi/auto/ 3.0/sdms/ stats/r/body/ 2012/189_ body.html D
> > statistics through: ","<table",">"&$H8&"< /",,,"</table" ) where $A$5=2 and
> > H8 is the name of the player for my golf pool.
> > Can you help me figure out why the new version does not work?
> > I substituted 109 for 189 it made no difference, tried experimenting to no
> > avail
> >
>
- 7a.
-
Re: Don't Know What Happened
Posted by: "codyklein" codyklein@alum.drexel.edu codyklein
Mon Jan 16, 2012 10:23 am (PST)
I don't see an easy way to pull the last trade price with the new format. Anyone come up with a solution?
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Nothing happened to the add-in or the VBA code.
>
> What happened is that Yahoo changed the format of their summary page -- it
> no longer has a table entry for "Last Trade:".
>
> On Sun, Jan 15, 2012 at 1:52 PM, laurancehill <lohill@...> wrote:
>
> > Randy,
> > I have done something to my Excel or to my installation of the SMF_addin
> > that has caused the addin to cease giving me values. Here is a function
> > that has worked for me without fail for at least four years and as of last
> > friday it stopped working. In the function the array aYdata is global.
> > Going back over it has shown me some faults but the fact is that it has
> > worked in the past.
> >
> > Public Function GetAPrice(sTicker As String) As Boolean
> > 'Takes a passed Ticker and retrieves a global array (aYdata) of Yahoo
> > data
> > 'aYdata(0) = Ticker (s)
> > 'aYdata(1) = Last Price (l1) el-one
> > 'aYdata(2) = Dividend/Share (d)
> > 'aYdata(3) = Stock Name (n)
> >
> > Dim sURL, sData As String
> > Dim nPos As Integer
> >
> > sURL = "http://finance.yahoo.com/ " + sTickerq?s=
> > sData = RCHGetTableCell(sURL, 1, "Trade:")
> > If sData = "Error" Then
> > sData = RCHGetTableCell(sURL, 1, "Net Asset Value:")
> > End If
> > aYdata(1) = sData
> > sData = RCHGetTableCell(sURL, 1, "Yield:")
> > If InStr(1, sData, "Error") = 0 And InStr(1, sData, "N/A") = 0 Then
> > nPos = InStr(1, sData, "(")
> > aYdata(2) = Left(sData, nPos - 1)
> > Else
> > aYdata(2) = "0"
> > End If
> > If aYdata(1) = "" Then
> > GetAPrice = False
> > Else
> > GetAPrice = True
> > End If
> > End Function
> >
> > In stepping back through the code the first call to the addin causes sData
> > to be "Error", the second call causes it to be "Error" and the last gets
> > "N/A (N/A)"
> >
> > I have reinstalled the latest version of the addin and have tested it by
> > setting a cell of Excel to "RCHGetElementNumber("AAPL", 25)" and it gave the
> > correct answer. I assume that lets me know the adding is working
> > correctly. Do you have any clue as to what might be going on?
> >
> > I am using Excel which I think is 2007. At least that is what the help
> > says. This is in Windows 7 but under Parallels on a Macintosh (10.7.2).
> > It has been in Parallels for at least two years.
> >
>
- 7b.
-
Re: Don't Know What Happened
Posted by: "Mike McQuaid" mikemcq802@yahoo.com mikemcq802
Mon Jan 16, 2012 10:39 am (PST)
No, it doesn't seem to be in a table anymore so smfGetTableCell won't work.
What about SMFGetTagContent? Or, even RCHGetYahooQuotes?
_____
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf
Of codyklein
Sent: Monday, January 16, 2012 1:24 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: Don't Know What Happened
I don't see an easy way to pull the last trade price with the new format.
Anyone come up with a solution?
--- In smf_addin@yahoogroups.com <mailto:smf_addin%40yahoogro ups.com> ,
Randy Harmelink <rharmelink@...> wrote:
>
> Nothing happened to the add-in or the VBA code.
>
> What happened is that Yahoo changed the format of their summary page -- it
> no longer has a table entry for "Last Trade:".
>
> On Sun, Jan 15, 2012 at 1:52 PM, laurancehill <lohill@...> wrote:
>
> > Randy,
> > I have done something to my Excel or to my installation of the SMF_addin
> > that has caused the addin to cease giving me values. Here is a function
> > that has worked for me without fail for at least four years and as of
last
> > friday it stopped working. In the function the array aYdata is global.
> > Going back over it has shown me some faults but the fact is that it has
> > worked in the past.
> >
> > Public Function GetAPrice(sTicker As String) As Boolean
> > 'Takes a passed Ticker and retrieves a global array (aYdata) of Yahoo
> > data
> > 'aYdata(0) = Ticker (s)
> > 'aYdata(1) = Last Price (l1) el-one
> > 'aYdata(2) = Dividend/Share (d)
> > 'aYdata(3) = Stock Name (n)
> >
> > Dim sURL, sData As String
> > Dim nPos As Integer
> >
> > sURL = "http://finance.yahoo.com/ " + sTickerq?s=
> > sData = RCHGetTableCell(sURL, 1, "Trade:")
> > If sData = "Error" Then
> > sData = RCHGetTableCell(sURL, 1, "Net Asset Value:")
> > End If
> > aYdata(1) = sData
> > sData = RCHGetTableCell(sURL, 1, "Yield:")
> > If InStr(1, sData, "Error") = 0 And InStr(1, sData, "N/A") = 0 Then
> > nPos = InStr(1, sData, "(")
> > aYdata(2) = Left(sData, nPos - 1)
> > Else
> > aYdata(2) = "0"
> > End If
> > If aYdata(1) = "" Then
> > GetAPrice = False
> > Else
> > GetAPrice = True
> > End If
> > End Function
> >
> > In stepping back through the code the first call to the addin causes
sData
> > to be "Error", the second call causes it to be "Error" and the last gets
> > "N/A (N/A)"
> >
> > I have reinstalled the latest version of the addin and have tested it by
> > setting a cell of Excel to "RCHGetElementNumber("AAPL", 25)" and it gave
the
> > correct answer. I assume that lets me know the adding is working
> > correctly. Do you have any clue as to what might be going on?
> >
> > I am using Excel which I think is 2007. At least that is what the help
> > says. This is in Windows 7 but under Parallels on a Macintosh (10.7.2).
> > It has been in Parallels for at least two years.
> >
>
- 7c.
-
Re: Don't Know What Happened
Posted by: "Ron Daring" ritchierun@yahoo.com ritchierun
Mon Jan 16, 2012 10:56 am (PST)
Hi all,
Has anyone discovered a solution to this change to the Yahoo Summary page, yet?
I was using "RCHGetTableCell("http://finance. yahoo.com/ ",1,">Last Trade")" in my lookup tables just fine for months, but it broke when Yahoo deleted the "Last Trade" from their Summary page. Since then, I havn't been able to use anything similar on any financial site to get the current price. At this point, I would be happy to get it from any site. I just need to replace a formula for that 1 column. Everything else still works.q?s=~~~~~
Thanks in advance,
Ron
_____________________ _________ __
From: Randy Harmelink <rharmelink@gmail.com >
To: smf_addin@yahoogroups.com
Sent: Sunday, January 15, 2012 1:02 PM
Subject: Re: [smf_addin] Don't Know What Happened
Nothing happened to the add-in or the VBA code.
What happened is that Yahoo changed the format of their summary page -- it no longer has a table entry for "Last Trade:".
On Sun, Jan 15, 2012 at 1:52 PM, laurancehill <lohill@cox.net> wrote:
Randy,
>I have done something to my Excel or to my installation of the SMF_addin that has caused the addin to cease giving me values. Here is a function that has worked for me without fail for at least four years and as of last friday it stopped working. In the function the array aYdata is global. Going back over it has shown me some faults but the fact is that it has worked in the past.
>
>Public Function GetAPrice(sTicker As String) As Boolean
> 'Takes a passed Ticker and retrieves a global array (aYdata) of Yahoo data
> 'aYdata(0) = Ticker (s)
> 'aYdata(1) = Last Price (l1) el-one
> 'aYdata(2) = Dividend/Share (d)
> 'aYdata(3) = Stock Name (n)
>
> Dim sURL, sData As String
> Dim nPos As Integer
>
> sURL = "http://finance.yahoo.com/ " + sTickerq?s=
> sData = RCHGetTableCell(sURL, 1, "Trade:")
> If sData = "Error" Then
> sData = RCHGetTableCell(sURL, 1, "Net Asset Value:")
> End If
> aYdata(1) = sData
> sData = RCHGetTableCell(sURL, 1, "Yield:")
> If InStr(1, sData, "Error") = 0 And InStr(1, sData, "N/A") = 0 Then
> nPos = InStr(1, sData, "(")
> aYdata(2) = Left(sData, nPos - 1)
> Else
> aYdata(2) = "0"
> End If
> If aYdata(1) = "" Then
> GetAPrice = False
> Else
> GetAPrice = True
> End If
>End Function
>
>In stepping back through the code the first call to the addin causes sData to be "Error", the second call causes it to be "Error" and the last gets "N/A (N/A)"
>
>I have reinstalled the latest version of the addin and have tested it by setting a cell of Excel to "RCHGetElementNumber("AAPL", 25)" and it gave the correct answer. I assume that lets me know the adding is working correctly. Do you have any clue as to what might be going on?
>
>I am using Excel which I think is 2007. At least that is what the help says. This is in Windows 7 but under Parallels on a Macintosh (10.7.2). It has been in Parallels for at least two years.
>
- 7d.
-
Re: [Bulk] Re: [smf_addin] Don't Know What Happened
Posted by: "Mike McQuaid" mikemcq802@yahoo.com mikemcq802
Mon Jan 16, 2012 11:26 am (PST)
Um, RCHGetYahooQuotes? See Randy's doc.
Or, smfGetTagContent. Also see Randy's docs.
Example for AAPL:
=smfGetTagContent("http://finance. yahoo.com/ ","span",0,"q?s=AAPL yfs_l84
<http://finance.yahoo.com/ > ")q?s=AAPL
There are other "last prices" available from various providers accessible
through RCHGetElementNumber. MSN is 25, if I remember right. See the
ElementDefinitions in your SMF install directory.
_____
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf
Of Ron Daring
Sent: Monday, January 16, 2012 1:56 PM
To: smf_addin@yahoogroups.com
Subject: [Bulk] Re: [smf_addin] Don't Know What Happened
Hi all,
Has anyone discovered a solution to this change to the Yahoo Summary page,
yet?
I was using "RCHGetTableCell("http://finance. yahoo.com/ ",1,"q?s=~~~~~
<http://finance.yahoo.com/ > >Last Trade")" in my lookup tables justq?s=~~~~~
fine for months, but it broke when Yahoo deleted the "Last Trade" from their
Summary page. Since then, I havn't been able to use anything similar on any
financial site to get the current price. At this point, I would be happy to
get it from any site. I just need to replace a formula for that 1 column.
Everything else still works.
Thanks in advance,
Ron
From: Randy Harmelink <rharmelink@gmail.com >
To: smf_addin@yahoogroups.com
Sent: Sunday, January 15, 2012 1:02 PM
Subject: Re: [smf_addin] Don't Know What Happened
Nothing happened to the add-in or the VBA code.
What happened is that Yahoo changed the format of their summary page -- it
no longer has a table entry for "Last Trade:".
On Sun, Jan 15, 2012 at 1:52 PM, laurancehill <lohill@cox.net> wrote:
Randy,
I have done something to my Excel or to my installation of the SMF_addin
that has caused the addin to cease giving me values. Here is a function
that has worked for me without fail for at least four years and as of last
friday it stopped working. In the function the array aYdata is global.
Going back over it has shown me some faults but the fact is that it has
worked in the past.
Public Function GetAPrice(sTicker As String) As Boolean
'Takes a passed Ticker and retrieves a global array (aYdata) of Yahoo
data
'aYdata(0) = Ticker (s)
'aYdata(1) = Last Price (l1) el-one
'aYdata(2) = Dividend/Share (d)
'aYdata(3) = Stock Name (n)
Dim sURL, sData As String
Dim nPos As Integer
sURL = "http://finance.yahoo.com/ " + sTickerq?s=
sData = RCHGetTableCell(sURL, 1, "Trade:")
If sData = "Error" Then
sData = RCHGetTableCell(sURL, 1, "Net Asset Value:")
End If
aYdata(1) = sData
sData = RCHGetTableCell(sURL, 1, "Yield:")
If InStr(1, sData, "Error") = 0 And InStr(1, sData, "N/A") = 0 Then
nPos = InStr(1, sData, "(")
aYdata(2) = Left(sData, nPos - 1)
Else
aYdata(2) = "0"
End If
If aYdata(1) = "" Then
GetAPrice = False
Else
GetAPrice = True
End If
End Function
In stepping back through the code the first call to the addin causes sData
to be "Error", the second call causes it to be "Error" and the last gets
"N/A (N/A)"
I have reinstalled the latest version of the addin and have tested it by
setting a cell of Excel to "RCHGetElementNumber("AAPL", 25)" and it gave the
correct answer. I assume that lets me know the adding is working correctly.
Do you have any clue as to what might be going on?
I am using Excel which I think is 2007. At least that is what the help
says. This is in Windows 7 but under Parallels on a Macintosh (10.7.2). It
has been in Parallels for at least two years.
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