13 New Messages
Digest #2335
| 4a Can I use rchgetelement number in an array? by "philwhittington@ymail.com" philwhittington@ymail.com 4c Re: Can I use rchgetelement number in an array? by "philwhittington@ymail.com" philwhittington@ymail.com 5a Limit the number of characters pulled from RCHGetTableCell? by "barclaywood@ymail.com" barclaywood@ymail.com 5c Re: Limit the number of characters pulled from RCHGetTableCell? by "barclaywood@ymail.com" barclaywood@ymail.com |
Messages
Sat Sep 1, 2012 6:39 am (PDT) . Posted by:
"Kermit W. Prather" kermitpra
Randy, I just downloaded and tried the Morningstar stock grades. As I do every template you upload.
However, I did not realize one needed a premium account.
It would be nice if you placed the following in the Template spreadsheets whenever a sign on is necessary to use the spreadsheet. A similar comment in the upload description would be nice as well.
This spreadsheet requires a Morningstar premium account and you must login first.
Thanks,
Kermit
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf Of Randy Harmelink
Sent: Friday, August 31, 2012 11:52 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: How to get the Morningstar Rating?
You need to log in to MorningStar with either the EXCEL Web Query dialog (preferred) or with IE, so that EXCEL has the proper security cookies available for the add-in to access the premium data. Logging in with another browser like FireFox won't do it.
On Fri, Aug 31, 2012 at 8:49 PM, Lei Liao <liaolei78@gmail.com > wrote:
Just one more question, I am the premium subscriber on Mstar, and how to get the Premium content to be shown in excel other than just showing the "Premium" when I extract them.
However, I did not realize one needed a premium account.
It would be nice if you placed the following in the Template spreadsheets whenever a sign on is necessary to use the spreadsheet. A similar comment in the upload description would be nice as well.
This spreadsheet requires a Morningstar premium account and you must login first.
Thanks,
Kermit
From: smf_addin@yahoogrou
Sent: Friday, August 31, 2012 11:52 PM
To: smf_addin@yahoogrou
Subject: Re: [smf_addin] Re: How to get the Morningstar Rating?
You need to log in to MorningStar with either the EXCEL Web Query dialog (preferred) or with IE, so that EXCEL has the proper security cookies available for the add-in to access the premium data. Logging in with another browser like FireFox won't do it.
On Fri, Aug 31, 2012 at 8:49 PM, Lei Liao <liaolei78@gmail.
Just one more question, I am the premium subscriber on Mstar, and how to get the Premium content to be shown in excel other than just showing the "Premium" when I extract them.
Sat Sep 1, 2012 7:40 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Good point. I just modified the description.
On Sat, Sep 1, 2012 at 6:39 AM, Kermit W. Prather
<kermitp@tampabay.rr.com >wrote:
>
> Randy, I just downloaded and tried the Morningstar stock grades. As I do
> every template you upload. ****
>
> ** **
>
> However, I did not realize one needed a premium account.****
>
> ** **
>
> It would be nice if you placed the following in the Template spreadsheets
> whenever a sign on is necessary to use the spreadsheet. A similar
> comment in the upload description would be nice as well.****
>
> ** **
>
> This spreadsheet requires a Morningstar premium account and you must login
> first.
>
On Sat, Sep 1, 2012 at 6:39 AM, Kermit W. Prather
<kermitp@tampabay.
>
> Randy, I just downloaded and tried the Morningstar stock grades. As I do
> every template you upload. ****
>
> ** **
>
> However, I did not realize one needed a premium account.****
>
> ** **
>
> It would be nice if you placed the following in the Template spreadsheets
> whenever a sign on is necessary to use the spreadsheet. A similar
> comment in the upload description would be nice as well.****
>
> ** **
>
> This spreadsheet requires a Morningstar premium account and you must login
> first.
>
Sat Sep 1, 2012 2:35 pm (PDT) . Posted by:
"Minh" minh0279
Hi pharnsbe and group,
Thank you very much for the link http://www.standardandpoors.com/indices/sp-500/en/us/?indexId=spusa-500-usduf--p-us-l-- . I clicked "Dividend Rate Change" – "Payouts and Yields" worksheet and I see the dividend payout from 03/31/1977 quarterly. Anyone know how I can get the dividend payout more further such as from 1957?
Another question is when I clicked http://www.multpl.com/s-p-500-dividend/table?f=m I am confused about the numbers. Why the monthly dividend here is much higher than the quarterly dividend on the first link?
Thanks
Minh
--- In smf_addin@yahoogroups.com , "pharnsbe" <harnsberger@...> wrote:
>
> Check out the S&P website:
> http://www.standardandpoors.com/indices/sp-500/en/us/?indexId=spusa-500-usduf--p-us-l--
>
> If you click on the "Download Index Data" link there's a list of Excel spreadsheets containing historical dividend info.
>
>
> --- In smf_addin@yahoogroups.com , "Minh" <minh0279@> wrote:
> >
> > hi all,
> >
> > I am looking for the S&P 500 historical daily dividend. Please let me know if you know how to get it.
> >
> > Thanks
> >
> > Minh
> >
>
Thank you very much for the link http://www.standard
Another question is when I clicked http://www.multpl.
Thanks
Minh
--- In smf_addin@yahoogrou
>
> Check out the S&P website:
> http://www.standard
>
> If you click on the "Download Index Data" link there's a list of Excel spreadsheets containing historical dividend info.
>
>
> --- In smf_addin@yahoogrou
> >
> > hi all,
> >
> > I am looking for the S&P 500 historical daily dividend. Please let me know if you know how to get it.
> >
> > Thanks
> >
> > Minh
> >
>
Sat Sep 1, 2012 3:39 pm (PDT) . Posted by:
"barclaywood@ymail.com" barclaywood@ymail.com
Thanks. I actually found a simpler solution. Excel gave me a little error message after the date was downloaded. Apparently it didn't like the two digit year given, and wanted to know whether I considered the value 1912 or 2012 (Y2k residual fears, I suppose). Once I told it to consider downloaded dates as 2012 (i.e., I answered its question), it let me format the dates.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> If you can't format the dates, it's because what's in the cell is a string.
> You'd need to convert that string into an EXCEL serial date value before it
> can be formatted as a date. Check out EXCEL's DATEVALUE() function.
>
> On Wed, Aug 29, 2012 at 10:29 PM, barclaywood@... <
> barclaywood@...> wrote:
>
> > Hi all, I'm using RCHGetTableCell to download ex-div dates. Everything is
> > fine, except I can't find a site that will download in my preferred format
> > (dd-mmm). I am getting data in dd-mmm-yy format (e.g., 29-Aug-12). It's a
> > nit picky thing, but I would REALLY like to be able to format the cells to
> > excise that year information.
> >
> > After I download the data, I am copying and pasting values only (that way
> > the workbook opens faster). But every attempt I make to format the cell or
> > cells is futile - the data stays in exactly the form it was originally.
> >
> > Any help? Thanks!
> >
>
--- In smf_addin@yahoogrou
>
> If you can't format the dates, it's because what's in the cell is a string.
> You'd need to convert that string into an EXCEL serial date value before it
> can be formatted as a date. Check out EXCEL's DATEVALUE() function.
>
> On Wed, Aug 29, 2012 at 10:29 PM, barclaywood@
> barclaywood@
>
> > Hi all, I'm using RCHGetTableCell to download ex-div dates. Everything is
> > fine, except I can't find a site that will download in my preferred format
> > (dd-mmm). I am getting data in dd-mmm-yy format (e.g., 29-Aug-12). It's a
> > nit picky thing, but I would REALLY like to be able to format the cells to
> > excise that year information.
> >
> > After I download the data, I am copying and pasting values only (that way
> > the workbook opens faster). But every attempt I make to format the cell or
> > cells is futile - the data stays in exactly the form it was originally.
> >
> > Any help? Thanks!
> >
>
Sat Sep 1, 2012 5:36 pm (PDT) . Posted by:
"philwhittington@ymail.com" philwhittington@ymail.com
Hi
I'm new to smf, but it's incredible - thanks! After realising how awesome it was, the next thing I realised was how slow my spreadsheets were. So I switched to array formulas.
I'm relatively new to array formulas, but I can get them working for =rchgetyahooquotes, to give me (for example), market cap, and last quote, for ~100 tickers very quickly.
But when I use rchgetelement number for return on assets and EV/EBITDA, I just get #Value! responses in the array cells.
Is it something about this function that can't be used in an array formula?
I'm new to smf, but it's incredible - thanks! After realising how awesome it was, the next thing I realised was how slow my spreadsheets were. So I switched to array formulas.
I'm relatively new to array formulas, but I can get them working for =rchgetyahooquotes, to give me (for example), market cap, and last quote, for ~100 tickers very quickly.
But when I use rchgetelement number for return on assets and EV/EBITDA, I just get #Value! responses in the array cells.
Is it something about this function that can't be used in an array formula?
Sat Sep 1, 2012 5:46 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The only reason RCHGetYahooQuotes() speeds things up is because Yahoo
creates a CSV file with all of the requested data on it, in a single
Internet access. It's faster to get one CSV file with 2000 data elements on
it, than to get 2000 individual CSV files, each with one data element on
it. Because it saves 1999 Internet accesses.
However, there's no reason to make RCHGetElementNumber() an array-entered
formula, as it would still need to do just as much work. The
RCHGetElementNumber() function still needs to go to each individual web
page you're extracting data from. It's the number of Internet accesses that
slows down the processing, not the number of individual functions. The
add-in does do internal processing, so that it saves the web page before
doing any extractions from it. That way, if you extract 10 data items from
the same web page, it only needs to go to the Internet once to get the web
page. But if you get one data item for each of ten different web page, that
requires 10 Internet accesses.
On Sat, Sep 1, 2012 at 5:25 PM, philwhittington@ymail.com <
philwhittington@gmail.com > wrote:
>
> I'm new to smf, but it's incredible - thanks! After realising how awesome
> it was, the next thing I realised was how slow my spreadsheets were. So I
> switched to array formulas.
>
> I'm relatively new to array formulas, but I can get them working for
> =rchgetyahooquotes, to give me (for example), market cap, and last quote,
> for ~100 tickers very quickly.
>
> But when I use rchgetelement number for return on assets and EV/EBITDA, I
> just get #Value! responses in the array cells.
>
> Is it something about this function that can't be used in an array formula?
>
creates a CSV file with all of the requested data on it, in a single
Internet access. It's faster to get one CSV file with 2000 data elements on
it, than to get 2000 individual CSV files, each with one data element on
it. Because it saves 1999 Internet accesses.
However, there's no reason to make RCHGetElementNumber
formula, as it would still need to do just as much work. The
RCHGetElementNumber
page you're extracting data from. It's the number of Internet accesses that
slows down the processing, not the number of individual functions. The
add-in does do internal processing, so that it saves the web page before
doing any extractions from it. That way, if you extract 10 data items from
the same web page, it only needs to go to the Internet once to get the web
page. But if you get one data item for each of ten different web page, that
requires 10 Internet accesses.
On Sat, Sep 1, 2012 at 5:25 PM, philwhittington@
philwhittington@
>
> I'm new to smf, but it's incredible - thanks! After realising how awesome
> it was, the next thing I realised was how slow my spreadsheets were. So I
> switched to array formulas.
>
> I'm relatively new to array formulas, but I can get them working for
> =rchgetyahooquotes, to give me (for example), market cap, and last quote,
> for ~100 tickers very quickly.
>
> But when I use rchgetelement number for return on assets and EV/EBITDA, I
> just get #Value! responses in the array cells.
>
> Is it something about this function that can't be used in an array formula?
>
Sat Sep 1, 2012 5:56 pm (PDT) . Posted by:
"philwhittington@ymail.com" philwhittington@ymail.com
Ok that's fair enough - thanks for the explanation. [Sorry the title of my post had nothing to do with my question - I'm new to the forum as well as smf :)]
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> The only reason RCHGetYahooQuotes() speeds things up is because Yahoo
> creates a CSV file with all of the requested data on it, in a single
> Internet access. It's faster to get one CSV file with 2000 data elements on
> it, than to get 2000 individual CSV files, each with one data element on
> it. Because it saves 1999 Internet accesses.
>
> However, there's no reason to make RCHGetElementNumber() an array-entered
> formula, as it would still need to do just as much work. The
> RCHGetElementNumber() function still needs to go to each individual web
> page you're extracting data from. It's the number of Internet accesses that
> slows down the processing, not the number of individual functions. The
> add-in does do internal processing, so that it saves the web page before
> doing any extractions from it. That way, if you extract 10 data items from
> the same web page, it only needs to go to the Internet once to get the web
> page. But if you get one data item for each of ten different web page, that
> requires 10 Internet accesses.
>
> On Sat, Sep 1, 2012 at 5:25 PM, philwhittington@... <
> philwhittington@...> wrote:
>
> >
> > I'm new to smf, but it's incredible - thanks! After realising how awesome
> > it was, the next thing I realised was how slow my spreadsheets were. So I
> > switched to array formulas.
> >
> > I'm relatively new to array formulas, but I can get them working for
> > =rchgetyahooquotes, to give me (for example), market cap, and last quote,
> > for ~100 tickers very quickly.
> >
> > But when I use rchgetelement number for return on assets and EV/EBITDA, I
> > just get #Value! responses in the array cells.
> >
> > Is it something about this function that can't be used in an array formula?
> >
>
--- In smf_addin@yahoogrou
>
> The only reason RCHGetYahooQuotes(
> creates a CSV file with all of the requested data on it, in a single
> Internet access. It's faster to get one CSV file with 2000 data elements on
> it, than to get 2000 individual CSV files, each with one data element on
> it. Because it saves 1999 Internet accesses.
>
> However, there's no reason to make RCHGetElementNumber
> formula, as it would still need to do just as much work. The
> RCHGetElementNumber
> page you're extracting data from. It's the number of Internet accesses that
> slows down the processing, not the number of individual functions. The
> add-in does do internal processing, so that it saves the web page before
> doing any extractions from it. That way, if you extract 10 data items from
> the same web page, it only needs to go to the Internet once to get the web
> page. But if you get one data item for each of ten different web page, that
> requires 10 Internet accesses.
>
> On Sat, Sep 1, 2012 at 5:25 PM, philwhittington@
> philwhittington@
>
> >
> > I'm new to smf, but it's incredible - thanks! After realising how awesome
> > it was, the next thing I realised was how slow my spreadsheets were. So I
> > switched to array formulas.
> >
> > I'm relatively new to array formulas, but I can get them working for
> > =rchgetyahooquotes, to give me (for example), market cap, and last quote,
> > for ~100 tickers very quickly.
> >
> > But when I use rchgetelement number for return on assets and EV/EBITDA, I
> > just get #Value! responses in the array cells.
> >
> > Is it something about this function that can't be used in an array formula?
> >
>
Sat Sep 1, 2012 5:47 pm (PDT) . Posted by:
"barclaywood@ymail.com" barclaywood@ymail.com
Hi, I'm pulling info from Dividata using the RCHGetTableCell command. Specifically, I'm getting their Dividend Increase Rating with the following:
=RCHGetTableCell("http://dividata.com/stock/ "&smfJoin(TICKER,"&stock="),1,">Dividend Increases:")
It is returning data like "4Above Average", "5Excellent", "1Poor", etc. Is there a way to limit the output to just the first character (i.e., the number)? I would love to just have a 1,2,3,4 or 5 printed out.
=RCHGetTableCell(
It is returning data like "4Above Average", "5Excellent"
Sat Sep 1, 2012 6:07 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
You could just use EXCEL's LEFT() function to keep the first byte.
However, it might be easier to get the two items individually, since they
are in their own SPAN tags within the table cell:
=smfGetTagContent("http://dividata.com/stock/ "&B3,"span",1,">Dividend
Increases:")
=smfGetTagContent("http://dividata.com/stock/ "&B3,"span",2,">Dividend
Increases:")
On Sat, Sep 1, 2012 at 5:46 PM, barclaywood@ymail.com <barclaywood@ymail.com
> wrote:
> Hi, I'm pulling info from Dividata using the RCHGetTableCell command.
> Specifically, I'm getting their Dividend Increase Rating with the
> following:
>
> =RCHGetTableCell("http://dividata.com/stock/ "&smfJoin(TICKER,"&stock="),1,">Dividend
> Increases:")
>
> It is returning data like "4Above Average", "5Excellent", "1Poor", etc.
> Is there a way to limit the output to just the first character (i.e., the
> number)? I would love to just have a 1,2,3,4 or 5 printed out.
>
However, it might be easier to get the two items individually, since they
are in their own SPAN tags within the table cell:
=smfGetTagContent(
Increases:")
=smfGetTagContent(
Increases:")
On Sat, Sep 1, 2012 at 5:46 PM, barclaywood@
> wrote:
> Hi, I'm pulling info from Dividata using the RCHGetTableCell command.
> Specifically, I'm getting their Dividend Increase Rating with the
> following:
>
> =RCHGetTableCell(
> Increases:")
>
> It is returning data like "4Above Average", "5Excellent"
> Is there a way to limit the output to just the first character (i.e., the
> number)? I would love to just have a 1,2,3,4 or 5 printed out.
>
Sat Sep 1, 2012 7:32 pm (PDT) . Posted by:
"barclaywood@ymail.com" barclaywood@ymail.com
Thanks! Didn't know about either of those features. Great to learn about both.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> You could just use EXCEL's LEFT() function to keep the first byte.
>
> However, it might be easier to get the two items individually, since they
> are in their own SPAN tags within the table cell:
>
> =smfGetTagContent("http://dividata.com/stock/ "&B3,"span",1,">Dividend
> Increases:")
> =smfGetTagContent("http://dividata.com/stock/ "&B3,"span",2,">Dividend
> Increases:")
>
> On Sat, Sep 1, 2012 at 5:46 PM, barclaywood@... <barclaywood@...
> > wrote:
>
> > Hi, I'm pulling info from Dividata using the RCHGetTableCell command.
> > Specifically, I'm getting their Dividend Increase Rating with the
> > following:
> >
> > =RCHGetTableCell("http://dividata.com/stock/ "&smfJoin(TICKER,"&stock="),1,">Dividend
> > Increases:")
> >
> > It is returning data like "4Above Average", "5Excellent", "1Poor", etc.
> > Is there a way to limit the output to just the first character (i.e., the
> > number)? I would love to just have a 1,2,3,4 or 5 printed out.
> >
>
--- In smf_addin@yahoogrou
>
> You could just use EXCEL's LEFT() function to keep the first byte.
>
> However, it might be easier to get the two items individually, since they
> are in their own SPAN tags within the table cell:
>
> =smfGetTagContent(
> Increases:")
> =smfGetTagContent(
> Increases:")
>
> On Sat, Sep 1, 2012 at 5:46 PM, barclaywood@
> > wrote:
>
> > Hi, I'm pulling info from Dividata using the RCHGetTableCell command.
> > Specifically, I'm getting their Dividend Increase Rating with the
> > following:
> >
> > =RCHGetTableCell(
> > Increases:")
> >
> > It is returning data like "4Above Average", "5Excellent"
> > Is there a way to limit the output to just the first character (i.e., the
> > number)? I would love to just have a 1,2,3,4 or 5 printed out.
> >
>
Sat Sep 1, 2012 7:49 pm (PDT) . Posted by:
"valuhunteruk" valuhunteruk
Hi,
I am a user from the UK and I have been using the smfGetAdvFNElement function for a while successfully (as part of OSV if that matters). I am now getting the standard "Error" message when I use it and the #NAME error with the template ADVFN data dump.
I have tried replacing the " & kAdvFNPrefix & " with uk (putting it directly into the code) but this has not solved the problem although believe that when I started using this SMF in early 2011 this "prefix switch" solved the problem. Unfortunately, this is not the case today.
I am able to use the RCHGetTableCell on uk.advfn.com but RCHGetElementNumber is not working with ADVFN either (in fact, as far as I can tell, only Google is working with RCHGetElementNumber).
Tbh, I am more than happy with RCHGetTableCell and RCHGetWebData however, the problem is only being able to switch tickers through editing the code directly which is obviously very cumbersome. As far as I am aware this can't be circumvented (before discovering SMF I spent many hours trying although I am not good with VB). If anyone knows a solution to this issue I would be grateful too.
Many thanks.
I am a user from the UK and I have been using the smfGetAdvFNElement function for a while successfully (as part of OSV if that matters). I am now getting the standard "Error" message when I use it and the #NAME error with the template ADVFN data dump.
I have tried replacing the " & kAdvFNPrefix & " with uk (putting it directly into the code) but this has not solved the problem although believe that when I started using this SMF in early 2011 this "prefix switch" solved the problem. Unfortunately, this is not the case today.
I am able to use the RCHGetTableCell on uk.advfn.com but RCHGetElementNumber is not working with ADVFN either (in fact, as far as I can tell, only Google is working with RCHGetElementNumber
Tbh, I am more than happy with RCHGetTableCell and RCHGetWebData however, the problem is only being able to switch tickers through editing the code directly which is obviously very cumbersome. As far as I am aware this can't be circumvented (before discovering SMF I spent many hours trying although I am not good with VB). If anyone knows a solution to this issue I would be grateful too.
Many thanks.
Sat Sep 1, 2012 8:04 pm (PDT) . Posted by:
"whitney" thelancour
Does anyone have any other ideas for a function that will extract data from the upper left table at this url http://205.166.161.12/oncorev2/ShowDetails.aspx?BookType=O&Book=48955&Page=1107
I cannot seem to get RCHGettablecell to recognize the table, but excel is able to extract the table, the table appears to be realized in the webpage, pulling my hair out! Thanks!
I cannot seem to get RCHGettablecell to recognize the table, but excel is able to extract the table, the table appears to be realized in the webpage, pulling my hair out! Thanks!
Sat Sep 1, 2012 9:12 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The problem is that the table is presented within a frame on the web page
you cited, so you need to use the URL of the framed web page, where the
data actually is:
=RCHGetTableCell("
http://205.166.161.12/oncorev2/details.aspx?BookType=O&Book=48955&Page=1107 ",1,"Record
Date:")
On Sat, Sep 1, 2012 at 8:04 PM, whitney <thelancour@yahoo.com > wrote:
> Does anyone have any other ideas for a function that will extract data
> from the upper left table at this url
> http://205.166.161.12/oncorev2/ShowDetails.aspx?BookType=O&Book=48955&Page=1107
>
> I cannot seem to get RCHGettablecell to recognize the table, but excel is
> able to extract the table, the table appears to be realized in the
> webpage, pulling my hair out! Thanks!
>
you cited, so you need to use the URL of the framed web page, where the
data actually is:
=RCHGetTableCell(
http://205.166.
Date:")
On Sat, Sep 1, 2012 at 8:04 PM, whitney <thelancour@yahoo.
> Does anyone have any other ideas for a function that will extract data
> from the upper left table at this url
> http://205.166.
>
> I cannot seem to get RCHGettablecell to recognize the table, but excel is
> able to extract the table, the table appears to be realized in the
> webpage, pulling my hair out! Thanks!
>
Tidak ada komentar:
Posting Komentar