Sorry, it took so long to get back to you. My head still hurts from
last nights celebrations.
Excel did in fact lock up along with some other programs I was
running at the time. I had tried to change the reference earlier, but
it did not seem to do it correctly. This AM is updated the links in
a virgin copy of the SMF-Charts.XLS and the references updated
correctly. All seems to be working well at the moment.
--- In smf_addin@yahoogroups.com, "Randy Harmelink" <rharmelink@...>
wrote:
>
> --- "Ron Gaillard" <ron.gaillard@> wrote:
> >
> > Excel locked up.
>
> Are you sure it's locking up? It can take 30-40 seconds to update
all
> of the calculations. During that time, I see an hourglass, but no
> note on the status bar that recalculation is being done. However, I
> do normally see the recalculation message, so I'm assuming this is a
> side effect related to the updating of the add-in links?
>
> > entered =RCHGetYahooHistory("DIS",2004,1,1,2004,12,31,"d")
> > Cell returns the word Date
>
> That is the proper response, unless you array-entered the formula
over
> a range of cells.
>
> > Open the file SMF-Charts.xls and get the original error with that
> > file.
>
> I'm beginning to think that will ALWAYS be the case unless both
> machines (the one that created the workbook and the one opening the
> workbook) have the add-in located under the exact same file path.
>
> Here's what I did to duplicate the errors and get to a "corrected"
> workbook (note I have alternative steps 3-5):
>
> 1. I turned off the add-in from menu option > Tools > Add-ins. This
> should allow me to get the same error messages you are seeing,
because
> my add-in links will not be working automatically.
>
> 2. I opened SMF-Charts.XLS from the Yahoo Groups library
>
> 3A. Error message about links to other data sources -- I told it to
> update the links
>
> 4A. Error message about links not being updatable -- I told it to
continue
>
> 5A. I'm now in a copy of the spreadsheet filled with "#NAME?" values
> in cells. This is to be expected, because none of the add-in
> functions are available.
>
> 3B. Error message about links to other data sources -- this time I
> told it "Don't Update".
>
> 4B. I'm now in a copy of the spreadsheet with the data the way the
> spreadsheet was saved (no recalculation was done because I told it
not
> to update). However, everywhere I used an add-in formula, the
formula
> looks something like this:
>
> 'C:\Documents and Settings\Administrator\Application
> Data\Microsoft\AddIns\RCH_Stock_Market_Functions.xla'!
RCHGetElementNumber(Ticker,9546)
>
> The file paths are the links that need to be updated.
>
> 5B. ...
>
> 6. I turn on the add-in from menu option > Tools > Add-ins. This
> apparently automatically updates all of the links to the library
> installed on the machine opening the workbook.
>
> 7. I immediately get the hourglass. However, status bar does NOT
> indicate calculation is occurring -- but it is. After about 35
> seconds, my charts appear. It is doing about 17 web page
retrievals,
> so I guess that's to be expected.
>
--- "Kermit W. Prather" <kermitp@...> wrote:
>
> I followed your steps and when I did #6 I still got the #name in
> all the cells for the smf-charts.xls workbook.
With or without an hourglass?
If without, check to see what the #NAME? formulas are referring to.
For example, when I went throught the steps, one of mine was:
='C:\Documents and Settings\Administrator\Application
Data\Microsoft\AddIns\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker\
,9546)
I followed your steps and when I did #6 I still got the #name in all the
cells for the smf-charts.xls workbook.
Kermit Prather
"Never be afraid to ask questions"
----- Original Message -----
From: "Randy Harmelink" <rharmelink@...>
To: <smf_addin@yahoogroups.com>
Sent: Tuesday, July 04, 2006 10:34 PM
Subject: [smf_addin] Re: File Uploaded: SMF-Charts.XLS
> --- "Ron Gaillard" <ron.gaillard@...> wrote:
>>
>> Excel locked up.
>
> Are you sure it's locking up? It can take 30-40 seconds to update all
> of the calculations. During that time, I see an hourglass, but no
> note on the status bar that recalculation is being done. However, I
> do normally see the recalculation message, so I'm assuming this is a
> side effect related to the updating of the add-in links?
>
>> entered =RCHGetYahooHistory("DIS",2004,1,1,2004,12,31,"d")
>> Cell returns the word Date
>
> That is the proper response, unless you array-entered the formula over
> a range of cells.
>
>> Open the file SMF-Charts.xls and get the original error with that
>> file.
>
> I'm beginning to think that will ALWAYS be the case unless both
> machines (the one that created the workbook and the one opening the
> workbook) have the add-in located under the exact same file path.
>
> Here's what I did to duplicate the errors and get to a "corrected"
> workbook (note I have alternative steps 3-5):
>
> 1. I turned off the add-in from menu option > Tools > Add-ins. This
> should allow me to get the same error messages you are seeing, because
> my add-in links will not be working automatically.
>
> 2. I opened SMF-Charts.XLS from the Yahoo Groups library
>
> 3A. Error message about links to other data sources -- I told it to
> update the links
>
> 4A. Error message about links not being updatable -- I told it to continue
>
> 5A. I'm now in a copy of the spreadsheet filled with "#NAME?" values
> in cells. This is to be expected, because none of the add-in
> functions are available.
>
> 3B. Error message about links to other data sources -- this time I
> told it "Don't Update".
>
> 4B. I'm now in a copy of the spreadsheet with the data the way the
> spreadsheet was saved (no recalculation was done because I told it not
> to update). However, everywhere I used an add-in formula, the formula
> looks something like this:
>
> 'C:\Documents and Settings\Administrator\Application
>
Data\Microsoft\AddIns\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker\
,9546)
>
> The file paths are the links that need to be updated.
>
> 5B. ...
>
> 6. I turn on the add-in from menu option > Tools > Add-ins. This
> apparently automatically updates all of the links to the library
> installed on the machine opening the workbook.
>
> 7. I immediately get the hourglass. However, status bar does NOT
> indicate calculation is occurring -- but it is. After about 35
> seconds, my charts appear. It is doing about 17 web page retrievals,
> so I guess that's to be expected.
>
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
--- "Kermit W. Prather" <kermitp@...> wrote:
>
> Randy, in my case I think the problem is that it is looking for
> RCH_Stock_Market_Functions not Stock Market Functions. I think you
> have confused us and Excel by renaming the add-in.
Did you try the different paths I described in message 23?
When I went through that process, I found out that when you save a
workbook that uses add-in formulas, it saves the absolute path of the
add-in in each formula in the workbook. It is THOSE links EXCEL is
complaining about when you open SMF-Charts.XLS.
That problem will exist EVERY time a workbook with add-in references
is opened and the add-in library is in a different location on the
host machine.
Randy, in my case I think the problem is that it is looking for
RCH_Stock_Market_Functions not Stock Market Functions. I think you have
confused us and Excel by renaming the add-in.
I, also, think while removing the old version I also removed the
RCHgetyahoohistory or it is still looking for it by the old name RCH_Stock
Kermit Prather
"Never be afraid to ask questions"
----- Original Message -----
From: "Randy Harmelink" <rharmelink@...>
To: <smf_addin@yahoogroups.com>
Sent: Tuesday, July 04, 2006 6:52 PM
Subject: [smf_addin] Re: File Uploaded: SMF-Charts.XLS
> --- "rgaillard" <ron.gaillard@...> wrote:
>>
>> replys the word "Stock" in the block
>
> When I enter:
>
> =RCHGetElementNumber("Version")
>
> I get this value in the cell:
>
> Stock Market Functions add-in, Version 1.3b
>
> So you must only be seeing the starting part of the message. Is there
> something in the cell after the one you entered the formula in.
>
> Which of these do you have in your list of add-ins (menu option >
> Tools > Add-ins):
>
>> RCH_Stock_Market_Functions
>> Stock Market Functions Add-In
>
> The former would have been the old version, the latter is 1.3b. To
> install 1.3b properly, the former should no longer be on the list and
> the latter should be checked.
>
>
>
>
>
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
It says I have Stock Market Functions add-in, version 1.3b So at least we
have the correct version
However, when I open the SMF-Charts.XLS I get message about the link
to another workbook.
When I click yes it open a window and is looking for
RCH_Stock_market_functions.xla
when I point it to the add-in directory I then get the message box
Kermit Prather
"Never be afraid to ask questions"
----- Original Message -----
From: "Randy Harmelink" <rharmelink@...>
To: <smf_addin@yahoogroups.com>
Sent: Tuesday, July 04, 2006 6:07 PM
Subject: [smf_addin] Re: File Uploaded: SMF-Charts.XLS
> --- "Kermit W. Prather" <kermitp@...> wrote:
>>
>> Not sure what I did wrong but when I tried to replace version 1.2
>> with 1.3B it does not appear to have added the add-in. It is not
>> list among the add-ins where it was previously.
>>
>> However, if I open the VB editor it shows a VBA project for
>> RCH_Stock_market_functions.xla
>
> I think I caused this error. When I rebuilt the add-in to reduce the
> space down from 8MB to 3MB, I named it internally as "Stock Market
> Functions Add-In". Do you have that on your add-in list along with
> the RCH version? If so, it may be causing a conflict. You should
> toggle off the RCH_ version and toggle on the new one.
>
> You can test which version you are using with this formula:
>
> =RCHGetElementNumber("Version")
>
> Let me know...
>
--- "Ron Gaillard" <ron.gaillard@...> wrote:
>
> Excel locked up.
Are you sure it's locking up? It can take 30-40 seconds to update all
of the calculations. During that time, I see an hourglass, but no
note on the status bar that recalculation is being done. However, I
do normally see the recalculation message, so I'm assuming this is a
side effect related to the updating of the add-in links?
> entered =RCHGetYahooHistory("DIS",2004,1,1,2004,12,31,"d")
> Cell returns the word Date
That is the proper response, unless you array-entered the formula over
a range of cells.
> Open the file SMF-Charts.xls and get the original error with that
> file.
I'm beginning to think that will ALWAYS be the case unless both
machines (the one that created the workbook and the one opening the
workbook) have the add-in located under the exact same file path.
Here's what I did to duplicate the errors and get to a "corrected"
workbook (note I have alternative steps 3-5):
1. I turned off the add-in from menu option > Tools > Add-ins. This
should allow me to get the same error messages you are seeing, because
my add-in links will not be working automatically.
2. I opened SMF-Charts.XLS from the Yahoo Groups library
3A. Error message about links to other data sources -- I told it to
update the links
4A. Error message about links not being updatable -- I told it to continue
5A. I'm now in a copy of the spreadsheet filled with "#NAME?" values
in cells. This is to be expected, because none of the add-in
functions are available.
3B. Error message about links to other data sources -- this time I
told it "Don't Update".
4B. I'm now in a copy of the spreadsheet with the data the way the
spreadsheet was saved (no recalculation was done because I told it not
to update). However, everywhere I used an add-in formula, the formula
looks something like this:
'C:\Documents and Settings\Administrator\Application
Data\Microsoft\AddIns\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker\
,9546)
The file paths are the links that need to be updated.
5B. ...
6. I turn on the add-in from menu option > Tools > Add-ins. This
apparently automatically updates all of the links to the library
installed on the machine opening the workbook.
7. I immediately get the hourglass. However, status bar does NOT
indicate calculation is occurring -- but it is. After about 35
seconds, my charts appear. It is doing about 17 web page retrievals,
so I guess that's to be expected.
The former would have been the old version, the latter is 1.3b. To install 1.3b properly, the former should no longer be on the list and the latter should be checked.
The former would have been the old version, the latter is 1.3b. To install 1.3b properly, the former should no longer be on the list and the latter should be checked.
--- "rgaillard" <ron.gaillard@...> wrote:
>
> replys the word "Stock" in the block
When I enter:
=RCHGetElementNumber("Version")
I get this value in the cell:
Stock Market Functions add-in, Version 1.3b
So you must only be seeing the starting part of the message. Is there
something in the cell after the one you entered the formula in.
Which of these do you have in your list of add-ins (menu option >
Tools > Add-ins):
> RCH_Stock_Market_Functions
> Stock Market Functions Add-In
The former would have been the old version, the latter is 1.3b. To
install 1.3b properly, the former should no longer be on the list and
the latter should be checked.
replys the word "Stock" in the block
--- In smf_addin@yahoogroups.com, "Randy Harmelink" <rharmelink@...>
wrote:
>
> --- "Kermit W. Prather" <kermitp@> wrote:
> >
> > Not sure what I did wrong but when I tried to replace version 1.2
> > with 1.3B it does not appear to have added the add-in. It is not
> > list among the add-ins where it was previously.
> >
> > However, if I open the VB editor it shows a VBA project for
> > RCH_Stock_market_functions.xla
>
> I think I caused this error. When I rebuilt the add-in to reduce
the
> space down from 8MB to 3MB, I named it internally as "Stock Market
> Functions Add-In". Do you have that on your add-in list along with
> the RCH version? If so, it may be causing a conflict. You should
> toggle off the RCH_ version and toggle on the new one.
>
> You can test which version you are using with this formula:
>
> =RCHGetElementNumber("Version")
>
> Let me know...
>
--- "Kermit W. Prather" <kermitp@...> wrote:
>
> Not sure what I did wrong but when I tried to replace version 1.2
> with 1.3B it does not appear to have added the add-in. It is not
> list among the add-ins where it was previously.
>
> However, if I open the VB editor it shows a VBA project for
> RCH_Stock_market_functions.xla
I think I caused this error. When I rebuilt the add-in to reduce the
space down from 8MB to 3MB, I named it internally as "Stock Market
Functions Add-In". Do you have that on your add-in list along with
the RCH version? If so, it may be causing a conflict. You should
toggle off the RCH_ version and toggle on the new one.
You can test which version you are using with this formula:
=RCHGetElementNumber("Version")
Let me know...
Not sure what I did wrong but when I tried to replace version 1.2 with 1.3B it does not appear to have added the add-in. It is not list among the add-ins where it was previously.
However, if I open the VB editor it shows a VBA project for RCH_Stock_market_functions.xla
I added the XLA file into the Microsoft add-in folder and Excel sees it when I try to add it. When I select it click okay, I then get an already exit message
When I try to open the SMF-Charts.XLS I get a message:
I reply yes and then get
I hope the images came thru if not I have to resend the post.
I've created a new folder to the library, "Templates and Examples". The first upload there is file SMF-Charts.XLS. It does require version 1.3b of the add-in.
It is a template that allows flexible charting of various AdvFN financial statement items, including a number of calculated items like Pitroski F-score values and Magic Formula Investing values (i.e. earnings yield and return on capital).
Since this is the first upload there, any feedback would be appreciated:
-- Too much or too little detail/data? -- Not enough documentation? -- Is there a better way to document it? -- Is the example too complicated?
Here's and example of what the SMF-Charts results can look like:
----- Original Message ---- From: Randy Harmelink <rharmelink@...> To: smf_addin@yahoogroups.com Sent: Monday, July 3, 2006 11:20:46 PM Subject: [smf_addin] File Uploaded: SMF-Charts.XLS
I've created a new folder to the library, "Templates and Examples". The first upload there is file SMF-Charts.XLS. It does require version 1.3b of the add-in.
It is a template that allows flexible charting of various AdvFN financial statement items, including a number of calculated items like Pitroski F-score values and Magic Formula Investing values (i.e. earnings yield and return on capital).
Since this is the first upload there, any feedback would be appreciated:
-- Too much or too little detail/data? -- Not enough documentation? -- Is there a better way to document it? -- Is the example too complicated?
Here's and example of what the SMF-Charts results can look like:
I've created a new folder to the library, "Templates and Examples". The first upload there is file SMF-Charts.XLS. It does require version 1.3b of the add-in.
It is a template that allows flexible charting of various AdvFN financial statement items, including a number of calculated items like Pitroski F-score values and Magic Formula Investing values (i.e. earnings yield and return on capital).
Since this is the first upload there, any feedback would be appreciated:
-- Too much or too little detail/data? -- Not enough documentation? -- Is there a better way to document it? -- Is the example too complicated?
Here's and example of what the SMF-Charts results can look like:
--- "owmcclure" <olivermcclure@...> wrote:
>
> The first relates to the Yahoo History function. I tried to set-up
> the formula but i could only get it to return the last data item
> rather than a time series?
Any time you need a formula to return a range of data, you have to
array enter it (Ctrl-Shift-Enter instead of just Enter). A formula
can only return data into the range it has within its domain. If
you're using the default data elements, you'll need to use seven
columns of data. The number of rows will be dependent on how much
data you are returning.
> Also, the "w" time period seemed to be an "invalid request". I'll
> try and upload a copy of my spreadsheet to the files area.
Will be fixed in the next release. For some reason, I had a "y" in
the list of valid values instead of the "w".
> The second query relates to the universe of stocks addressed by the
> sources you use. Have you been able to determine what the universes
> are and whether any foreign stocks are include, excluding ADRs?
You'd have to go to the sources themselves to find out. All the
add-in does is extract date from the web pages they present. I've
even run into several situations where one source uses a different
ticker symbol for the same company.
Hi,
Just started using the add-in on the recommendation of George at Fat
Pitch Financials. Really like what i've seen so far. I have a copy
of queries...
The first relates to the Yahoo History function. I tried to set-up
the formula but i could only get it to return the last data item
rather than a time series? Also, the "w" time period seemed to be an
"invalid request". I'll try and upload a copy of my spreadsheet to
the files area.
The second query relates to the universe of stocks addressed by the
sources you use. Have you been able to determine what the universes
are and whether any foreign stocks are include, excluding ADRs?
many thanks
Oliver
Thanks for the great work. I'll be putting this version of the add-in
to use tomorrow.
George
--- In smf_addin@yahoogroups.com, "Randy Harmelink" <rharmelink@...>
wrote:
>
> Changes for release 1.3b
>
> -- Added a number of mutual fund data elements from Yahoo Finance
> -- Added 10 years of annual financial statement data from AdvFN
> -- Added 5 years of quarterly financial statement data from AdvFN
>
> I had to purge the add-in and recreate it from scratch to reduce the
> size from 8MB to 3MB. It shouldn't cause any problems, but I thought
> I should mention it just in case...
>
Changes for release 1.3b
-- Added a number of mutual fund data elements from Yahoo Finance
-- Added 10 years of annual financial statement data from AdvFN
-- Added 5 years of quarterly financial statement data from AdvFN
I had to purge the add-in and recreate it from scratch to reduce the
size from 8MB to 3MB. It shouldn't cause any problems, but I thought
I should mention it just in case...
--- In smf_addin@yahoogroups.com, "McGurck" <joe.harris@...> wrote:
>
> RCHGetElementNumber("IBM", 25)
> works correctly producing the last trade price for IBM.
>
> However,
> RCHGetElementNumber("SPY", 25)
> just produces an error.
The add-in can only retrieve what the data sources provide. Element
number 25 is the "Last Price" from the MSN Print Report for stocks.
However, SPY is an ETF, not a stock. There is no MSN Print Report
data page for ETFs.
If you're looking for current (i.e. delayed) prices, however, the
RCHGetYahooQuotes() function would probably be the best way to go.
Several example:
=RCHGetYahooQuotes("SPY","l1")
=RCHGetYahooQuotes("SPY,MMM","l1")
=RCHGetYahooQuotes("SPY,MMM","l1v")
The latter two must be array-entered, as they return a range of data
instead of just a single cell.
RCHGetElementNumber("IBM", 25)
works correctly producing the last trade price for IBM.
However,
RCHGetElementNumber("SPY", 25)
just produces an error.
-- Joe
I just uploaded version 1.3a of the add-in to the library.
The main purpose of this version was to rewrite how data elements are extracted off of the web pages. The rewrite should make it easier for individuals to define their own data source (i.e. web pages) and their own elements from data sources. In the near future, a document will be placed in the files area describing how to accomplish this task.
I'm hoping this will allow individuals to contribute to the breadth of the functionality of the add-in to make it more of a collaborative development. Hopefully, it won't get out of hand.
In testing the rewrite, several elements were found to be in error and were fixed. These elements were previously being stated as having a zero value, but are now being correctly extracted:
Also, somedate fields now contain more information than just the date:
Data Source
Element Name
Element Number
New Format
Old Format
YahooAE
Consensus EPS Trend -- Date -- FQ1
553
Current Qtr Jun-06
Jun-06
YahooAE
Consensus EPS Trend -- Date -- FQ2
554
Next Qtr Sep-06
Sep-06
YahooAE
Consensus EPS Trend -- Date -- FY1
555
Current Year Dec-06
Dec-06
YahooAE
Consensus EPS Trend -- Date -- FY2
556
Next Year Dec-07
Dec-07
YahooAE
Earnings Estimates -- Date -- FQ1
577
Current Qtr Jun-06
Jun-06
YahooAE
Earnings Estimates -- Date -- FQ2
578
Next Qtr Sep-06
Sep-06
YahooAE
Earnings Estimates -- Date -- FY1
579
Current Year Dec-06
Dec-06
YahooAE
Earnings Estimates -- Date -- FY2
580
Next Year Dec-07
Dec-07
YahooAE
EPS Revisions -- Date -- FQ1
653
Current Qtr Jun-06
Jun-06
YahooAE
EPS Revisions -- Date -- FQ2
654
Next Qtr Sep-06
Sep-06
YahooAE
EPS Revisions -- Date -- FY1
655
Current Year Dec-06
Dec-06
YahooAE
EPS Revisions -- Date -- FY2
656
Next Year Dec-07
Dec-07
YahooAE
Revenue Estimates -- Date -- FQ1
673
Current Qtr Jun-06
Jun-06
YahooAE
Revenue Estimates -- Date -- FQ2
674
Next Qtr Sep-06
Sep-06
YahooAE
Revenue Estimates -- Date -- FY1
675
Current Year Dec-06
Dec-06
YahooAE
Revenue Estimates -- Date -- FY2
676
Next Year Dec-07
Dec-07
Zacks
This Quarter -- Date
853
This Quarter06/2006
06/2006
Zacks
Next Quarter -- Date
859
This Year12/2006
12/2006
Zacks
This Year -- Date
865
Next Quarter09/2006
09/2006
Zacks
Next Year -- Date
871
Next Year12/2007
12/2007
Earnings
Quarterly Earnings -- Calendar Date -- 2
1295
24-Jan-06 BMO
24-Jan-06
Earnings
Quarterly Earnings -- Calendar Date -- 8
1325
23-Feb-05 AMC
23-Feb-05
The "Earnings" data source date changes could apply to any of their dates. I just showed two representative changes (BMO = Before Market Open; AMC = After Market Close).
--- "fatpitchfinancials" <george@...> wrote:
>
> By the way, I'd love to share my discovery of your Excel Add-In with
> my blog readers. Would you mind if I tell them about it and maybe
> include a link to this group?
No problem. The more people using it, the more likely errors are to
be found and eliminated.
All the better reason for having a separate Yahoo group. However, you
should also mention the XLTraders group -- if they are interested in
EXCEL as a stock market tool, there will be stuff over there they
would probably be interested in as well.
Thanks for the update. Keep up the great work!
By the way, I'd love to share my discovery of your Excel Add-In with
my blog readers. Would you mind if I tell them about it and maybe
include a link to this group?
Sincerely,
George
Fat Pitch Financials
http://www.fatpitchfinancials.com