Minggu, 04 Desember 2011

[smf_addin] Digest Number 2035

Messages In This Digest (12 Messages)

Messages

1a.

Correct Installation Place

Posted by: "paulperton1" PaulPerton1@aol.com   paulperton1

Sat Dec 3, 2011 8:40 am (PST)



Hi All,

Could somebody please tell me the correct installation place for the add-in.

I have downloaded a few files and some have the add in at C:\Program Files\SMF Add-in and Some at C:\SMF-Add-in

For best practice, Where should I place mine.

1b.

Re: Correct Installation Place

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Sat Dec 3, 2011 10:09 am (PST)



The add-in itself will work almost no matter where you put it.

What happens is that whenever a workbook is saved that has add-in formulas,
EXCEL saves a hard-coded link to the location of the add-in. And whenever a
workbook is opened that has add-in formulas, EXCEL checks each hard-coded
link to see if the add-in is in that location. If EXCEL finds the add-in in
that location, EXCEL strips off the hard-coded link and just leaves the
formula. However, if EXCEL doesn't find the add-in that location, EXCEL
leaves that hard-coded link on the formula and says the link is
"unresolved".

So, the only time you have an issue is when the opener of the workbook and
the saver of the workbook have the add-in in different locations. In that
case, you'll have all "unresolved" links that need to be manually fixed. I
generally just do a "find and replace all" on the hard-coded link that is
prefacing the formulas. In fact, I just use a macro that is in the add-in
called smfFixLinks, which I have assigned to a button on my toolbar.

The "C:\Program Files\SMF Add-in" location is the one I originally started
with. However, when I got my new computer, Vista wouldn't allow me to
update the add-in while it was in that location. So, I moved my location to
"C:\SMF-Add-in". That's why templates I create can be in either location,
because new ones are in the new location I use. I know the Old School Value
website uses a standard location of "C:\SMF". And some of the workbooks
uploaded by others use even other locations.

On Sat, Dec 3, 2011 at 9:40 AM, paulperton1 <PaulPerton1@aol.com> wrote:

>
> Could somebody please tell me the correct installation place for the
> add-in.
>
> I have downloaded a few files and some have the add in at C:\Program
> Files\SMF Add-in and Some at C:\SMF-Add-in
>
> For best practice, Where should I place mine.
>
2a.

Re: Ticker for Dow Jones Industrials?

Posted by: "flyingcircus98" circus317@comcast.net   flyingcircus98

Sat Dec 3, 2011 8:56 am (PST)



To confirm, I've been informed, at long last, by Yahoo "customer care" that they are no longer allowed to provide downloads of historical quotes of Dow Jones index data. ^DWC (total market), ^DWXRSN (Dow int RE) (known as $DWXRS on stockcharts).

Any recommendations on other providers indices on Yahoo?

FC

2b.

Re: Ticker for Dow Jones Industrials?

Posted by: "David Cerruti" david.cerruti@gmail.com   cerruticerruti

Sat Dec 3, 2011 9:19 am (PST)



Sorry, checked iShares, but they don't have an ETF for those indices.

On Sat, Dec 3, 2011 at 11:56 AM, flyingcircus98 <circus317@comcast.net>wrote:

> **
>
>
> To confirm, I've been informed, at long last, by Yahoo "customer care"
> that they are no longer allowed to provide downloads of historical quotes
> of Dow Jones index data. ^DWC (total market), ^DWXRSN (Dow int RE) (known
> as $DWXRS on stockcharts).
>
> Any recommendations on other providers indices on Yahoo?
>
> FC
>
>
>
2c.

Re: Ticker for Dow Jones Industrials?

Posted by: "Mike McQuaid" mikemcq802@yahoo.com   mikemcq802

Sat Dec 3, 2011 9:19 am (PST)



S&P 500?

^SP500
^SP500TR (total return)

http://finance.yahoo.com/lookup?s=^sp5

_____

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf
Of flyingcircus98
Sent: Saturday, December 03, 2011 11:56 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: Ticker for Dow Jones Industrials?

