9 New Messages
Digest #2275
Messages
Thu Jul 12, 2012 9:04 pm (PDT) . Posted by:
"investor952" investor952
7.1a - smfStrExtr.html
Hello Randy..doesn't Excel already have functions that do this..like
the Mid()function?..to extract characters from strings?
Hello Randy..doesn'
the Mid()function?
Thu Jul 12, 2012 9:20 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Yes. But typically, you'd need to do two FIND() functions before doing the
MID(). And this process is something that gets done very often when
extracting data. For example, to extract "table" out of "1table2":
D1: =FIND("1","1table2")
D2: =FIND("2","1table2")
D3: =MID("1table2",D3+1,D4-D3-1)
Or, if I wanted to combine that all into a single formula, it's a messy:
D4:
=MID("1table2",FIND("1","1table2")+1,FIND("2","1table2")-FIND("1","1table2")-1)
Now, all I need to do is a very simple:
D5: =smfStrExtr("1table2","1","2")
So, the new function easily converts a three-step process into a single one.
On Thu, Jul 12, 2012 at 9:04 PM, investor952 <investor952@yahoo.com > wrote:
> 7.1a - smfStrExtr.html
>
> Hello Randy..doesn't Excel already have functions that do this..like
> the Mid()function?..to extract characters from strings?
>
MID(). And this process is something that gets done very often when
extracting data. For example, to extract "table" out of "1table2":
D1: =FIND("1","1table2"
D2: =FIND("2","1table2"
D3: =MID("1table2"
Or, if I wanted to combine that all into a single formula, it's a messy:
D4:
=MID("1table2"
Now, all I need to do is a very simple:
D5: =smfStrExtr(
So, the new function easily converts a three-step process into a single one.
On Thu, Jul 12, 2012 at 9:04 PM, investor952 <investor952@
> 7.1a - smfStrExtr.html
>
> Hello Randy..doesn'
> the Mid()function?
>
Thu Jul 12, 2012 9:35 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Oops, it was actually more complicated:
D3: =FIND("1","21table21")
D4: =FIND("2","21table21"*,D3+1*)
D5: =MID("21table21",D3+1,D4-D3-1)
...and the messy:
D6:
=MID("21table21",FIND("1","21table21")+1,FIND("2","21table21",FIND("1","21table21")+1)-FIND("1","21table21")-1)
...vs the simple:
D7: =smfStrExtr("21table21","1","2")
On Thu, Jul 12, 2012 at 9:04 PM, investor952 <investor952@yahoo.com > wrote:
> 7.1a - smfStrExtr.html
>
> Hello Randy..doesn't Excel already have functions that do this..like
> the Mid()function?..to extract characters from strings?
>
D3: =FIND("1","21table2
D4: =FIND("2","21table2
D5: =MID("21table21"
...and the messy:
D6:
=MID("21table21"
...vs the simple:
D7: =smfStrExtr(
On Thu, Jul 12, 2012 at 9:04 PM, investor952 <investor952@
> 7.1a - smfStrExtr.html
>
> Hello Randy..doesn'
> the Mid()function?
>
Fri Jul 13, 2012 12:34 am (PDT) . Posted by:
"christianilson79" christianilson79
=RCHGetElementNumber("Version") returns:
Stock Market Functions add-in, Version 2.1.2012.02.02 (D:\Documents\SMF add in; 49)
while =smfGetAParms(3380)gives me an empty cell.
Cheers
Chris
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> That one works fine for me. What do you get for:
>
> =RCHGetElementNumber("Version")
> =smfGetAParms(3380)
>
> On Thu, Jul 12, 2012 at 6:57 AM, christianilson79 <
> christian.weitert@...> wrote:
>
> >
> > that was a bad example, I just played around with codes. How about
> > =RCHGetElementNumber("NVS";3380), the operating Income from google finance.
> > That does not work for me either.
> >
>
Stock Market Functions add-in, Version 2.1.2012.02.
while =smfGetAParms(
Cheers
Chris
--- In smf_addin@yahoogrou
>
> That one works fine for me. What do you get for:
>
> =RCHGetElementNumbe
> =smfGetAParms(
>
> On Thu, Jul 12, 2012 at 6:57 AM, christianilson79 <
> christian.weitert@
>
> >
> > that was a bad example, I just played around with codes. How about
> > =RCHGetElementNumbe
> > That does not work for me either.
> >
>
Fri Jul 13, 2012 1:08 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Oops. You need to retrieve an element number before running the
smfGetAParms() function. It's blank until the element definitions are
loaded.
Also, just out of curiosity, do you get redirected if you attempt to go to
this web page:
http://www.google.com/finance?fstype=ii&q=NVS
On Fri, Jul 13, 2012 at 12:34 AM, christianilson79 <
christian.weitert@googlemail.com > wrote:
> =RCHGetElementNumber("Version") returns:
> Stock Market Functions add-in, Version 2.1.2012.02.02 (D:\Documents\SMF
> add in; 49)
>
> while =smfGetAParms(3380)gives me an empty cell.
>
smfGetAParms(
loaded.
Also, just out of curiosity, do you get redirected if you attempt to go to
this web page:
http://www.google.
On Fri, Jul 13, 2012 at 12:34 AM, christianilson79 <
christian.weitert@
> =RCHGetElementNumbe
> Stock Market Functions add-in, Version 2.1.2012.02.
> add in; 49)
>
> while =smfGetAParms(
>
Fri Jul 13, 2012 2:19 am (PDT) . Posted by:
"christianilson79" christianilson79
I get the google finance page with: your request could not be processed.
=smfGetAParms(3380) now gives me:
Google;Annual Income Statement -- Operating Income -- FY1;http://www.google.com/finance?fstype=ii&q=~~~~~;1;INCANNUALDIV;Operating Income; ; ;0;</TABLE;0;0
Thanks again for your help!
Chris
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Oops. You need to retrieve an element number before running the
> smfGetAParms() function. It's blank until the element definitions are
> loaded.
>
> Also, just out of curiosity, do you get redirected if you attempt to go to
> this web page:
>
> http://www.google.com/finance?fstype=ii&q=NVS
>
> On Fri, Jul 13, 2012 at 12:34 AM, christianilson79 <
> christian.weitert@...> wrote:
>
> > =RCHGetElementNumber("Version") returns:
> > Stock Market Functions add-in, Version 2.1.2012.02.02 (D:\Documents\SMF
> > add in; 49)
> >
> > while =smfGetAParms(3380)gives me an empty cell.
> >
>
=smfGetAParms(
Google;Annual Income Statement -- Operating Income -- FY1;http://www.google.
Thanks again for your help!
Chris
--- In smf_addin@yahoogrou
>
> Oops. You need to retrieve an element number before running the
> smfGetAParms(
> loaded.
>
> Also, just out of curiosity, do you get redirected if you attempt to go to
> this web page:
>
> http://www.google.
>
> On Fri, Jul 13, 2012 at 12:34 AM, christianilson79 <
> christian.weitert@
>
> > =RCHGetElementNumbe
> > Stock Market Functions add-in, Version 2.1.2012.02.
> > add in; 49)
> >
> > while =smfGetAParms(
> >
>
Fri Jul 13, 2012 3:52 am (PDT) . Posted by:
"antonvanas1989" antonvanas1989
Randy need your help again.
for currency from google i use another line now.
like this:
=smfStrExtr(smfGetTagContent("http://www.google.com/finance?q= "&"KO";"div";-1;"Currency in");"in ";"D")
But you see i get now "US" insteed of "USD" i know because i give for end parameter "D" but i don't know how i could name the end for this source code:
<div>Currency in USD</div>
Thanxs
--- In smf_addin@yahoogroups.com , "antonvanas1989" <antonvanas1989@...> wrote:
>
> The "c4" is what i meant but i wonder where on the site the value come from, because yahoo is publish it on tabs like "Summary", "Profile", "Income Statement"
>
> I got my currency's now thanxs.
> Maybe you can make a number for RCHGetElementNumber for all the source currencys like MSN Currency/ Google Currency. It's a suggestion because i think more people like to have a currency symbol on there page. (I do because it's not always clear if the data is in euros or dollars for Europese stocks)
>
> --- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@> wrote:
> >
> > "c4" is the Yahoo code you were asking about -- for RCHGetYahooQuotes().
> > Unless you meant something else by Yahoo code?
> >
> > You told your extraction to stop at the first blank after "in " was found,
> > so you just get "US". In this case you need to create a dummy string at the
> > end, to aid the extraction, as in:
> >
> > =smfStrExtr(smfGetTagContent("
> > http://investing.money.msn.com/investments/financial-statements?symbol=KO ","span",-1,"Financial
> > Data in")&"|","in ","|")
> >
> > Note that I appended the "|" to the end of the tag content, so I had
> > something to delimit the end of the string to extract from the tag content.
> >
> > On Mon, Jul 9, 2012 at 3:59 PM, antonvanas1989 <antonvanas1989@>wrote:
> >
> > >
> > > Where does the "c4" come from on the Yahoo site?
> > >
> > > can you help me out with the msn line you gave, i'm trying to only show
> > > the "US Dollar" im getting "US" with this line:
> > > =smfStrExtr(smfGetTagContent("
> > > http://investing.money.msn.com/investments/financial-statements?symbol=KO ";"span",-1,"Financial
> > > Data in"),"in "," ")
> > >
> >
>
for currency from google i use another line now.
like this:
=smfStrExtr(
But you see i get now "US" insteed of "USD" i know because i give for end parameter "D" but i don't know how i could name the end for this source code:
<div>Currency in USD</div>
Thanxs
--- In smf_addin@yahoogrou
>
> The "c4" is what i meant but i wonder where on the site the value come from, because yahoo is publish it on tabs like "Summary", "Profile", "Income Statement"
>
> I got my currency's now thanxs.
> Maybe you can make a number for RCHGetElementNumber for all the source currencys like MSN Currency/ Google Currency. It's a suggestion because i think more people like to have a currency symbol on there page. (I do because it's not always clear if the data is in euros or dollars for Europese stocks)
>
> --- In smf_addin@yahoogrou
> >
> > "c4" is the Yahoo code you were asking about -- for RCHGetYahooQuotes(
> > Unless you meant something else by Yahoo code?
> >
> > You told your extraction to stop at the first blank after "in " was found,
> > so you just get "US". In this case you need to create a dummy string at the
> > end, to aid the extraction, as in:
> >
> > =smfStrExtr(
> > http://investing.
> > Data in")&"|","in ","|")
> >
> > Note that I appended the "|" to the end of the tag content, so I had
> > something to delimit the end of the string to extract from the tag content.
> >
> > On Mon, Jul 9, 2012 at 3:59 PM, antonvanas1989 <antonvanas1989@
> >
> > >
> > > Where does the "c4" come from on the Yahoo site?
> > >
> > > can you help me out with the msn line you gave, i'm trying to only show
> > > the "US Dollar" im getting "US" with this line:
> > > =smfStrExtr(
> > > http://investing.
> > > Data in"),"in "," ")
> > >
> >
>
Fri Jul 13, 2012 4:09 am (PDT) . Posted by:
"antonvanas1989" antonvanas1989
Randy need your help again.
for currency from google i use another line now.
like this:
=smfStrExtr(smfGetTagContent("http://www.google.com/finance?q= "&"KO";"div";-1;"Currency in");"in ";"D")
But you see i get now "US" insteed of "USD" i know because i give for end parameter "D" but i don't know how i could name the end for this source code:
<div>Currency in USD</div>
Thanxs
--- In smf_addin@yahoogroups.com , "antonvanas1989" <antonvanas1989@...> wrote:
>
> The "c4" is what i meant but i wonder where on the site the value come from, because yahoo is publish it on tabs like "Summary", "Profile", "Income Statement"
>
> I got my currency's now thanxs.
> Maybe you can make a number for RCHGetElementNumber for all the source currencys like MSN Currency/ Google Currency. It's a suggestion because i think more people like to have a currency symbol on there page. (I do because it's not always clear if the data is in euros or dollars for Europese stocks)
>
> --- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@> wrote:
> >
> > "c4" is the Yahoo code you were asking about -- for RCHGetYahooQuotes().
> > Unless you meant something else by Yahoo code?
> >
> > You told your extraction to stop at the first blank after "in " was found,
> > so you just get "US". In this case you need to create a dummy string at the
> > end, to aid the extraction, as in:
> >
> > =smfStrExtr(smfGetTagContent("
> > http://investing.money.msn.com/investments/financial-statements?symbol=KO ","span",-1,"Financial
> > Data in")&"|","in ","|")
> >
> > Note that I appended the "|" to the end of the tag content, so I had
> > something to delimit the end of the string to extract from the tag content.
> >
> > On Mon, Jul 9, 2012 at 3:59 PM, antonvanas1989 <antonvanas1989@>wrote:
> >
> > >
> > > Where does the "c4" come from on the Yahoo site?
> > >
> > > can you help me out with the msn line you gave, i'm trying to only show
> > > the "US Dollar" im getting "US" with this line:
> > > =smfStrExtr(smfGetTagContent("
> > > http://investing.money.msn.com/investments/financial-statements?symbol=KO ";"span",-1,"Financial
> > > Data in"),"in "," ")
> > >
> >
>
for currency from google i use another line now.
like this:
=smfStrExtr(
But you see i get now "US" insteed of "USD" i know because i give for end parameter "D" but i don't know how i could name the end for this source code:
<div>Currency in USD</div>
Thanxs
--- In smf_addin@yahoogrou
>
> The "c4" is what i meant but i wonder where on the site the value come from, because yahoo is publish it on tabs like "Summary", "Profile", "Income Statement"
>
> I got my currency's now thanxs.
> Maybe you can make a number for RCHGetElementNumber for all the source currencys like MSN Currency/ Google Currency. It's a suggestion because i think more people like to have a currency symbol on there page. (I do because it's not always clear if the data is in euros or dollars for Europese stocks)
>
> --- In smf_addin@yahoogrou
> >
> > "c4" is the Yahoo code you were asking about -- for RCHGetYahooQuotes(
> > Unless you meant something else by Yahoo code?
> >
> > You told your extraction to stop at the first blank after "in " was found,
> > so you just get "US". In this case you need to create a dummy string at the
> > end, to aid the extraction, as in:
> >
> > =smfStrExtr(
> > http://investing.
> > Data in")&"|","in ","|")
> >
> > Note that I appended the "|" to the end of the tag content, so I had
> > something to delimit the end of the string to extract from the tag content.
> >
> > On Mon, Jul 9, 2012 at 3:59 PM, antonvanas1989 <antonvanas1989@
> >
> > >
> > > Where does the "c4" come from on the Yahoo site?
> > >
> > > can you help me out with the msn line you gave, i'm trying to only show
> > > the "US Dollar" im getting "US" with this line:
> > > =smfStrExtr(
> > > http://investing.
> > > Data in"),"in "," ")
> > >
> >
>
Fri Jul 13, 2012 4:27 am (PDT) . Posted by:
"bushpilote@yahoo.ca" bushpilote
You are welcomed.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Hmm. That does work. I must be thinking of a length limitation on something
> else -- it was EXCEL that wouldn't allow the URL to be over 99 bytes long,
> when the VBA statement ran. I thought it was for the background image of a
> comment (which is what RCHCreateComment is doing). Apparently not.
>
> Thanks for jumping in.
>
> On Thu, Jul 12, 2012 at 4:55 AM, <bushpilote@...> wrote:
>
> > Hi Randy and Josh,
> > One solution is to place the entire link address ("http://....1") into a
> > cell (eg "A1") and then build your rchCreateComment function
> > [rchCreateComment(A1,99,250,250,1)].
> >
> > I work with Excel 2007.
> >
>
--- In smf_addin@yahoogrou
>
> Hmm. That does work. I must be thinking of a length limitation on something
> else -- it was EXCEL that wouldn't allow the URL to be over 99 bytes long,
> when the VBA statement ran. I thought it was for the background image of a
> comment (which is what RCHCreateComment is doing). Apparently not.
>
> Thanks for jumping in.
>
> On Thu, Jul 12, 2012 at 4:55 AM, <bushpilote@
>
> > Hi Randy and Josh,
> > One solution is to place the entire link address ("http://....1") into a
> > cell (eg "A1") and then build your rchCreateComment function
> > [rchCreateComment(
> >
> > I work with Excel 2007.
> >
>
Tidak ada komentar:
Posting Komentar