8 New Messages
Digest #2499
Messages
Thu Feb 7, 2013 3:47 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Take a look at:
- RCHGetElementNumber() -- a list of elements is in the XLS file
distributed with the ZIP archive
- RCHGetTableCell() -- can extract data off many web pages with
historical data, and most element numbers for the first function are just
the saved parameters of RCHGetTableCell().
- smfGetAdvFNElement() -- retrieves data from AdvFN, which has annual
and quarterly financial statements data back to 1993/1994, for companies
that have been around that long.
For example, any of the following can return the most recent quarterly EPS
of AAPL, from Google or AdvFN:
=RCHGetElementNumber("AAPL",2912)
=RCHGetElementNumber("AAPL",9046)
=RCHGetTableCell("http://www.google.com/finance?fstype=ii&q=AAPL ",1,"Diluted
EPS Excluding Extraordinary Items")
=smfGetAdvFNElement("AAPL";,"Q",1,">Basic EPS (Continuing)")
The last two functions are basically the saved definitions of the first two
element numbers.
On Thu, Feb 7, 2013 at 3:25 AM, dertom4ever dertom4ever@yahoo.de > wrote:
>
> just wondering if you can access historical data about companies/stocks
> through any of the functions of the add-in. I am talking about more than
> just the stock prices and volume, which I know can be accessed through
> rchgetyahoohistory(). I am talking about historical earnings or at least
> earnings per share (parameter "e") for the last quarters/years.
>
> I tried to experiment a little with, for example:
>
> =rchgetyahooquotes("aapl";"e";"MRQ")
> =rchgetyahooquotes("aapl";"e";"mrq-5")
> =rchgetyahooquotes("aapl";"e(mry-4)")
>
> or something like that but nothing seemed to work. However, I think you
> get the idea of what i'm trying to do here (mrq=most recent quarter, mry=mr
> year...)
>
> Can you access this data in any particular way through the add-in?
>
- RCHGetElementNumber
distributed with the ZIP archive
- RCHGetTableCell(
historical data, and most element numbers for the first function are just
the saved parameters of RCHGetTableCell(
- smfGetAdvFNElement(
and quarterly financial statements data back to 1993/1994, for companies
that have been around that long.
For example, any of the following can return the most recent quarterly EPS
of AAPL, from Google or AdvFN:
=RCHGetElementNumbe
=RCHGetElementNumbe
=RCHGetTableCell(
EPS Excluding Extraordinary Items")
=smfGetAdvFNElement
The last two functions are basically the saved definitions of the first two
element numbers.
On Thu, Feb 7, 2013 at 3:25 AM, dertom4ever dertom4ever@
>
> just wondering if you can access historical data about companies/stocks
> through any of the functions of the add-in. I am talking about more than
> just the stock prices and volume, which I know can be accessed through
> rchgetyahoohistory(
> earnings per share (parameter "e"
>
> I tried to experiment a little with, for example:
>
> =rchgetyahooquotes(
> =rchgetyahooquotes(
> =rchgetyahooquotes(
>
> or something like that but nothing seemed to work. However, I think you
> get the idea of what i'm trying to do here (mrq=most recent quarter, mry=mr
> year...)
>
> Can you access this data in any particular way through the add-in?
>
Thu Feb 7, 2013 3:51 am (PST) . Posted by:
"bingouser" bingouser
Thanks. I downloaded the sample table. Does it avoid the problem of having arrays always refreshing? I'm finding that it's a frequent interruption, and from your posts on the subject, it looks like even using a single cell to trigger updates won't eliminate a refresh on adding/deleting rows and columns or closing and opening the workbook. I notice you wrote briefly about another approach that just downloads the data rather than using an array. (http://finance.groups.yahoo.com/group/smf_addin/messages/708?threaded=1&m=e ) Is this an elaboration of that technique? If so, is there any documentation for it--i.e., how to get daily and single-date historical quotes and dividends/yield?
For my other message, TRANSPOSE may work--I didn't know it could be called like that. However, I need to do something about the refresh problem first.
--- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
>
> That's for the smfUpdateDownloadTable macro, not a function...
>
> On Thu, Feb 7, 2013 at 12:06 AM, bingouser wrote:
>
> > I recall seeing a post that mentioned using an "x" in the column header
> > for a particular function to prevent the column from refreshing. I haven't
> > been able to find it with a search. Could someone point me in the right
> > direction?
> >
>
For my other message, TRANSPOSE may work--I didn't know it could be called like that. However, I need to do something about the refresh problem first.
--- In smf_addin@yahoogrou
>
> That's for the smfUpdateDownloadTa
>
> On Thu, Feb 7, 2013 at 12:06 AM, bingouser wrote:
>
> > I recall seeing a post that mentioned using an "x" in the column header
> > for a particular function to prevent the column from refreshing. I haven't
> > been able to find it with a search. Could someone point me in the right
> > direction?
> >
>
Thu Feb 7, 2013 4:15 am (PST) . Posted by:
"Randy Harmelink" rharmelink
On Thu, Feb 7, 2013 at 4:51 AM, bingouser bingouser@yahoo.com > wrote:
>
> Thanks. I downloaded the sample table. Does it avoid the problem of
having arrays always refreshing?
With smfUpdateDownloadTable, NOTHING updates in the table unless you run
the macro. That's because you have VALUES in your data table instead of
FORMULAS.
Unfortunately, if you're using array-entered formulas for speedy retrieval
of data, smfUpdateDownloadTable isn't going to help. It can't use
array-entered formulas. Each web page (or each single-item
RCHGetYahooQuotes() formula) would require a new Internet access.
> I'm finding that it's a frequent interruption, and from your posts on the
subject, it looks like even using a single
> cell to trigger updates won't eliminate a refresh on adding/deleting rows
and columns or closing and opening the
> workbook.
The add-in functions should be non-volatile unless you make them volatile,
so they shouldn't normally recalculate.
However, it is true that deleting or adding rows and columns triggers EXCEL
recalculation.
And opening a workbook will cause a recalculation.
That's the way EXCEL formulas and recalculation work. It's unrelated to the
add-in.
> I notice you wrote briefly about another approach that just downloads the
data rather than using an array
> (
http://finance.groups.yahoo.com/group/smf_addin/messages/708?threaded=1&m=e )
Is this an elaboration
> of that technique? If so, is there any documentation for it--i.e., how to
get daily and single-date historical
> quotes and dividends/yield?
That's similar to what smfUpdateDownloadTable is doing -- placing values in
the workbook instead of using formulas. However, it does require you to
write your own VBA to place the data into the workbook.
> For my other message, TRANSPOSE may work--I didn't know it could be
called like that. However, I need to
> do something about the refresh problem first.
The solution depends a lot on the specifics of what you're doing.
For my own usage of the add-in, because I can grab the data any time I want
it, I rarely have a need to grab much data. It's one reason I wrote the
add-in -- I didn't want to have to collect and store data all the time. I'm
happy to let someone else maintain the data and just retrieve it as needed.
I used to load up databases of EVERYTHING back in the 80's, and it's a lot
of unnecessary work.
>
> Thanks. I downloaded the sample table. Does it avoid the problem of
having arrays always refreshing?
With smfUpdateDownloadTa
the macro. That's because you have VALUES in your data table instead of
FORMULAS.
Unfortunately, if you're using array-entered formulas for speedy retrieval
of data, smfUpdateDownloadTa
array-entered formulas. Each web page (or each single-item
RCHGetYahooQuotes(
> I'm finding that it's a frequent interruption, and from your posts on the
subject, it looks like even using a single
> cell to trigger updates won't eliminate a refresh on adding/deleting rows
and columns or closing and opening the
> workbook.
The add-in functions should be non-volatile unless you make them volatile,
so they shouldn'
However, it is true that deleting or adding rows and columns triggers EXCEL
recalculation.
And opening a workbook will cause a recalculation.
That's the way EXCEL formulas and recalculation work. It's unrelated to the
add-in.
> I notice you wrote briefly about another approach that just downloads the
data rather than using an array
> (
http://finance.
Is this an elaboration
> of that technique? If so, is there any documentation for it--i.e., how to
get daily and single-date historical
> quotes and dividends/yield?
That's similar to what smfUpdateDownloadTa
the workbook instead of using formulas. However, it does require you to
write your own VBA to place the data into the workbook.
> For my other message, TRANSPOSE may work--I didn't know it could be
called like that. However, I need to
> do something about the refresh problem first.
The solution depends a lot on the specifics of what you're doing.
For my own usage of the add-in, because I can grab the data any time I want
it, I rarely have a need to grab much data. It's one reason I wrote the
add-in -- I didn't want to have to collect and store data all the time. I'm
happy to let someone else maintain the data and just retrieve it as needed.
I used to load up databases of EVERYTHING back in the 80's, and it's a lot
of unnecessary work.
Thu Feb 7, 2013 4:38 am (PST) . Posted by:
"bingouser" bingouser
> With smfUpdateDownloadTa
>
> Unfortunately, if you're using array-entered formulas for speedy retrieval of data, smfUpdateDownloadTa
> RCHGetYahooQuotes(
Even if each one requires a new access, as historical quotes and historical divs do now, if the smfUpdateDownloadTa
Thu Feb 7, 2013 5:40 am (PST) . Posted by:
"Randy Harmelink" rharmelink
On Thu, Feb 7, 2013 at 5:38 AM, bingouser bingouser@yahoo.com > wrote:
> Even if each one requires a new access, as historical quotes and
historical divs do now, if the
> smfUpdateDownloadTable cells have values instead of formulas, presumably
they wouldn't refresh when
> adding/deleting rows or columns or opening or closing a workbook?
But it would be much faster to let the data refresh than to get it piece by
piece, if you're getting hundreds of data items, since one Internet access
can get thousands of pieces of data.
If you need a lot of RCHGetYahooQuotes() data items, you'd be better off
writing your own VBA to load the data into the workbook. For example,
something like:
Sub Test()
Range("C3:M202") = RCHGetYahooQuotes(Range("B3:B202"), Range("C2:M2"),
pDim1:=200, pDim2:=24)
End Sub
...which gives me:
l1 d1 t1 s IBM 201.02 2/6/2013 4:01pm IBM MMM
102.69 2/6/2013 4:00pm MMM
However, then your ranges are hard-coded, so inserts/deletions of
rows/columns won't adjust them. You'd be better off using range names,
which would adjust. Another way to go about it, keying off only a single
cell:
Sub Test()
Dim rStart As Range, rOutput As Range, rTickers As Range, rCodes As
Range
Set rStart = Range("B2")
Set rOutput = Range(rStart.Offset(1, 1), rStart.Offset(200, 24))
Set rTickers = Range(rStart.Offset(1, 0), rStart.Offset(200, 0))
Set rCodes = Range(rStart.Offset(0, 1), rStart.Offset(0, 24))
rOutput = RCHGetYahooQuotes(rTickers, rCodes, pDim1:=200, pDim2:=24)
End Sub
Then, if you have multiple sets of 200 ticker symbols, you could just put
that in a loop and modify your rOutput and rTickers ranges relative to the
loop counter.
> The historical data doesn't need to be refreshed, so I'm happy to let it
sit there.
Using your own VBA routine, correct? Not smfUpdateDownloadTable.
smfUpdateDownloadTable won't work well with historical quotes, unless
you're getting a single closing quote.
> Is there a way to enter a formula in row 2 of the smfUpdateDownloadTable
to access that info? I tried a few
> variations of =RCHGetYahooQuotes() (with l1 in C3), but all I get in the
column is NAME? or random numbers.
RCHGetYahooQuotes("~~~~~","l1")
It's technically NOT a formula, but a text string that
smfUpdateDownloadTable will process as a formula, after substituting in the
ticker symbol where the five tildas are..
> Even if each one requires a new access, as historical quotes and
historical divs do now, if the
> smfUpdateDownloadTa
they wouldn't refresh when
> adding/deleting rows or columns or opening or closing a workbook?
But it would be much faster to let the data refresh than to get it piece by
piece, if you're getting hundreds of data items, since one Internet access
can get thousands of pieces of data.
If you need a lot of RCHGetYahooQuotes(
writing your own VBA to load the data into the workbook. For example,
something like:
Sub Test()
Range("
pDim1:=200, pDim2:=24)
End Sub
...which gives me:
l1 d1 t1 s IBM 201.02 2/6/2013 4:01pm IBM MMM
102.69 2/6/2013 4:00pm MMM
However, then your ranges are hard-coded, so inserts/deletions of
rows/columns won't adjust them. You'd be better off using range names,
which would adjust. Another way to go about it, keying off only a single
cell:
Sub Test()
Dim rStart As Range, rOutput As Range, rTickers As Range, rCodes As
Range
Set rStart = Range("
Set rOutput = Range(rStart.
Set rTickers = Range(rStart.
Set rCodes = Range(rStart.
rOutput = RCHGetYahooQuotes(
End Sub
Then, if you have multiple sets of 200 ticker symbols, you could just put
that in a loop and modify your rOutput and rTickers ranges relative to the
loop counter.
> The historical data doesn't need to be refreshed, so I'm happy to let it
sit there.
Using your own VBA routine, correct? Not smfUpdateDownloadTa
smfUpdateDownloadTa
you're getting a single closing quote.
> Is there a way to enter a formula in row 2 of the smfUpdateDownloadTa
to access that info? I tried a few
> variations of =RCHGetYahooQuotes(
column is NAME? or random numbers.
RCHGetYahooQuotes(
It's technically NOT a formula, but a text string that
smfUpdateDownloadTa
ticker symbol where the five tildas are..
Thu Feb 7, 2013 4:00 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Not if you look here:
http://finance.yahoo.com/q/ks?s=BP
That is, 4.5% is the TRAILING dividend, while 4.9% is the
*expected*FORWARD dividend (which may not come to pass).
Unfortunately, Yahoo's CSV file interface, which is used by
RCHGetYahooQuotes(), only has the ability to return the trailing dividend.
RWR doesn't have a Key Statistics page, but you can see the TTM of
dividends here:
http://finance.yahoo.com/q/hp?s=RWR&g=v
...which is a 2.9% yield.
On Thu, Feb 7, 2013 at 4:24 AM, aeonf22 ori@gutman.com> wrote:
> Hello all.
>
> using the =RCHGetYahooQuotes(BP,"y") function for example I get a DIV
> yield of 4.5
> When I go to yahoo quotes manually to compare, I get a yield of 4.9:
> http://finance.yahoo.com/q?s=bp&ql=1
>
> another example is RWR
> functions returns a value of 2.9%, yahoo states 3.05%
> GE is another example.
>
http://finance.
That is, 4.5% is the TRAILING dividend, while 4.9% is the
*expected*FORWARD dividend (which may not come to pass).
Unfortunately, Yahoo's CSV file interface, which is used by
RCHGetYahooQuotes(
RWR doesn't have a Key Statistics page, but you can see the TTM of
dividends here:
http://finance.
...which is a 2.9% yield.
On Thu, Feb 7, 2013 at 4:24 AM, aeonf22 ori@gutman.com> wrote:
> Hello all.
>
> using the =RCHGetYahooQuotes(
> yield of 4.5
> When I go to yahoo quotes manually to compare, I get a yield of 4.9:
> http://finance.
>
> another example is RWR
> functions returns a value of 2.9%, yahoo states 3.05%
> GE is another example.
>
Thu Feb 7, 2013 7:19 am (PST) . Posted by:
"raniersfury" raniersfury
First off, thanks for putting this group together and to the members who have posted templates - it's saved countless hours of data pulling.
Is there a template out there that tracks historic PE (high, low) by year, for the past 10 years?
Also, I can't seem to get the 10 Year Financial Summary workbook to pull complete data for Berkshire Hathaway, either the BRK.A or the BRK.B tickers. Using "BRK" appears to pull about 5 years of data, but even that seems to have a few errors.
Thanks in advance for any help with one or both of the above.
Is there a template out there that tracks historic PE (high, low) by year, for the past 10 years?
Also, I can't seem to get the 10 Year Financial Summary workbook to pull complete data for Berkshire Hathaway, either the BRK.A or the BRK.B tickers. Using "BRK" appears to pull about 5 years of data, but even that seems to have a few errors.
Thanks in advance for any help with one or both of the above.
Thu Feb 7, 2013 7:20 am (PST) . Posted by:
"Hari" harishr30
Hi,
Sub: Any Excel macro file available for downloading all the real-time
stock price data from yahoo finance
Is there any excel macro file available to download all the important
fields from yahoo finance like price, next earnings date etc. for all
around 500 stock tickers.
Please help.
Regards,
Harish
Tidak ada komentar:
Posting Komentar