To confirm, I've been informed, at long last, by Yahoo "customer care" that
they are no longer allowed to provide downloads of historical quotes of Dow
Jones index data. ^DWC (total market), ^DWXRSN (Dow int RE) (known as $DWXRS
on stockcharts).

Any recommendations on other providers indices on Yahoo?

FC

2d.

Re: Ticker for Dow Jones Industrials?

Posted by: "adam pilz" adampilz.2326@gmail.com   pilzadam

Sat Dec 3, 2011 9:29 am (PST)



Not sure if this helps but FRED has the industrial, composite, utility and
transportation averages.

http://research.stlouisfed.org/fred2/search?st=dow+jones

AP

On Sat, Dec 3, 2011 at 12:19 PM, David Cerruti <david.cerruti@gmail.com>wrote:

> **
>
>
> Sorry, checked iShares, but they don't have an ETF for those indices.
>
>
> On Sat, Dec 3, 2011 at 11:56 AM, flyingcircus98 <circus317@comcast.net>wrote:
>
>> **
>>
>>
>> To confirm, I've been informed, at long last, by Yahoo "customer care"
>> that they are no longer allowed to provide downloads of historical quotes
>> of Dow Jones index data. ^DWC (total market), ^DWXRSN (Dow int RE) (known
>> as $DWXRS on stockcharts).
>>
>> Any recommendations on other providers indices on Yahoo?
>>
>> FC
>>
>>
>
>
3a.

Re: Distribute Spreadsheet with SMF Add-In

Posted by: "bryankam10" bryan.a.kam@gmail.com   bryankam10

Sat Dec 3, 2011 3:41 pm (PST)



Hi Randy -

I was wondering, the macro you have written in the below post to replace the location add-in, does that work only in a single worksheet, or the whole workbook? I've written a similar macro to run when a workbook automatically opens, but for some reason when the macro code executes, it only does find and replace for the one worksheet and not all the worksheets in the workbook. Please let me know if you've encountered this problem, and if you've managed to find a way to fix it.

Thanks,
Bryan

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Unfortunately, this is a common situation when workbooks are shared. It
> occurs because when you save your EXCEL file,MicroSoft decided to save the
> hard-coded location of the add-in in the cell formula, so that EXCEL knows
> the function comes from an external source. Then, when the file is opened,
> EXCEL looks for that external source. If it finds it, it resolves the link
> and removes the hard-coded location -- all is well. However, if it doesn't
> find the external source, EXCEL marks it as an unresolved link, which makes
> the cell non-functional until the link error is resolved.
>
> As you note, a manual change, cell by cell, does fix the problem. If you
> check EXCEL's menus, you find a way to > Edit > Links, which is another way
> to solve the issue for an entire workbook. However, I've found it work
> differently on different versions of EXCEL and was not a reliable way to fix
> link errors.
>
> The quickest and most reliable way I've found to fix the issue is to do a
> "Find and Replace All" (keyboard shortcut Ctrl+H), replacing all hard-coded
> locations:
>
> 'C:\User Name\Application
> Data\Microsoft\AddIns\RCH_Stock_Market_Functions.xla'!
>
> ...with nothing. Once that is done and the file is saved, that person's
> hard-coded add-in location will be saved with the formulas, so that the next
> time they open the file on their computer, the links will be resolved
> automatically.
>
> It's one reason I tried to go with a standard folder for the location of the
> add-in in the installation instructions. If everyone used the same location,
> this would never be an issue.
>
> Unfortunately, even I had to violate that, because Vista wouldn't let me
> update the add-in's XLA file while it was located in a subdirectory of the
> "Program Files" folder. The "Find and Replace" process could be automated --
> I have a macro I manually run to do it as needed when I open files that used
> to use my old location:
>
> Public Sub smfFixLinks()
> ActiveSheet.Cells.Replace _
> What:="'C:\Program Files\SMF
> Add-In\RCH_Stock_Market_Functions.xla'!", _
> Replacement:="", _
> LookAt:=xlPart, _
> SearchOrder:=xlByRows, _
> MatchCase:=False, _
> SearchFormat:=False, _
> ReplaceFormat:=False
> End Sub
>
>
> On Wed, Aug 25, 2010 at 1:45 PM, Ping Zhu <p_zhu00@...> wrote:
>
> > This might be a pretty dumb question, but I've got an issue when I tried
> > to copy my working Excel model to my colleagues' computers. I installed
> > RCH_Stock_Market_Functions.xla as add-ins in my computer, and was able to
> > develop a model by placing the formulas in some cells directly (not in VBA
> > code). For example:
> >
> > In cell A1, I placed "=RCHGetElementNumber("YUM", 13862)", and I correctly
> > got the result as "YUM! BRANDS INC".
> >
> > Unfortunately, when I copy this same Excel file to another computer (with
> > SMF Add-Ins installed), the formula in cell A1 was automatically changed to
> > something like:
> >
> > ='C:\User Name\Application
> > Data\Microsoft\AddIns\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber("YUM",
> > 13862)
> >
> > As a result, this cell no longer returns a valid result. If I manually
> > remove the path, it'll work fine in the new computer. However, this is very
> > tedious as I have many RCHGetElementNumber calls in lots of cells.
> >
> > I suspect I'm not installing the Excel add-in correctly? Is there a way so
> > that I can freely copy my finished models to other computers in the future,
> > without encountering the same problem?
> >
>

3b.

Re: Distribute Spreadsheet with SMF Add-In

Posted by: "dguillett1" dguillett1@gmail.com   donaldb36

Sat Dec 3, 2011 3:59 pm (PST)



In case Randy is not available.
You did not post your code but if you want to do a find/replace on each worksheet, you must write your macro accordingly

for each ws in worksheets
ws. do your thing
next ws

Don Guillett
SalesAid Software
dguillett1@gmail.com

From: bryankam10
Sent: Saturday, December 03, 2011 5:41 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: Distribute Spreadsheet with SMF Add-In

Hi Randy -

I was wondering, the macro you have written in the below post to replace the location add-in, does that work only in a single worksheet, or the whole workbook? I've written a similar macro to run when a workbook automatically opens, but for some reason when the macro code executes, it only does find and replace for the one worksheet and not all the worksheets in the workbook. Please let me know if you've encountered this problem, and if you've managed to find a way to fix it.

Thanks,
Bryan

--- In mailto:smf_addin%40yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Unfortunately, this is a common situation when workbooks are shared. It
> occurs because when you save your EXCEL file,MicroSoft decided to save the
> hard-coded location of the add-in in the cell formula, so that EXCEL knows
> the function comes from an external source. Then, when the file is opened,
> EXCEL looks for that external source. If it finds it, it resolves the link
> and removes the hard-coded location -- all is well. However, if it doesn't
> find the external source, EXCEL marks it as an unresolved link, which makes
> the cell non-functional until the link error is resolved.
>
> As you note, a manual change, cell by cell, does fix the problem. If you
> check EXCEL's menus, you find a way to > Edit > Links, which is another way
> to solve the issue for an entire workbook. However, I've found it work
> differently on different versions of EXCEL and was not a reliable way to fix
> link errors.
>
> The quickest and most reliable way I've found to fix the issue is to do a
> "Find and Replace All" (keyboard shortcut Ctrl+H), replacing all hard-coded
> locations:
>
> 'C:\User Name\Application
> Data\Microsoft\AddIns\RCH_Stock_Market_Functions.xla'!
>
> ...with nothing. Once that is done and the file is saved, that person's
> hard-coded add-in location will be saved with the formulas, so that the next
> time they open the file on their computer, the links will be resolved
> automatically.
>
> It's one reason I tried to go with a standard folder for the location of the
> add-in in the installation instructions. If everyone used the same location,
> this would never be an issue.
>
> Unfortunately, even I had to violate that, because Vista wouldn't let me
> update the add-in's XLA file while it was located in a subdirectory of the
> "Program Files" folder. The "Find and Replace" process could be automated --
> I have a macro I manually run to do it as needed when I open files that used
> to use my old location:
>
> Public Sub smfFixLinks()
> ActiveSheet.Cells.Replace _
> What:="'C:\Program Files\SMF
> Add-In\RCH_Stock_Market_Functions.xla'!", _
> Replacement:="", _
> LookAt:=xlPart, _
> SearchOrder:=xlByRows, _
> MatchCase:=False, _
> SearchFormat:=False, _
> ReplaceFormat:=False
> End Sub
>
>
> On Wed, Aug 25, 2010 at 1:45 PM, Ping Zhu <p_zhu00@...> wrote:
>
> > This might be a pretty dumb question, but I've got an issue when I tried
> > to copy my working Excel model to my colleagues' computers. I installed
> > RCH_Stock_Market_Functions.xla as add-ins in my computer, and was able to
> > develop a model by placing the formulas in some cells directly (not in VBA
> > code). For example:
> >
> > In cell A1, I placed "=RCHGetElementNumber("YUM", 13862)", and I correctly
> > got the result as "YUM! BRANDS INC".
> >
> > Unfortunately, when I copy this same Excel file to another computer (with
> > SMF Add-Ins installed), the formula in cell A1 was automatically changed to
> > something like:
> >
> > ='C:\User Name\Application
> > Data\Microsoft\AddIns\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber("YUM",
> > 13862)
> >
> > As a result, this cell no longer returns a valid result. If I manually
> > remove the path, it'll work fine in the new computer. However, this is very
> > tedious as I have many RCHGetElementNumber calls in lots of cells.
> >
> > I suspect I'm not installing the Excel add-in correctly? Is there a way so
> > that I can freely copy my finished models to other computers in the future,
> > without encountering the same problem?
> >
>

3c.

Re: Distribute Spreadsheet with SMF Add-In

Posted by: "Bryan Kam" bryan.a.kam@gmail.com   bryankam10

Sat Dec 3, 2011 4:11 pm (PST)



Got it, thanks alot!

On Sat, Dec 3, 2011 at 6:59 PM, dguillett1 <dguillett1@gmail.com> wrote:

> **
>
>
> In case Randy is not available.
> You did not post your code but if you want to do a find/replace on each
> worksheet, you must write your macro accordingly
>
> for each ws in worksheets
> ws. do your thing
> next ws
>
>
> Don Guillett
> SalesAid Software
> dguillett1@gmail.com
>
> *From:* bryankam10 <bryan.a.kam@gmail.com>
> *Sent:* Saturday, December 03, 2011 5:41 PM
> *To:* smf_addin@yahoogroups.com
> *Subject:* [smf_addin] Re: Distribute Spreadsheet with SMF Add-In
>
>
>
> Hi Randy -
>
> I was wondering, the macro you have written in the below post to replace
> the location add-in, does that work only in a single worksheet, or the
> whole workbook? I've written a similar macro to run when a workbook
> automatically opens, but for some reason when the macro code executes, it
> only does find and replace for the one worksheet and not all the worksheets
> in the workbook. Please let me know if you've encountered this problem, and
> if you've managed to find a way to fix it.
>
> Thanks,
> Bryan
>
> --- In mailto:smf_addin%40yahoogroups.com <smf_addin%40yahoogroups.com>,
> Randy Harmelink <rharmelink@...> wrote:
> >
> > Unfortunately, this is a common situation when workbooks are shared. It
> > occurs because when you save your EXCEL file,MicroSoft decided to save
> the
> > hard-coded location of the add-in in the cell formula, so that EXCEL
> knows
> > the function comes from an external source. Then, when the file is
> opened,
> > EXCEL looks for that external source. If it finds it, it resolves the
> link
> > and removes the hard-coded location -- all is well. However, if it
> doesn't
> > find the external source, EXCEL marks it as an unresolved link, which
> makes
> > the cell non-functional until the link error is resolved.
> >
> > As you note, a manual change, cell by cell, does fix the problem. If you
> > check EXCEL's menus, you find a way to > Edit > Links, which is another
> way
> > to solve the issue for an entire workbook. However, I've found it work
> > differently on different versions of EXCEL and was not a reliable way to
> fix
> > link errors.
> >
> > The quickest and most reliable way I've found to fix the issue is to do a
> > "Find and Replace All" (keyboard shortcut Ctrl+H), replacing all
> hard-coded
> > locations:
> >
> > 'C:\User Name\Application
> > Data\Microsoft\AddIns\RCH_Stock_Market_Functions.xla'!
> >
> > ...with nothing. Once that is done and the file is saved, that person's
> > hard-coded add-in location will be saved with the formulas, so that the
> next
> > time they open the file on their computer, the links will be resolved
> > automatically.
> >
> > It's one reason I tried to go with a standard folder for the location of
> the
> > add-in in the installation instructions. If everyone used the same
> location,
> > this would never be an issue.
> >
> > Unfortunately, even I had to violate that, because Vista wouldn't let me
> > update the add-in's XLA file while it was located in a subdirectory of
> the
> > "Program Files" folder. The "Find and Replace" process could be
> automated --
> > I have a macro I manually run to do it as needed when I open files that
> used
> > to use my old location:
> >
> > Public Sub smfFixLinks()
> > ActiveSheet.Cells.Replace _
> > What:="'C:\Program Files\SMF
> > Add-In\RCH_Stock_Market_Functions.xla'!", _
> > Replacement:="", _
> > LookAt:=xlPart, _
> > SearchOrder:=xlByRows, _
> > MatchCase:=False, _
> > SearchFormat:=False, _
> > ReplaceFormat:=False
> > End Sub
> >
> >
> > On Wed, Aug 25, 2010 at 1:45 PM, Ping Zhu <p_zhu00@...> wrote:
> >
> > > This might be a pretty dumb question, but I've got an issue when I
> tried
> > > to copy my working Excel model to my colleagues' computers. I installed
> > > RCH_Stock_Market_Functions.xla as add-ins in my computer, and was able
> to
> > > develop a model by placing the formulas in some cells directly (not in
> VBA
> > > code). For example:
> > >
> > > In cell A1, I placed "=RCHGetElementNumber("YUM", 13862)", and I
> correctly
> > > got the result as "YUM! BRANDS INC".
> > >
> > > Unfortunately, when I copy this same Excel file to another computer
> (with
> > > SMF Add-Ins installed), the formula in cell A1 was automatically
> changed to
> > > something like:
> > >
> > > ='C:\User Name\Application
> > >
> Data\Microsoft\AddIns\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber("YUM",
> > > 13862)
> > >
> > > As a result, this cell no longer returns a valid result. If I manually
> > > remove the path, it'll work fine in the new computer. However, this is
> very
> > > tedious as I have many RCHGetElementNumber calls in lots of cells.
> > >
> > > I suspect I'm not installing the Excel add-in correctly? Is there a
> way so
> > > that I can freely copy my finished models to other computers in the
> future,
> > > without encountering the same problem?
> > >
> >
>
>
>
3d.

Re: Distribute Spreadsheet with SMF Add-In

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Sat Dec 3, 2011 4:17 pm (PST)



I just have it run for a single worksheet. Primarily because that's pretty
much all I ever do.

It's easy to change though -- instead of:

ActiveSheet.Cells.Replace ...

Just do something like:

ActiveWorkbook.Sheets.Select
Selection.Cells.Replace ...

The first statement just selects all sheets, and does the replacement on
that selection instead of just the active sheet...

On Sat, Dec 3, 2011 at 4:41 PM, bryankam10 <bryan.a.kam@gmail.com> wrote:

>
> I was wondering, the macro you have written in the below post to replace
> the location add-in, does that work only in a single worksheet, or the
> whole workbook? I've written a similar macro to run when a workbook
> automatically opens, but for some reason when the macro code executes, it
> only does find and replace for the one worksheet and not all the worksheets
> in the workbook. Please let me know if you've encountered this problem, and
> if you've managed to find a way to fix it.
>
> Thanks,
> Bryan
>
> --- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
> >
> > Unfortunately, this is a common situation when workbooks are shared. It
> > occurs because when you save your EXCEL file,MicroSoft decided to save
> the
> > hard-coded location of the add-in in the cell formula, so that EXCEL
> knows
> > the function comes from an external source. Then, when the file is
> opened,
> > EXCEL looks for that external source. If it finds it, it resolves the
> link
> > and removes the hard-coded location -- all is well. However, if it
> doesn't
> > find the external source, EXCEL marks it as an unresolved link, which
> makes
> > the cell non-functional until the link error is resolved.
> >
> > As you note, a manual change, cell by cell, does fix the problem. If you
> > check EXCEL's menus, you find a way to > Edit > Links, which is another
> way
> > to solve the issue for an entire workbook. However, I've found it work
> > differently on different versions of EXCEL and was not a reliable way to
> fix
> > link errors.
> >
> > The quickest and most reliable way I've found to fix the issue is to do a
> > "Find and Replace All" (keyboard shortcut Ctrl+H), replacing all
> hard-coded
> > locations:
> >
> > 'C:\User Name\Application
> > Data\Microsoft\AddIns\RCH_Stock_Market_Functions.xla'!
> >
> > ...with nothing. Once that is done and the file is saved, that person's
> > hard-coded add-in location will be saved with the formulas, so that the
> next
> > time they open the file on their computer, the links will be resolved
> > automatically.
> >
> > It's one reason I tried to go with a standard folder for the location of
> the
> > add-in in the installation instructions. If everyone used the same
> location,
> > this would never be an issue.
> >
> > Unfortunately, even I had to violate that, because Vista wouldn't let me
> > update the add-in's XLA file while it was located in a subdirectory of
> the
> > "Program Files" folder. The "Find and Replace" process could be
> automated --
> > I have a macro I manually run to do it as needed when I open files that
> used
> > to use my old location:
> >
> > Public Sub smfFixLinks()
> > ActiveSheet.Cells.Replace _
> > What:="'C:\Program Files\SMF
> > Add-In\RCH_Stock_Market_Functions.xla'!", _
> > Replacement:="", _
> > LookAt:=xlPart, _
> > SearchOrder:=xlByRows, _
> > MatchCase:=False, _
> > SearchFormat:=False, _
>
4a.

