15 New Messages
Digest #2688
Messages
Tue Jul 9, 2013 8:58 am (PDT) . Posted by:
"racecar00" racecar00
Excel accepts the syntax but the add-in pulls in "Error". For example, the ticker VFINX should pull in -0.02 as of today for the 1-Month (row) and "Total Return %" (column).
At this point I am trying to diagnose if it is me or broader error.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Try:
>
> RCHGetTableCell("
> http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol= "&D5,
> 1,"Trailing Total Returns","1-Month")
>
> On Mon, Jul 8, 2013 at 5:41 PM, racecar00 <agelhausen@...> wrote:
>
> >
> > Could you help me with the syntax for obtaining daily performance figures
> > from the Morningstar "printreport" URL
> >
> > Example: http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=
> >
> > I am trying to pull data from the table subheaded "Trailing Total
> > Returns." I would like to pull data ("Total Return %") for 1-month,
> > 3-month, etc...
> >
> > I have tried several combinations with no luck.
> >
> > =RCHGetTableCell("
> > http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol= "&$B65,1,">1-Month",">Trailing
> > Total Returns")
> >
>
At this point I am trying to diagnose if it is me or broader error.
--- In smf_addin@yahoogrou
>
> Try:
>
> RCHGetTableCell(
> http://quicktake.
> 1,"Trailing Total Returns"
>
> On Mon, Jul 8, 2013 at 5:41 PM, racecar00 <agelhausen@
>
> >
> > Could you help me with the syntax for obtaining daily performance figures
> > from the Morningstar "printreport&q
> >
> > Example: http://quicktake.
> >
> > I am trying to pull data from the table subheaded "Trailing Total
> > Returns." I would like to pull data ("Total Return %") for 1-month,
> > 3-month, etc...
> >
> > I have tried several combinations with no luck.
> >
> > =RCHGetTableCell(
> > http://quicktake.
> > Total Returns"
> >
>
Tue Jul 9, 2013 9:05 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I get -0.02.
MorningStar is one of those sites that does a redirection on your first
visit to the site each day. So you need to visit the site with the EXCEL
Web Query dialog or IE at the start of a day's sessions. That allows
MorningStar to create a cookie that says they've done the redirection for
that day (usually for some type of advertising). The add-in just retrieves
the source code of the web page, so it doesn't actually run any JavaScript
code that would update cookies.
I also got "Error" when I first entered the formula this morning. But I
just opened up the Web Query dialog (alt+d+d+w) and visited morningstar.com,
and then canceled out. I then ran the smfForceRecalculation macro to force
the add-in to get a fresh copy of the web page. Voila. -0.02 replaced
"Error".
Most people never notice this, because they've visiting MorningStar in IE
prior to using EXCEL and the add-in. So the process has been done
automatically for them. I've always had to do it this way, because I use
FireFox as a browser, and it has a completely separate set of cookies that
EXCEL isn't aware of.
On Tue, Jul 9, 2013 at 8:58 AM, racecar00 <agelhausen@gmail.com > wrote:
> Excel accepts the syntax but the add-in pulls in "Error". For example,
> the ticker VFINX should pull in -0.02 as of today for the 1-Month (row) and
> "Total Return %" (column).
>
> At this point I am trying to diagnose if it is me or broader error.
>
MorningStar is one of those sites that does a redirection on your first
visit to the site each day. So you need to visit the site with the EXCEL
Web Query dialog or IE at the start of a day's sessions. That allows
MorningStar to create a cookie that says they've done the redirection for
that day (usually for some type of advertising)
the source code of the web page, so it doesn't actually run any JavaScript
code that would update cookies.
I also got "Error" when I first entered the formula this morning. But I
just opened up the Web Query dialog (alt+d+d+w) and visited morningstar.
and then canceled out. I then ran the smfForceRecalculati
the add-in to get a fresh copy of the web page. Voila. -0.02 replaced
"Error"
Most people never notice this, because they've visiting MorningStar in IE
prior to using EXCEL and the add-in. So the process has been done
automatically for them. I've always had to do it this way, because I use
FireFox as a browser, and it has a completely separate set of cookies that
EXCEL isn't aware of.
On Tue, Jul 9, 2013 at 8:58 AM, racecar00 <agelhausen@gmail.
> Excel accepts the syntax but the add-in pulls in "Error"
> the ticker VFINX should pull in -0.02 as of today for the 1-Month (row) and
> "Total Return %" (column).
>
> At this point I am trying to diagnose if it is me or broader error.
>
Tue Jul 9, 2013 9:50 am (PDT) . Posted by:
"racecar00" racecar00
Perfect - merci.
Applying the same process to an ETF requires a slight modification to the "printreport" URL. From my search, I found it is this:
"http://quicktake.morningstar.com/etfnet/PrintReport.aspx?Symbol=ticker "
When I try this link and use any number of tickers (AGG, IWO, SPY, etc...) I receive an error from Morningstar. Has this URL been modified by Morningstar or is it truly an error?
Thanks again.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> I get -0.02.
>
> MorningStar is one of those sites that does a redirection on your first
> visit to the site each day. So you need to visit the site with the EXCEL
> Web Query dialog or IE at the start of a day's sessions. That allows
> MorningStar to create a cookie that says they've done the redirection for
> that day (usually for some type of advertising). The add-in just retrieves
> the source code of the web page, so it doesn't actually run any JavaScript
> code that would update cookies.
>
> I also got "Error" when I first entered the formula this morning. But I
> just opened up the Web Query dialog (alt+d+d+w) and visited morningstar.com,
> and then canceled out. I then ran the smfForceRecalculation macro to force
> the add-in to get a fresh copy of the web page. Voila. -0.02 replaced
> "Error".
>
> Most people never notice this, because they've visiting MorningStar in IE
> prior to using EXCEL and the add-in. So the process has been done
> automatically for them. I've always had to do it this way, because I use
> FireFox as a browser, and it has a completely separate set of cookies that
> EXCEL isn't aware of.
>
> On Tue, Jul 9, 2013 at 8:58 AM, racecar00 <agelhausen@...> wrote:
>
> > Excel accepts the syntax but the add-in pulls in "Error". For example,
> > the ticker VFINX should pull in -0.02 as of today for the 1-Month (row) and
> > "Total Return %" (column).
> >
> > At this point I am trying to diagnose if it is me or broader error.
> >
>
Applying the same process to an ETF requires a slight modification to the "printreport&q
"http://quicktake.
When I try this link and use any number of tickers (AGG, IWO, SPY, etc...) I receive an error from Morningstar. Has this URL been modified by Morningstar or is it truly an error?
Thanks again.
--- In smf_addin@yahoogrou
>
> I get -0.02.
>
> MorningStar is one of those sites that does a redirection on your first
> visit to the site each day. So you need to visit the site with the EXCEL
> Web Query dialog or IE at the start of a day's sessions. That allows
> MorningStar to create a cookie that says they've done the redirection for
> that day (usually for some type of advertising)
> the source code of the web page, so it doesn't actually run any JavaScript
> code that would update cookies.
>
> I also got "Error" when I first entered the formula this morning. But I
> just opened up the Web Query dialog (alt+d+d+w) and visited morningstar.
> and then canceled out. I then ran the smfForceRecalculati
> the add-in to get a fresh copy of the web page. Voila. -0.02 replaced
> "Error"
>
> Most people never notice this, because they've visiting MorningStar in IE
> prior to using EXCEL and the add-in. So the process has been done
> automatically for them. I've always had to do it this way, because I use
> FireFox as a browser, and it has a completely separate set of cookies that
> EXCEL isn't aware of.
>
> On Tue, Jul 9, 2013 at 8:58 AM, racecar00 <agelhausen@
>
> > Excel accepts the syntax but the add-in pulls in "Error"
> > the ticker VFINX should pull in -0.02 as of today for the 1-Month (row) and
> > "Total Return %" (column).
> >
> > At this point I am trying to diagnose if it is me or broader error.
> >
>
Tue Jul 9, 2013 12:12 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Because of them converting everything over to dynamic web page
presentations, the actual data is stored elsewhere and retrieved. It looks
like a lot of print pages have now been obsoleted. If I'm reading my
JavaScript code correctly on the dynamically loaded web page, this looks
like the source page URL for the performance data:
http://performance.morningstar.com/Performance/cef/trailing-total-returns.action?t=SPY
On Tue, Jul 9, 2013 at 9:50 AM, racecar00 <agelhausen@gmail.com > wrote:
>
> Applying the same process to an ETF requires a slight modification to the
> "printreport" URL. From my search, I found it is this:
>
> "http://quicktake.morningstar.com/etfnet/PrintReport.aspx?Symbol=ticker "
>
> When I try this link and use any number of tickers (AGG, IWO, SPY, etc...)
> I receive an error from Morningstar. Has this URL been modified by
> Morningstar or is it truly an error?
>
presentations, the actual data is stored elsewhere and retrieved. It looks
like a lot of print pages have now been obsoleted. If I'm reading my
JavaScript code correctly on the dynamically loaded web page, this looks
like the source page URL for the performance data:
http://performance.
On Tue, Jul 9, 2013 at 9:50 AM, racecar00 <agelhausen@gmail.
>
> Applying the same process to an ETF requires a slight modification to the
> "printreport&q
>
> "http://quicktake.
>
> When I try this link and use any number of tickers (AGG, IWO, SPY, etc...)
> I receive an error from Morningstar. Has this URL been modified by
> Morningstar or is it truly an error?
>
Tue Jul 9, 2013 2:25 pm (PDT) . Posted by:
"racecar00" racecar00
Thanks again!
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Because of them converting everything over to dynamic web page
> presentations, the actual data is stored elsewhere and retrieved. It looks
> like a lot of print pages have now been obsoleted. If I'm reading my
> JavaScript code correctly on the dynamically loaded web page, this looks
> like the source page URL for the performance data:
>
> http://performance.morningstar.com/Performance/cef/trailing-total-returns.action?t=SPY
>
> On Tue, Jul 9, 2013 at 9:50 AM, racecar00 <agelhausen@...> wrote:
>
> >
> > Applying the same process to an ETF requires a slight modification to the
> > "printreport" URL. From my search, I found it is this:
> >
> > "http://quicktake.morningstar.com/etfnet/PrintReport.aspx?Symbol=ticker "
> >
> > When I try this link and use any number of tickers (AGG, IWO, SPY, etc...)
> > I receive an error from Morningstar. Has this URL been modified by
> > Morningstar or is it truly an error?
> >
>
--- In smf_addin@yahoogrou
>
> Because of them converting everything over to dynamic web page
> presentations, the actual data is stored elsewhere and retrieved. It looks
> like a lot of print pages have now been obsoleted. If I'm reading my
> JavaScript code correctly on the dynamically loaded web page, this looks
> like the source page URL for the performance data:
>
> http://performance.
>
> On Tue, Jul 9, 2013 at 9:50 AM, racecar00 <agelhausen@
>
> >
> > Applying the same process to an ETF requires a slight modification to the
> > "printreport&q
> >
> > "http://quicktake.
> >
> > When I try this link and use any number of tickers (AGG, IWO, SPY, etc...)
> > I receive an error from Morningstar. Has this URL been modified by
> > Morningstar or is it truly an error?
> >
>
Tue Jul 9, 2013 2:47 pm (PDT) . Posted by:
"racecar00" racecar00
Last one. This string covers performance for mutual funds, ETFs, and now I want to add stocks. Here is the formula I am using:
=RCHGetTableCell("http://performance.morningstar.com/Performance/stock/trailing-total-returns.action?t= "&$B97,3,"Total Return %","ticker")
The unique part of this link is that the row header is dynamic and not static, that is, it changes from a fixed name like "price" to the ticker. I know I can concatenate the last expression with a cell reference (e.g. $B97 in place of "ticker") but so far I have been unsuccessful. Example:
=RCHGetTableCell("http://performance.morningstar.com/Performance/stock/trailing-total-returns.action?t= "&$B97,3,"Total Return %"&$B97)
Thanks
--- In smf_addin@yahoogroups.com , "racecar00" <agelhausen@...> wrote:
>
> Thanks again!
>
> --- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@> wrote:
> >
> > Because of them converting everything over to dynamic web page
> > presentations, the actual data is stored elsewhere and retrieved. It looks
> > like a lot of print pages have now been obsoleted. If I'm reading my
> > JavaScript code correctly on the dynamically loaded web page, this looks
> > like the source page URL for the performance data:
> >
> > http://performance.morningstar.com/Performance/cef/trailing-total-returns.action?t=SPY
> >
> > On Tue, Jul 9, 2013 at 9:50 AM, racecar00 <agelhausen@> wrote:
> >
> > >
> > > Applying the same process to an ETF requires a slight modification to the
> > > "printreport" URL. From my search, I found it is this:
> > >
> > > "http://quicktake.morningstar.com/etfnet/PrintReport.aspx?Symbol=ticker "
> > >
> > > When I try this link and use any number of tickers (AGG, IWO, SPY, etc...)
> > > I receive an error from Morningstar. Has this URL been modified by
> > > Morningstar or is it truly an error?
> > >
> >
>
=RCHGetTableCell(
The unique part of this link is that the row header is dynamic and not static, that is, it changes from a fixed name like "price" to the ticker. I know I can concatenate the last expression with a cell reference (e.g. $B97 in place of "ticker"
=RCHGetTableCell(
Thanks
--- In smf_addin@yahoogrou
>
> Thanks again!
>
> --- In smf_addin@yahoogrou
> >
> > Because of them converting everything over to dynamic web page
> > presentations, the actual data is stored elsewhere and retrieved. It looks
> > like a lot of print pages have now been obsoleted. If I'm reading my
> > JavaScript code correctly on the dynamically loaded web page, this looks
> > like the source page URL for the performance data:
> >
> > http://performance.
> >
> > On Tue, Jul 9, 2013 at 9:50 AM, racecar00 <agelhausen@
> >
> > >
> > > Applying the same process to an ETF requires a slight modification to the
> > > "printreport&q
> > >
> > > "http://quicktake.
> > >
> > > When I try this link and use any number of tickers (AGG, IWO, SPY, etc...)
> > > I receive an error from Morningstar. Has this URL been modified by
> > > Morningstar or is it truly an error?
> > >
> >
>
Tue Jul 9, 2013 9:40 am (PDT) . Posted by:
"PeteA" option2z
Subu,
Two thoughts:
1. Please check again that the References are checked:
2. Notice the "HoadleOptionAddin" IF you have not searched through and
replaced it, that might be a cause. As a long shot, you could enable the
references I show, for those are the ones in my TtP-S system (01 Module)
that
I use and mine works well.
Contact me by email, and I'll see if there is anything else I can do to
help.
Pete A
<http://www.sundognorth.com/TtP-S/ >
Two thoughts:
1. Please check again that the References are checked:
2. Notice the "HoadleOptionA
replaced it, that might be a cause. As a long shot, you could enable the
references I show, for those are the ones in my TtP-S system (01 Module)
that
I use and mine works well.
Contact me by email, and I'll see if there is anything else I can do to
help.
Pete A
<http://www.sundogno
Tue Jul 9, 2013 9:44 am (PDT) . Posted by:
"PeteA" option2z
RE: Randy's "
Add-in are loaded when EXCEL is opened. But the EXCEL and VBA environments
are separate as far as the add-in file usage goes. Neither knows or cares
about the other.
It could be that pete's XL sheet had the add-in in a different location
when it was saved, so that is where it is looking for the library and not
finding it. So you get the compile error, because the library doesn't
exist? It doesn't care that it can be found somewhere else, because it's
not going to go looking for it other than where it was told it would be."
Randy, I believe that is corrected when you do what I suggested in another
post:
From VBA Menu, Tools/References and make sure that the RCH. is checked.
Right?
Thanks,
Pete A
<http://www.sundognorth.com/TtP-S/ >
Add-in are loaded when EXCEL is opened. But the EXCEL and VBA environments
are separate as far as the add-in file usage goes. Neither knows or cares
about the other.
It could be that pete's XL sheet had the add-in in a different location
when it was saved, so that is where it is looking for the library and not
finding it. So you get the compile error, because the library doesn't
exist? It doesn't care that it can be found somewhere else, because it's
not going to go looking for it other than where it was told it would be."
Randy, I believe that is corrected when you do what I suggested in another
post:
From VBA Menu, Tools/References and make sure that the RCH. is checked.
Right?
Thanks,
Pete A
<http://www.sundogno
Tue Jul 9, 2013 10:33 am (PDT) . Posted by:
"Kermit W. Prather" kermitpra
You should not need to open the SMF addin ever. I was just suggesting you do that to see if the recalculate function was there because you said it was missing.
All of Randy's functions are inside the XLA which is the ADDIN.
I don't see how only part of it can be loaded.
I'm lost as far as what is failing for you and what to do about it.
Maybe the author of the spreadsheet can help solve the problem.
-----Original Message-----
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf Of Subu S
Sent: Tuesday, July 09, 2013 5:56 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: is it possible to run RCH functions from a USB drive ?
Hi Kermit
I think you are closest to my problem
I'm using MS Excel, MS Office Professional 2010 (Ver 14.0.6129.xyz...) and a Win 7 Machine
1. I have downloaded SMF_Stock_market...Zip from files section of this group and copied it on to D:\SMF Add-In\
2. How do I get ALL of Randy's utilities to be automatically loaded every time my microsoft XL is open ? I don't see them auto loaded when XL is open
3. as of now I first double click RCH_Stock_Market_Functions.xla , keep it open before starting with Pete's sp sheets... or sample sp sheet .... but I see that opening RCH_Stock_Market_Functions.xla may NOT be the best solution , MAY NOT be loading ALL utilities, so how do I ensure that ALL of Randy's VBA including utils are loaded when XL is open ?
my brief searching on the net did NOT yield any help so posting here
regards
subu
--- In smf_addin@yahoogroups.com , "Kermit W. Prather" <kermitp@...> wrote:
>
> I agree with Randy that is I have no clue why it is getting that error. Read my best guest below.
>
> The smfforcecalculate VBA code is in the modUtilities module. You might Verify it is known to VBA
> 1. click the developer task bar and
> 2. select Visual Basics
> 3. Expand the RCH_Stock_Market_Functions
> 4. Expand the Modules
> 5. Select and double click the ModUtilities
> 6. Smfforcecalculate is the first sub in the module.
>
> I̢۪m pretty sure it is there. VBA has rules about calling other projects. I ran into that when I was trying to call smfupdateDownloadTable. I ended up having to write this code. So it could be you will have to do the same for calling the Smfforcecalculate function.
>
> Sub Button1_Click()
> Clear_Form
> Application.Run "'C:\SMFADDIN\RCH_Stock_Market_Functions.xla'!modDownloadTable.smfUpdateDownloadTable"
> End Sub
>
> When I download and try to open the spreadsheet I get this error when changing the links to point to my location for SMF-ADDIN.
> The error the worksheet contains one or more invalid references. Such as an invalid path, etc. Unfortunately, Microsoft Excel fails to give any help as to where the invalid path might be.
> I suspect the error is because the attempt to go across VBA projects.
> I am in no way a VBA expert. I struggle with every error and end up calling my son for help.
>
> I tried changing the VBA code where it called the SMFrecalculate and it took excel down. I ended up having to delete the spreadsheet and start over. Sorry, I can̢۪t resolve your problem.
> For whatever reason it does not like the call.
>
>
>
> From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On
> Behalf Of Randy Harmelink
> Sent: Monday, July 08, 2013 2:34 PM
> To: smf_addin@yahoogroups.com
> Subject: Re: [smf_addin] Re: is it possible to run RCH functions from a USB drive ?
>
>
>
> You shouldn't get the compile error if you have the SMF add-in set up as a reference within the VBA project. It is an independent setting from the add-in manager of EXCEL.
>
> However, if the location of the SMF add-in is changing because it's on a USB drive (or even because it's located on a dynamic drive), the VBA compiler may have issues with that. I'm not sure, because I never do it.
>
> From your pictures, it looks like it should be working, so I am lost as to what the solution might be.
> On Mon, Jul 8, 2013 at 11:14 AM, Subu S <mail_to_subu@...> wrote:
> 5. I have then downloaded another XL sheet that uses the
> RCH_Stock_market_funtions 7. I need too update this file... meaning
> run the macros...meaning use the RCH_stock_market_functions 8. before
> I do that update (of portfolio # 1) i open portfolio #1 XL file, open
> VBA ALT + f11, click on tools - reference and check that
> RCH_Stock_market_functions IS checked 9. It is checked 10. when i run the refresh / update on the portfolio tracker file I still get this error http://flic.kr/p/f5hvkz 11. it says smfforcecalculate is missing ...
> 12. is smf force calculate supposed to be within the RCH_...bundle ?
> or what am I missing ?
>
------------------------------------
Yahoo! Groups Links
All of Randy's functions are inside the XLA which is the ADDIN.
I don't see how only part of it can be loaded.
I'm lost as far as what is failing for you and what to do about it.
Maybe the author of the spreadsheet can help solve the problem.
-----Original Message-----
From: smf_addin@yahoogrou
Sent: Tuesday, July 09, 2013 5:56 AM
To: smf_addin@yahoogrou
Subject: [smf_addin] Re: is it possible to run RCH functions from a USB drive ?
Hi Kermit
I think you are closest to my problem
I'm using MS Excel, MS Office Professional 2010 (Ver 14.0.6129.xyz.
1. I have downloaded SMF_Stock_market.
2. How do I get ALL of Randy's utilities to be automatically loaded every time my microsoft XL is open ? I don't see them auto loaded when XL is open
3. as of now I first double click RCH_Stock_Market_
my brief searching on the net did NOT yield any help so posting here
regards
subu
--- In smf_addin@yahoogrou
>
> I agree with Randy that is I have no clue why it is getting that error. Read my best guest below.
>
> The smfforcecalculate VBA code is in the modUtilities module. You might Verify it is known to VBA
> 1. click the developer task bar and
> 2. select Visual Basics
> 3. Expand the RCH_Stock_Market_
> 4. Expand the Modules
> 5. Select and double click the ModUtilities
> 6. Smfforcecalculate is the first sub in the module.
>
> I̢۪m pretty sure it is there. VBA has rules about calling other projects. I ran into that when I was trying to call smfupdateDownloadTa
>
> Sub Button1_Click(
> Clear_Form
> Application.
> End Sub
>
> When I download and try to open the spreadsheet I get this error when changing the links to point to my location for SMF-ADDIN.
> The error the worksheet contains one or more invalid references. Such as an invalid path, etc. Unfortunately, Microsoft Excel fails to give any help as to where the invalid path might be.
> I suspect the error is because the attempt to go across VBA projects.
> I am in no way a VBA expert. I struggle with every error and end up calling my son for help.
>
> I tried changing the VBA code where it called the SMFrecalculate and it took excel down. I ended up having to delete the spreadsheet and start over. Sorry, I can̢۪t resolve your problem.
> For whatever reason it does not like the call.
>
>
>
> From: smf_addin@yahoogrou
> Behalf Of Randy Harmelink
> Sent: Monday, July 08, 2013 2:34 PM
> To: smf_addin@yahoogrou
> Subject: Re: [smf_addin] Re: is it possible to run RCH functions from a USB drive ?
>
>
>
> You shouldn'
>
> However, if the location of the SMF add-in is changing because it's on a USB drive (or even because it's located on a dynamic drive), the VBA compiler may have issues with that. I'm not sure, because I never do it.
>
> From your pictures, it looks like it should be working, so I am lost as to what the solution might be.
> On Mon, Jul 8, 2013 at 11:14 AM, Subu S <mail_to_
> 5. I have then downloaded another XL sheet that uses the
> RCH_Stock_market_
> run the macros...meaning use the RCH_stock_market_
> I do that update (of portfolio # 1) i open portfolio #1 XL file, open
> VBA ALT + f11, click on tools - reference and check that
> RCH_Stock_market_
> 12. is smf force calculate supposed to be within the RCH_...bundle ?
> or what am I missing ?
>
------------
Yahoo! Groups Links
Tue Jul 9, 2013 10:44 am (PDT) . Posted by:
"Kermit W. Prather" kermitpra
Yes, that is clearer.
My best guess is the spreadsheet has hard code a link to Randy's code which is looking for the SMF addin in the default file that Randy recommends.
If that is true then you cannot move the SMF addin to any other folder.
Try putting the SMF ADDIN back in the programs file to see if the problem goes away.
Contact the spreadsheet author and ask if he hardcoded links to the SMF addin location.
All I can tell you is the spreadsheet does not work on my machine either because I relocated the SMF addin.
Good luck,
Kermit
-----Original Message-----
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf Of Subu S
Sent: Tuesday, July 09, 2013 7:39 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: is it possible to run RCH functions from a USB drive ?
Hi Kermit
I am NOT sure IF I was clear the last time, so the same post with some basic mods
I think you are closest to my problem
I'm using MS Excel, MS Office Professional 2010 (Ver 14.0.6129.xyz...) and a Win 7 Machine
1. I have downloaded SMF_Stock_market...Zip from files section of this group and copied it on to D:\SMF Add-In\ .... THIS IS IMPORTANT - because now the SMG functions are on a fixed drive. Not USB, same drive letter every time
1.1. In XL I have opened "developer" then "add-ins" and opened the Stock market functions add in, and I can see that as in this example http://flic.kr/p/f6d7Ch or a closer look as in here http://flic.kr/p/f5XN7a
2. Still I do get a VBA error when I start pete's XL sheet sample here : http://flic.kr/p/f6atN5 and or here http://flic.kr/p/f6atML
2.1. So I am assuming that SOME of the VBA is NOT being loaded !!
2.3. If that assumption is right, How do I get ALL of Randy's utilities to be automatically loaded every time my microsoft XL is open ? I don't see them auto loaded when XL is open
... # 3 ...deleted ....
4. I did check the "modules" as you mentioned and the VBA was there please see here http://flic.kr/p/f6deru and here http://flic.kr/p/f5Y2x4
5. my brief searching on the net did NOT yield any help so posting here
regards
subu
--- In smf_addin@yahoogroups.com , "Subu S" <mail_to_subu@...> wrote:
>
> Hi Kermit
>
> I think you are closest to my problem
>
> I'm using MS Excel, MS Office Professional 2010 (Ver 14.0.6129.xyz...)
> and a Win 7 Machine
>
> 1. I have downloaded SMF_Stock_market...Zip from files section of this
> group and copied it on to D:\SMF Add-In\
>
> 2. How do I get ALL of Randy's utilities to be automatically loaded
> every time my microsoft XL is open ? I don't see them auto loaded when
> XL is open
>
> 3. as of now I first double click RCH_Stock_Market_Functions.xla , keep it open before starting with Pete's sp sheets... or sample sp sheet .... but I see that opening RCH_Stock_Market_Functions.xla may NOT be the best solution , MAY NOT be loading ALL utilities, so how do I ensure that ALL of Randy's VBA including utils are loaded when XL is open ?
>
>
> my brief searching on the net did NOT yield any help so posting here
>
>
>
> regards
>
> subu
>
>
>
>
>
>
> --- In smf_addin@yahoogroups.com , "Kermit W. Prather" <kermitp@> wrote:
> >
> > I agree with Randy that is I have no clue why it is getting that error. Read my best guest below.
> >
> > The smfforcecalculate VBA code is in the modUtilities module. You might Verify it is known to VBA
> > 1. click the developer task bar and
> > 2. select Visual Basics
> > 3. Expand the RCH_Stock_Market_Functions
> > 4. Expand the Modules
> > 5. Select and double click the ModUtilities
> > 6. Smfforcecalculate is the first sub in the module.
> >
> > I̢۪m pretty sure it is there. VBA has rules about calling other projects. I ran into that when I was trying to call smfupdateDownloadTable. I ended up having to write this code. So it could be you will have to do the same for calling the Smfforcecalculate function.
> >
> > Sub Button1_Click()
> > Clear_Form
> > Application.Run "'C:\SMFADDIN\RCH_Stock_Market_Functions.xla'!modDownloadTable.smfUpdateDownloadTable"
> > End Sub
> >
> > When I download and try to open the spreadsheet I get this error when changing the links to point to my location for SMF-ADDIN.
> > The error the worksheet contains one or more invalid references. Such as an invalid path, etc. Unfortunately, Microsoft Excel fails to give any help as to where the invalid path might be.
> > I suspect the error is because the attempt to go across VBA projects.
> > I am in no way a VBA expert. I struggle with every error and end up calling my son for help.
> >
> > I tried changing the VBA code where it called the SMFrecalculate and it took excel down. I ended up having to delete the spreadsheet and start over. Sorry, I can̢۪t resolve your problem.
> > For whatever reason it does not like the call.
> >
> >
> >
> > From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ]
> > On Behalf Of Randy Harmelink
> > Sent: Monday, July 08, 2013 2:34 PM
> > To: smf_addin@yahoogroups.com
> > Subject: Re: [smf_addin] Re: is it possible to run RCH functions from a USB drive ?
> >
> >
> >
> > You shouldn't get the compile error if you have the SMF add-in set up as a reference within the VBA project. It is an independent setting from the add-in manager of EXCEL.
> >
> > However, if the location of the SMF add-in is changing because it's on a USB drive (or even because it's located on a dynamic drive), the VBA compiler may have issues with that. I'm not sure, because I never do it.
> >
> > From your pictures, it looks like it should be working, so I am lost as to what the solution might be.
> > On Mon, Jul 8, 2013 at 11:14 AM, Subu S <mail_to_subu@> wrote:
> > 5. I have then downloaded another XL sheet that uses the
> > RCH_Stock_market_funtions 7. I need too update this file... meaning
> > run the macros...meaning use the RCH_stock_market_functions 8.
> > before I do that update (of portfolio # 1) i open portfolio #1 XL
> > file, open VBA ALT + f11, click on tools - reference and check that
> > RCH_Stock_market_functions IS checked 9. It is checked 10. when i run the refresh / update on the portfolio tracker file I still get this error http://flic.kr/p/f5hvkz 11. it says smfforcecalculate is missing ...
> > 12. is smf force calculate supposed to be within the RCH_...bundle ?
> > or what am I missing ?
> >
>
------------------------------------
Yahoo! Groups Links
My best guess is the spreadsheet has hard code a link to Randy's code which is looking for the SMF addin in the default file that Randy recommends.
If that is true then you cannot move the SMF addin to any other folder.
Try putting the SMF ADDIN back in the programs file to see if the problem goes away.
Contact the spreadsheet author and ask if he hardcoded links to the SMF addin location.
All I can tell you is the spreadsheet does not work on my machine either because I relocated the SMF addin.
Good luck,
Kermit
-----Original Message-----
From: smf_addin@yahoogrou
Sent: Tuesday, July 09, 2013 7:39 AM
To: smf_addin@yahoogrou
Subject: [smf_addin] Re: is it possible to run RCH functions from a USB drive ?
Hi Kermit
I am NOT sure IF I was clear the last time, so the same post with some basic mods
I think you are closest to my problem
I'm using MS Excel, MS Office Professional 2010 (Ver 14.0.6129.xyz.
1. I have downloaded SMF_Stock_market.
1.1. In XL I have opened "developer&quo
2. Still I do get a VBA error when I start pete's XL sheet sample here : http://flic.
2.1. So I am assuming that SOME of the VBA is NOT being loaded !!
2.3. If that assumption is right, How do I get ALL of Randy's utilities to be automatically loaded every time my microsoft XL is open ? I don't see them auto loaded when XL is open
... # 3 ...deleted ....
4. I did check the "modules" as you mentioned and the VBA was there please see here http://flic.
5. my brief searching on the net did NOT yield any help so posting here
regards
subu
--- In smf_addin@yahoogrou
>
> Hi Kermit
>
> I think you are closest to my problem
>
> I'm using MS Excel, MS Office Professional 2010 (Ver 14.0.6129.xyz.
> and a Win 7 Machine
>
> 1. I have downloaded SMF_Stock_market.
> group and copied it on to D:\SMF Add-In\
>
> 2. How do I get ALL of Randy's utilities to be automatically loaded
> every time my microsoft XL is open ? I don't see them auto loaded when
> XL is open
>
> 3. as of now I first double click RCH_Stock_Market_
>
>
> my brief searching on the net did NOT yield any help so posting here
>
>
>
> regards
>
> subu
>
>
>
>
>
>
> --- In smf_addin@yahoogrou
> >
> > I agree with Randy that is I have no clue why it is getting that error. Read my best guest below.
> >
> > The smfforcecalculate VBA code is in the modUtilities module. You might Verify it is known to VBA
> > 1. click the developer task bar and
> > 2. select Visual Basics
> > 3. Expand the RCH_Stock_Market_
> > 4. Expand the Modules
> > 5. Select and double click the ModUtilities
> > 6. Smfforcecalculate is the first sub in the module.
> >
> > I̢۪m pretty sure it is there. VBA has rules about calling other projects. I ran into that when I was trying to call smfupdateDownloadTa
> >
> > Sub Button1_Click(
> > Clear_Form
> > Application.
> > End Sub
> >
> > When I download and try to open the spreadsheet I get this error when changing the links to point to my location for SMF-ADDIN.
> > The error the worksheet contains one or more invalid references. Such as an invalid path, etc. Unfortunately, Microsoft Excel fails to give any help as to where the invalid path might be.
> > I suspect the error is because the attempt to go across VBA projects.
> > I am in no way a VBA expert. I struggle with every error and end up calling my son for help.
> >
> > I tried changing the VBA code where it called the SMFrecalculate and it took excel down. I ended up having to delete the spreadsheet and start over. Sorry, I can̢۪t resolve your problem.
> > For whatever reason it does not like the call.
> >
> >
> >
> > From: smf_addin@yahoogrou
> > On Behalf Of Randy Harmelink
> > Sent: Monday, July 08, 2013 2:34 PM
> > To: smf_addin@yahoogrou
> > Subject: Re: [smf_addin] Re: is it possible to run RCH functions from a USB drive ?
> >
> >
> >
> > You shouldn'
> >
> > However, if the location of the SMF add-in is changing because it's on a USB drive (or even because it's located on a dynamic drive), the VBA compiler may have issues with that. I'm not sure, because I never do it.
> >
> > From your pictures, it looks like it should be working, so I am lost as to what the solution might be.
> > On Mon, Jul 8, 2013 at 11:14 AM, Subu S <mail_to_
> > 5. I have then downloaded another XL sheet that uses the
> > RCH_Stock_market_
> > run the macros...meaning use the RCH_stock_market_
> > before I do that update (of portfolio # 1) i open portfolio #1 XL
> > file, open VBA ALT + f11, click on tools - reference and check that
> > RCH_Stock_market_
> > 12. is smf force calculate supposed to be within the RCH_...bundle ?
> > or what am I missing ?
> >
>
------------
Yahoo! Groups Links
Tue Jul 9, 2013 12:18 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I'm not sure it does.
What I'm wondering is if the VBA environment saves a hard-coded location of
the reference library, just like the add-in manager of EXCEL does. So when
it comes time to recompile, it looks for the library and the routine, but
it's not found where the hard-coded location says it is supposed to be.
Maybe if you REDO adding it as a reference library, with the new location.
But, there can be some funky things going on with a USB drive. The
operating system does treat them a little differently. I just don't know
how differently in this type of case...
On Tue, Jul 9, 2013 at 9:43 AM, PeteA <optionzz@gmail.com > wrote:
>
>
> Randy, I believe that is corrected when you do what I suggested in another
> post:****
>
> From VBA Menu, Tools/References and make sure that the RCH… is checked.***
> *
>
>
>
What I'm wondering is if the VBA environment saves a hard-coded location of
the reference library, just like the add-in manager of EXCEL does. So when
it comes time to recompile, it looks for the library and the routine, but
it's not found where the hard-coded location says it is supposed to be.
Maybe if you REDO adding it as a reference library, with the new location.
But, there can be some funky things going on with a USB drive. The
operating system does treat them a little differently. I just don't know
how differently in this type of case...
On Tue, Jul 9, 2013 at 9:43 AM, PeteA <optionzz@gmail.
>
>
> Randy, I believe that is corrected when you do what I suggested in another
> post:****
>
> From VBA Menu, Tools/References and make sure that the RCH… is checked.***
> *
>
>
>
Tue Jul 9, 2013 11:54 pm (PDT) . Posted by:
"Subu S" mail_to_subu
Thanks to all those who replied
Some quick clarifications
1. Though the thread title is still USB drive... the add ins and Pete's programs are now run from a fixed D drive and this is a HDD, fixed drive
2. I have not changed the subject fearing yahoo will start a new thread
3. I shall check IF thee is hardcoded mention on Pete's VBA, but I doubt that very much
4. all the places where it stalled were just the reference to "smfForceRecal..." or something like that , no mention of drive letter
TIa
regards
Subu
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> I'm not sure it does.
>
> What I'm wondering is if the VBA environment saves a hard-coded location of
> the reference library, just like the add-in manager of EXCEL does. So when
> it comes time to recompile, it looks for the library and the routine, but
> it's not found where the hard-coded location says it is supposed to be.
>
> Maybe if you REDO adding it as a reference library, with the new location.
>
> But, there can be some funky things going on with a USB drive. The
> operating system does treat them a little differently. I just don't know
> how differently in this type of case...
>
> On Tue, Jul 9, 2013 at 9:43 AM, PeteA <optionzz@...> wrote:
>
> >
> >
> > Randy, I believe that is corrected when you do what I suggested in another
> > post:****
> >
> > From VBA Menu, Tools/References and make sure that the RCH… is checked.***
> > *
> >
> >
> >
>
Some quick clarifications
1. Though the thread title is still USB drive... the add ins and Pete's programs are now run from a fixed D drive and this is a HDD, fixed drive
2. I have not changed the subject fearing yahoo will start a new thread
3. I shall check IF thee is hardcoded mention on Pete's VBA, but I doubt that very much
4. all the places where it stalled were just the reference to "smfForceRecal
TIa
regards
Subu
--- In smf_addin@yahoogrou
>
> I'm not sure it does.
>
> What I'm wondering is if the VBA environment saves a hard-coded location of
> the reference library, just like the add-in manager of EXCEL does. So when
> it comes time to recompile, it looks for the library and the routine, but
> it's not found where the hard-coded location says it is supposed to be.
>
> Maybe if you REDO adding it as a reference library, with the new location.
>
> But, there can be some funky things going on with a USB drive. The
> operating system does treat them a little differently. I just don't know
> how differently in this type of case...
>
> On Tue, Jul 9, 2013 at 9:43 AM, PeteA <optionzz@
>
> >
> >
> > Randy, I believe that is corrected when you do what I suggested in another
> > post:****
> >
> > From VBA Menu, Tools/References and make sure that the RCH… is checked.***
> > *
> >
> >
> >
>
Tue Jul 9, 2013 10:46 am (PDT) . Posted by:
"Kermit W. Prather" kermitpra
Open the elements file and search for company name.
-----Original Message-----
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf
Of timbabear
Sent: Tuesday, July 09, 2013 9:41 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Printing company name for a ticker
I have a field which has the following instruction set:
=RCHGetElementNumber(Ticker,13862)
It has stopped printing the company name, which used to be the result of
element number 13862. What is the new element number which would give me
the company name.
Remember, a rock is smarter than I am with this stuff.
Tim
------------------------------------
Yahoo! Groups Links
-----Original Message-----
From: smf_addin@yahoogrou
Of timbabear
Sent: Tuesday, July 09, 2013 9:41 AM
To: smf_addin@yahoogrou
Subject: [smf_addin] Printing company name for a ticker
I have a field which has the following instruction set:
=RCHGetElementNumbe
It has stopped printing the company name, which used to be the result of
element number 13862. What is the new element number which would give me
the company name.
Remember, a rock is smarter than I am with this stuff.
Tim
------------
Yahoo! Groups Links
Tue Jul 9, 2013 11:09 am (PDT) . Posted by:
"timbabear" timbabear
Finding the elements file for a guy dumber than a rock was fun....having found it, scrolling through 13,862 listings to find that the one I had was supposed to be working added only a moment of frustration until I noticed that 13,863 from Yahoo Key Statistics might do the job. I plugged it in and Viola! it worked.
Thanks for taking the time to point me in the proper direction!
Timba
--- In smf_addin@yahoogroups.com , "Kermit W. Prather" <kermitp@...> wrote:
>
> Open the elements file and search for company name.
>
> -----Original Message-----
> From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf
> Of timbabear
> Sent: Tuesday, July 09, 2013 9:41 AM
> To: smf_addin@yahoogroups.com
> Subject: [smf_addin] Printing company name for a ticker
>
> I have a field which has the following instruction set:
> =RCHGetElementNumber(Ticker,13862)
>
> It has stopped printing the company name, which used to be the result of
> element number 13862. What is the new element number which would give me
> the company name.
>
> Remember, a rock is smarter than I am with this stuff.
>
> Tim
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
Thanks for taking the time to point me in the proper direction!
Timba
--- In smf_addin@yahoogrou
>
> Open the elements file and search for company name.
>
> -----Original Message-----
> From: smf_addin@yahoogrou
> Of timbabear
> Sent: Tuesday, July 09, 2013 9:41 AM
> To: smf_addin@yahoogrou
> Subject: [smf_addin] Printing company name for a ticker
>
> I have a field which has the following instruction set:
> =RCHGetElementNumbe
>
> It has stopped printing the company name, which used to be the result of
> element number 13862. What is the new element number which would give me
> the company name.
>
> Remember, a rock is smarter than I am with this stuff.
>
> Tim
>
>
>
> ------------
>
> Yahoo! Groups Links
>
Tue Jul 9, 2013 12:27 pm (PDT) . Posted by:
"Kermit W. Prather" kermitpra
Glad you found it.
If did not use the Excel "Find & select" function to find the field, you
might consider it next time.
If you do a find looking for company name you only get 2 hits but if you
find for just company you get 100 hits.
So it is important to narrow your search and then expand it if you don't
find what you are looking for.
Manually paging thru Randy element listing can take a long time and you
still might miss it. Using find helps and minimizes the frustration level.
Just think of poor Randy who typed all that stuff into the spreadsheet,
gives it to us for FREE and then answers all of our dumb question.
Also, don't make any changes in that spreadsheet unless you know exactly
what you are doing.
-----Original Message-----
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf
Of timbabear
Sent: Tuesday, July 09, 2013 2:09 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: Printing company name for a ticker
Finding the elements file for a guy dumber than a rock was fun....having
found it, scrolling through 13,862 listings to find that the one I had was
supposed to be working added only a moment of frustration until I noticed
that 13,863 from Yahoo Key Statistics might do the job. I plugged it in and
Viola! it worked.
Thanks for taking the time to point me in the proper direction!
Timba
--- In smf_addin@yahoogroups.com , "Kermit W. Prather" <kermitp@...> wrote:
>
> Open the elements file and search for company name.
>
> -----Original Message-----
> From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On
> Behalf Of timbabear
> Sent: Tuesday, July 09, 2013 9:41 AM
> To: smf_addin@yahoogroups.com
> Subject: [smf_addin] Printing company name for a ticker
>
> I have a field which has the following instruction set:
> =RCHGetElementNumber(Ticker,13862)
>
> It has stopped printing the company name, which used to be the result
> of element number 13862. What is the new element number which would
> give me the company name.
>
> Remember, a rock is smarter than I am with this stuff.
>
> Tim
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
------------------------------------
Yahoo! Groups Links
If did not use the Excel "Find & select" function to find the field, you
might consider it next time.
If you do a find looking for company name you only get 2 hits but if you
find for just company you get 100 hits.
So it is important to narrow your search and then expand it if you don't
find what you are looking for.
Manually paging thru Randy element listing can take a long time and you
still might miss it. Using find helps and minimizes the frustration level.
Just think of poor Randy who typed all that stuff into the spreadsheet,
gives it to us for FREE and then answers all of our dumb question.
Also, don't make any changes in that spreadsheet unless you know exactly
what you are doing.
-----Original Message-----
From: smf_addin@yahoogrou
Of timbabear
Sent: Tuesday, July 09, 2013 2:09 PM
To: smf_addin@yahoogrou
Subject: [smf_addin] Re: Printing company name for a ticker
Finding the elements file for a guy dumber than a rock was fun....having
found it, scrolling through 13,862 listings to find that the one I had was
supposed to be working added only a moment of frustration until I noticed
that 13,863 from Yahoo Key Statistics might do the job. I plugged it in and
Viola! it worked.
Thanks for taking the time to point me in the proper direction!
Timba
--- In smf_addin@yahoogrou
>
> Open the elements file and search for company name.
>
> -----Original Message-----
> From: smf_addin@yahoogrou
> Behalf Of timbabear
> Sent: Tuesday, July 09, 2013 9:41 AM
> To: smf_addin@yahoogrou
> Subject: [smf_addin] Printing company name for a ticker
>
> I have a field which has the following instruction set:
> =RCHGetElementNumbe
>
> It has stopped printing the company name, which used to be the result
> of element number 13862. What is the new element number which would
> give me the company name.
>
> Remember, a rock is smarter than I am with this stuff.
>
> Tim
>
>
>
> ------------
>
> Yahoo! Groups Links
>
------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar