14 New Messages
Digest #2553
Messages
Tue Mar 19, 2013 6:05 am (PDT) . Posted by:
"jtravisano2002" jtravisano2002
Thank you!Really appreciate the work you've done!
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> You're talking about an industry group, not a sector.
>
> Yahoo doesn't carry historical quotes on industry groups, but they do have
> current quotes. For example:
>
> =RCHGetYahooQuotes("^YHOh829","l1")
>
> The list of available industry groups on Yahoo:
>
> http://biz.yahoo.com/ic/ind_index.html
>
> BarChart might be a better source:
>
> http://www.barchart.com/stocks/sectors/
>
> It looks like you could use the
> RCHGetWebData-Template-BarChart-Quotes-Extract.xls workbook to get
> historical quotes from there...
>
> On Sun, Mar 17, 2013 at 10:35 AM, jtravisano2002
> <jacob.travisano@...>wrote:
>
> >
> > Is there a way to download historical S&P sector data(daily or monthly)
> > ? For instance one of the sectors is Radio Broadcasting, can I get the
> > daily(or month end) value for this sector for each month? If yes, how far
> > back does the data go?
> >
>
--- In smf_addin@yahoogrou
>
> You're talking about an industry group, not a sector.
>
> Yahoo doesn't carry historical quotes on industry groups, but they do have
> current quotes. For example:
>
> =RCHGetYahooQuotes(
>
> The list of available industry groups on Yahoo:
>
> http://biz.yahoo.
>
> BarChart might be a better source:
>
> http://www.barchart
>
> It looks like you could use the
> RCHGetWebData-
> historical quotes from there...
>
> On Sun, Mar 17, 2013 at 10:35 AM, jtravisano2002
> <jacob.travisano
>
> >
> > Is there a way to download historical S&P sector data(daily or monthly)
> > ? For instance one of the sectors is Radio Broadcasting, can I get the
> > daily(or month end) value for this sector for each month? If yes, how far
> > back does the data go?
> >
>
Tue Mar 19, 2013 7:16 am (PDT) . Posted by:
"friedman_steve" friedman_steve
Randy,
Thanks for all your help in advance.
I'm trying to speed up things for me by using arrays. I typically have a master excel sheet with hundreds of tickers down a column, and several variables across many columns for each. Can you clarify/help with below? I do have arrays working for stock prices.
1) GetElementNumber: is there a way to enter as an array or something to speed this up? didn't seem to work for me? Either by entering multiples columns at once, or multiple tickers?
I currently just have something like: =RCHGetElementNumber($A2,F$10)
2)GetCSVFile for Finviz: I have formula to grab many columns at once:
=smfGetCSVFile("http://finviz.com/export.ashx?v=151&t=MMM&c=1,3,4,5,68 ")
a) is it faster having 2 stocks listed in the same formula than just listing 1 and running twice? How do I do it where it references the tickers in column A (even if just one ticker)?
b) can I alter the formula so the table is produced WITHOUT headers (just the numbers data).
AND how does the above compare in speed with using the GetTableCell formula?
=RCHGetTableCell("http://finviz.com/quote.ashx?t= "&smfJoin($A$22:$A$100,","),1,"?t="&$A22&"&",">"&C$17)
3) is the GetCSVFile also work for Yahoo key stats? is it faster than just using the GetElementNumber?
Thanks Randy
Thanks for all your help in advance.
I'm trying to speed up things for me by using arrays. I typically have a master excel sheet with hundreds of tickers down a column, and several variables across many columns for each. Can you clarify/help with below? I do have arrays working for stock prices.
1) GetElementNumber: is there a way to enter as an array or something to speed this up? didn't seem to work for me? Either by entering multiples columns at once, or multiple tickers?
I currently just have something like: =RCHGetElementNumbe
2)GetCSVFile for Finviz: I have formula to grab many columns at once:
=smfGetCSVFile(
a) is it faster having 2 stocks listed in the same formula than just listing 1 and running twice? How do I do it where it references the tickers in column A (even if just one ticker)?
b) can I alter the formula so the table is produced WITHOUT headers (just the numbers data).
AND how does the above compare in speed with using the GetTableCell formula?
=RCHGetTableCell(
3) is the GetCSVFile also work for Yahoo key stats? is it faster than just using the GetElementNumber?
Thanks Randy
Tue Mar 19, 2013 9:38 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
On Tue, Mar 19, 2013 at 7:16 AM, friedman_steve <friedman_steve@yahoo.com >wrote:
> Randy,
> Thanks for all your help in advance.
> I'm trying to speed up things for me by using arrays. I typically have a
> master excel sheet with hundreds of tickers down a column, and several
> variables across many columns for each. Can you clarify/help with below?
> I do have arrays working for stock prices.
>
> 1) GetElementNumber: is there a way to enter as an array or something to
> speed this up? didn't seem to work for me? Either by entering multiples
> columns at once, or multiple tickers?
> I currently just have something like: =RCHGetElementNumber($A2,F$10)
>
No. There's no reason to have an array-entered version of this function. It
would still require the same number of Internet accesses and element
extractions.
The reason RCHGetYahooQuotes() is faster as an array-entered function is
that Yahoo creates a custom CSV file based on the parameters passed to it
-- multiple tickers and multiple data items. But when data is retrieved
from a web page, you still need to retrieve each individual web page. Other
than where a data provider creates a downloadable file, there is nothing
out there to automatically aggregate the data in a single Internet access.
2)GetCSVFile for Finviz: I have formula to grab many columns at once:
> =smfGetCSVFile("http://finviz.com/export.ashx?v=151&t=MMM&c=1,3,4,5,68 ")
> a) is it faster having 2 stocks listed in the same formula than
> just listing 1 and running twice? How do I do it where it references the
> tickers in column A (even if just one ticker)?
>
Yes, it would be faster, because each invocation of the function is a
separate Internet access.
The second question is answered in your next question -- use the smfJoin()
function.
b) can I alter the formula so the table is produced WITHOUT headers
> (just the numbers data).
> AND how does the above compare in speed with using the GetTableCell
> formula?
> =RCHGetTableCell("http://finviz.com/quote.ashx?t=
> "&smfJoin($A$22:$A$100,","),1,"?t="&$A22&"&",">"&C$17)
>
I don't know of a way to suppress headers from the CSV file.
The smfGetCSVFile() function (with multiple tickers) would be only slightly
faster. Both would have a single Internet access, but parsing of the CSV
file would be slightly faster than extraction of each individual item from
the returned web page when using RCHGetTableCell().
> 3) is the GetCSVFile also work for Yahoo key stats? is it faster than just
> using the GetElementNumber?
>
There is no CSV file available that packages up all of the data on the
Yahoo Key Statistics web page.
The biggest time cruncher on RCHGetElementNumber() and RCHGetTableCell() is
that you need to do an Internet access of each unique URL that data is
being extracted from. The add-in stores a copy of the retrieved web page,
and then extracts the data from that stored copy of the web page. So if you
pull multiple data items from the same web page, it only needs to get it
from the Internet once, because the additional extractions will come from
the stored copy of the web page.
> Randy,
> Thanks for all your help in advance.
> I'm trying to speed up things for me by using arrays. I typically have a
> master excel sheet with hundreds of tickers down a column, and several
> variables across many columns for each. Can you clarify/help with below?
> I do have arrays working for stock prices.
>
> 1) GetElementNumber: is there a way to enter as an array or something to
> speed this up? didn't seem to work for me? Either by entering multiples
> columns at once, or multiple tickers?
> I currently just have something like: =RCHGetElementNumbe
>
No. There's no reason to have an array-entered version of this function. It
would still require the same number of Internet accesses and element
extractions.
The reason RCHGetYahooQuotes(
that Yahoo creates a custom CSV file based on the parameters passed to it
-- multiple tickers and multiple data items. But when data is retrieved
from a web page, you still need to retrieve each individual web page. Other
than where a data provider creates a downloadable file, there is nothing
out there to automatically aggregate the data in a single Internet access.
2)GetCSVFile for Finviz: I have formula to grab many columns at once:
> =smfGetCSVFile(
> a) is it faster having 2 stocks listed in the same formula than
> just listing 1 and running twice? How do I do it where it references the
> tickers in column A (even if just one ticker)?
>
Yes, it would be faster, because each invocation of the function is a
separate Internet access.
The second question is answered in your next question -- use the smfJoin()
function.
b) can I alter the formula so the table is produced WITHOUT headers
> (just the numbers data).
> AND how does the above compare in speed with using the GetTableCell
> formula?
> =RCHGetTableCell(
> "&smfJoin(
>
I don't know of a way to suppress headers from the CSV file.
The smfGetCSVFile(
faster. Both would have a single Internet access, but parsing of the CSV
file would be slightly faster than extraction of each individual item from
the returned web page when using RCHGetTableCell(
> 3) is the GetCSVFile also work for Yahoo key stats? is it faster than just
> using the GetElementNumber?
>
There is no CSV file available that packages up all of the data on the
Yahoo Key Statistics web page.
The biggest time cruncher on RCHGetElementNumber
that you need to do an Internet access of each unique URL that data is
being extracted from. The add-in stores a copy of the retrieved web page,
and then extracts the data from that stored copy of the web page. So if you
pull multiple data items from the same web page, it only needs to get it
from the Internet once, because the additional extractions will come from
the stored copy of the web page.
Tue Mar 19, 2013 9:43 am (PDT) . Posted by:
"cgrablew" cgrablew
Randy,
I have a spreadsheet that uses smfupdatetable to retrieve approximately 246 rows with 44 columns of data (10,824) elements. The refresh is taking just under a hour. This process has been in place for a few years now and seems to running longer. I'm wondering if there is a better way doing this now maybe using smfGetCSVFile or something else.
Thanks
Chuck Grablewski
PS - I could send you the spreadsheet if that would help.
I have a spreadsheet that uses smfupdatetable to retrieve approximately 246 rows with 44 columns of data (10,824) elements. The refresh is taking just under a hour. This process has been in place for a few years now and seems to running longer. I'm wondering if there is a better way doing this now maybe using smfGetCSVFile or something else.
Thanks
Chuck Grablewski
PS - I could send you the spreadsheet if that would help.
Tue Mar 19, 2013 9:49 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
If some elements can be retrieved using the FinViz or Yahoo CSV files, that
may be a faster way to get those data elements. Ultimately, the speed is
primarily based on the number of web pages you need to retrieve to get the
data.
On Tue, Mar 19, 2013 at 9:43 AM, cgrablew <cgrablew@gmail.com > wrote:
>
> I have a spreadsheet that uses smfupdatetable to retrieve
> approximately 246 rows with 44 columns of data (10,824) elements. The
> refresh is taking just under a hour. This process has been in place for a
> few years now and seems to running longer. I'm wondering if there is a
> better way doing this now maybe using smfGetCSVFile or something else.
>
> Thanks
>
> Chuck Grablewski
>
> PS - I could send you the spreadsheet if that would help.
>
may be a faster way to get those data elements. Ultimately, the speed is
primarily based on the number of web pages you need to retrieve to get the
data.
On Tue, Mar 19, 2013 at 9:43 AM, cgrablew <cgrablew@gmail.
>
> I have a spreadsheet that uses smfupdatetable to retrieve
> approximately 246 rows with 44 columns of data (10,824) elements. The
> refresh is taking just under a hour. This process has been in place for a
> few years now and seems to running longer. I'm wondering if there is a
> better way doing this now maybe using smfGetCSVFile or something else.
>
> Thanks
>
> Chuck Grablewski
>
> PS - I could send you the spreadsheet if that would help.
>
Tue Mar 19, 2013 2:47 pm (PDT) . Posted by:
"joshi_mandar" joshi_mandar
Randy - Do you know if this tool works for mini options? You may or may not know mini-options (10 shares instead of 100 shares) are now available for AAPL and few other company stocks.
Tue Mar 19, 2013 2:55 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I haven't made any changes for the min-options...
I haven't even looked at them.
On Tue, Mar 19, 2013 at 2:47 PM, joshi_mandar <joshi_mandar@hotmail.com >wrote:
> Randy - Do you know if this tool works for mini options? You may or may
> not know mini-options (10 shares instead of 100 shares) are now available
> for AAPL and few other company stocks.
>
I haven't even looked at them.
On Tue, Mar 19, 2013 at 2:47 PM, joshi_mandar <joshi_mandar@
> Randy - Do you know if this tool works for mini options? You may or may
> not know mini-options (10 shares instead of 100 shares) are now available
> for AAPL and few other company stocks.
>
Tue Mar 19, 2013 4:24 pm (PDT) . Posted by:
"freefaller6" freefaller6
Hi Randy, this value is coming up error; cell reference is 13883. Would you please look into this? Thanks.
Tue Mar 19, 2013 4:32 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Element #13883 is coming up fine here. But it's the Price/Cash Flow from
Zacks, not an EPS (ttm) field...
On Tue, Mar 19, 2013 at 4:24 PM, freefaller6 <freefaller6@yahoo.com > wrote:
> Hi Randy, this value is coming up error; cell reference is 13883. Would
> you please look into this? Thanks.
>
Zacks, not an EPS (ttm) field...
On Tue, Mar 19, 2013 at 4:24 PM, freefaller6 <freefaller6@
> Hi Randy, this value is coming up error; cell reference is 13883. Would
> you please look into this? Thanks.
>
Tue Mar 19, 2013 8:35 pm (PDT) . Posted by:
"Lawrence" lawleesh
Thanks Randy. I managed to find it.
Is there something similar for downloading from markets.ft.com? I could not find it in the files area.
regards
LL
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Check out the template in the FILES area:
>
> RCHGetTableCell-Template-Yahoo-Financial-Statements.xls
>
> On Mon, Mar 18, 2013 at 8:48 PM, Lawrence <lawrence.leesh@...> wrote:
>
> >
> > Yahoo Finance gives four years of historical P&L and B/S. Is there a way
> > (e.g. via addin) to download revenue, eps, etc into excel?
> >
>
Is there something similar for downloading from markets.ft.com? I could not find it in the files area.
regards
LL
--- In smf_addin@yahoogrou
>
> Check out the template in the FILES area:
>
> RCHGetTableCell-
>
> On Mon, Mar 18, 2013 at 8:48 PM, Lawrence <lawrence.
>
> >
> > Yahoo Finance gives four years of historical P&L and B/S. Is there a way
> > (e.g. via addin) to download revenue, eps, etc into excel?
> >
>
Tue Mar 19, 2013 8:51 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Nothing, as far as I know...
On Tue, Mar 19, 2013 at 8:35 PM, Lawrence <lawrence.leesh@gmail.com > wrote:
>
> Is there something similar for downloading from markets.ft.com? I could
> not find it in the files area.
>
On Tue, Mar 19, 2013 at 8:35 PM, Lawrence <lawrence.leesh@
>
> Is there something similar for downloading from markets.ft.com? I could
> not find it in the files area.
>
Tue Mar 19, 2013 9:52 pm (PDT) . Posted by:
"T" thomas777crown
=INDEX(smfTech(RCHGetYahooHistory("GRO",,,,,,,,,,1,1,51,6),"EMA",50),51)
I keep on getting the wrong value for the moving average for 50 days. Is this the right formula to get an Exponential moving average for the last 50 days. Also what would be the right formula for 100 days as well.
Cheers,
T
I keep on getting the wrong value for the moving average for 50 days. Is this the right formula to get an Exponential moving average for the last 50 days. Also what would be the right formula for 100 days as well.
Cheers,
T
Tue Mar 19, 2013 10:18 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Because the nature of an EMA is to build from the previous day's value, you
need to give it more than 51 days to build a "true" 50-day EMA, to a point
where the oldest data is no longer a factor. Try something like:
=INDEX(smfTech(RCHGetYahooHistory("GRO",,,,,,,,,,1,1,300,6),"EMA",50),300)
For 100, I'd do something like:
=INDEX(smfTech(RCHGetYahooHistory("GRO",,,,,,,,,,1,1,500,6),"EMA",100),500)
On Tue, Mar 19, 2013 at 9:52 PM, T <thomas777crown@yahoo.com > wrote:
> =INDEX(smfTech(RCHGetYahooHistory("GRO",,,,,,,,,,1,1,51,6),"EMA",50),51)
>
> I keep on getting the wrong value for the moving average for 50 days. Is
> this the right formula to get an Exponential moving average for the last 50
> days. Also what would be the right formula for 100 days as well.
>
need to give it more than 51 days to build a "true" 50-day EMA, to a point
where the oldest data is no longer a factor. Try something like:
=INDEX(smfTech(
For 100, I'd do something like:
=INDEX(smfTech(
On Tue, Mar 19, 2013 at 9:52 PM, T <thomas777crown@
> =INDEX(smfTech(
>
> I keep on getting the wrong value for the moving average for 50 days. Is
> this the right formula to get an Exponential moving average for the last 50
> days. Also what would be the right formula for 100 days as well.
>
Tue Mar 19, 2013 10:19 pm (PDT) . Posted by:
"tar044" tar044
In order to lookup the FINRA code, I used below:
Assuming CUSIP is in cell D5:
=RCHGetWebData(
--- In smf_addin@yahoogrou
>
> The FINRA website maintains price, description and transation data: http://cxa.gtm.
> I am trying to extract Composite Trade Information. For example: CUSIP 167593JA1, the Bond Detail page is at this adddress: http://cxa.gtm.
>
> The Bond Detail page display both fixed description information and variable price/yield information. At the bottom of the page in the section Composite Trade Information, I want to extract in the last 3 lines on the left of the table: Date, Price and Yield.
>
> I created an Excel table with these fields:
> CUSIP: 167593JA1
>
> Short Name: CHICAGO IL B 3.75 010128
>
> Bond Detail Page address: http://cxa.gtm.
>
> FINRA ID: MTY3NTkzSkEx [extracted from the page address]
>
> Price: =RCHGetTableCell(
>
> Date: RCHGetTableCell(
>
> Any ideas?
>
Tidak ada komentar:
Posting Komentar