RCHGetHTMLTable

Posted by: "Ameri Sky" amerisky@yahoo.com   amerisky

Sat Dec 3, 2011 10:57 pm (PST)



Hi Randy

I tried to use "=RCHGetHTMLTable(
"http://www.finviz.com/insidertrading.ashx",
"Relationship", -1, "", 1)" to get the whole table of
insider trading from Finviz.com. However, the Excel was hanging there. Could
you please tell me how to solve this problem?
 
Thanks

Amerisky
4b.

Re: RCHGetHTMLTable

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Sat Dec 3, 2011 11:41 pm (PST)



It's because they have script coded within the HTML table tags. The
RCHGetHTMLTable() parser is just a simple parsing routine, so it can't
handle all possible permutations of HTML coding.

However, you can do individual RCHGetTableCell() functions to grab the data.

I tried to do an EXCEL Web Query, thinking that might be a good workaround,
but apparently the HTML table tags also confuse it, so it doesn't select
any data to put into a worksheet either.

On Sat, Dec 3, 2011 at 11:57 PM, Ameri Sky <amerisky@yahoo.com> wrote:

>
> I tried to use "=RCHGetHTMLTable( "
> http://www.finviz.com/insidertrading.ashx", "Relationship", -1, "", 1)"
> to get the whole table of insider trading from Finviz.com. However, the
> Excel was hanging there. Could you please tell me how to solve this problem?
>
>
Recent Activity
Visit Your Group
Ads on Yahoo!

Learn more now.

Reach customers

searching for you.

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! News

Get it all here

Breaking news to

entertainment news

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

Tidak ada komentar:

Posting Komentar