Messages In This Digest (11 Messages)
- 1a.
- Re: StockHouse data errors From: e_bartsch
- 1b.
- Re: StockHouse data errors From: Randy Harmelink
- 2a.
- Excel 2010 From: almondtree
- 2b.
- Re: Excel 2010 From: Randy Harmelink
- 2c.
- Re: Excel 2010 From: almondtree
- 3a.
- Re: Distribute Spreadsheet with SMF Add-In From: bryankam10
- 3b.
- Re: Distribute Spreadsheet with SMF Add-In From: Randy Harmelink
- 4a.
- 200 day MA for SPY From: vegantom
- 4b.
- Re: 200 day MA for SPY From: Randy Harmelink
- 5a.
- rchgetwebdata From: kkkutty
- 5b.
- Re: rchgetwebdata From: Randy Harmelink
Messages
- 1a.
-
Re: StockHouse data errors
Posted by: "e_bartsch" eabartsch@comcast.net e_bartsch
Thu Nov 10, 2011 11:04 am (PST)
Randy:
You helped me with a post on this a few days ago. If I go from a
GetElementNumber function to the GetTableCell function you propose
below, what do I need to do (if I can) substitute the ticker symbol
("MMM" in the example below) for a cell value that contains a ticker
symbol? Let's say MMM is in cell C7--how does one get the GetTableCell
function to pull the ticker contents from a cell and put it into the
htttp:// address?
Thanks.
Eric
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...>
wrote:
>
> Try something like:
>
> Value Formula 81.59 =RCHGetTableCell("
> http://www.barchart.com/cheatsheet. ",1,"2nd Levelphp?sym=MMM
Resistance")
> 81.03
=RCHGetTableCell("http://www.barchart .com/cheatsheet. ",1,"1stphp?sym=MMM
> Level Resistance") 79.45 =RCHGetTableCell("
> http://www.barchart.com/cheatsheet. ",1,"1st Level Support")php?sym=MMM
> 78.43
=RCHGetTableCell("http://www.barchart .com/cheatsheet. ",1,"2ndphp?sym=MMM
> Level Support")
>
> On Sat, Oct 22, 2011 at 5:20 PM, A B morvornagh@... wrote:
>
> >
> > A question for all members. I was also using quite a few of the
stockhouse
> > indicators that are now 'moved'. I have found work arounds for most
of the
> > ones I was using except the 1 / 2 day support / resistance. Which I
think
> > are very useful. While looking for suitable alternatives I noticed
that
> > Barchart on their cheat sheet have this page
http://www.barchart.com/cheatsheet. php?sym=MMM.
> >
> > <http://www.barchart.com/cheatsheet. >php?sym=MMM
> > Does anyone know If I wanted to configure the addin to pull say the
one and
> > two day pivot prices from this page which documentation should I
read?
> >
> > The RCHGetelementnumber already supports the other technical pages
from
> > barchart i.e
http://www.barchart.com/technicals/ <http://www.barchartstocks/MMM .com/ch\ >(#'s 1215 - 1284) and
eatsheet.php?sym=MMM
> > http://www.barchart.com/opinions/ (#'s 702-717) to so I amstocks/MMM
> > thinking this should be fairly easy for me to configure for myself
(if they
> > dont already exist).
> >
> > I did try to figure it out prior to this note but could not quite
get to
> > grips with how the code worked. i.e. to configure the url get and
post and
> > then assign it to an element number (I am a coder but a VBA
developer I am
> > not).
> >
>
- 1b.
-
Re: StockHouse data errors
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Thu Nov 10, 2011 11:08 am (PST)
Just use normal EXCEL string concatenation:
="...string1..."&A1
=A1&"...string1..."
="...string1..."&A1&". ..string2. .."
On Thu, Nov 10, 2011 at 12:04 PM, e_bartsch <eabartsch@comcast.net > wrote:
>
> You helped me with a post on this a few days ago. If I go from a
> GetElementNumber function to the GetTableCell function you propose
> below, what do I need to do (if I can) substitute the ticker symbol
> ("MMM" in the example below) for a cell value that contains a ticker
> symbol? Let's say MMM is in cell C7--how does one get the GetTableCell
> function to pull the ticker contents from a cell and put it into the
> htttp:// address?
>
- 2a.
-
Excel 2010
Posted by: "almondtree" almondtree@yahoo.com almondtree
Thu Nov 10, 2011 6:57 pm (PST)
I downloaded SMF-Template-RCHGetYahooQuote s.xls looks good, then enable macros it all #NAME. Nothing works in Excel 2010. I have Win7 pro, x2 4core Hp Q9300, 3 displays.
- 2b.
-
Re: Excel 2010
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Thu Nov 10, 2011 7:35 pm (PST)
You don't need to enable macros. You probably just have link errors that
need to be resolved. There's an entry in the "Links" area of the group that
addresses that.
When you say "nothing works" -- did you try entering any of the example
formulas yourself, from the documentation?
On Thu, Nov 10, 2011 at 7:50 PM, almondtree <almondtree@yahoo.com > wrote:
> I downloaded SMF-Template-RCHGetYahooQuote s.xls looks good, then enable
> macros it all #NAME. Nothing works in Excel 2010. I have Win7 pro, x2 4core
> Hp Q9300, 3 displays.
>
- 2c.
-
Re: Excel 2010
Posted by: "almondtree" almondtree@yahoo.com almondtree
Thu Nov 10, 2011 10:52 pm (PST)
I think I solved some of my problems: saved a book1.xls in .xlsm. found #8375 message started copying finviz stuff. =RCHGetTableCell("http://finviz. com/quote. ",1,">Book/sh"ashx?t=MMM ) This worked. Thanks for point me right direction!
--- In smf_addin@yahoogroups.com , "almondtree" <almondtree@...> wrote:
>
> I downloaded SMF-Template-RCHGetYahooQuote s.xls looks good, then enable macros it all #NAME. Nothing works in Excel 2010. I have Win7 pro, x2 4core Hp Q9300, 3 displays.
>
- 3a.
-
Re: Distribute Spreadsheet with SMF Add-In
Posted by: "bryankam10" bryan.a.kam@gmail.com bryankam10
Thu Nov 10, 2011 7:49 pm (PST)
Hi Randy,
Many thanks for your add-in. I just found the below reply on replacing the links. I was just wondering if you know if the "find and replace" method works if the link to the add-in is part of an array. I've tried to do this, but don't think it does. Would you have any solutions to this?
Many 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'!RCHGetEleme ntNumber( "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: "Randy Harmelink" rharmelink@gmail.com rharmelink
Thu Nov 10, 2011 8:01 pm (PST)
Yes, it does work on arrays as well. As I noted, I even use a macro to do
the replacement. I have it assigned to a toolbar button. So, whenever I
have a worksheet with bad links, I use the toolbar button to fix them all,
regardless of whether the formulas are array-entered or not. But I do have
to run it on each individual worksheet.
I did have one person use the exact string I cited below. But you need to
replace the path found on YOUR formulas, in your worksheet.
On Thu, Nov 10, 2011 at 8:41 PM, bryankam10 <bryan.a.kam@gmail.com > wrote:
>
> Many thanks for your add-in. I just found the below reply on replacing the
> links. I was just wondering if you know if the "find and replace" method
> works if the link to the add-in is part of an array. I've tried to do this,
> but don't think it does. Would you have any solutions to this?
>
> --- 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
>
- 4a.
-
200 day MA for SPY
Posted by: "vegantom" vegantom@yahoo.com vegantom
Thu Nov 10, 2011 9:19 pm (PST)
Hi Randy,others,
Where can I retrieve the 200 day SMA for SPY from ? I couldn't find it at yahoo or msn.
I can generate it based on yahoo historical prices, but that will be the 200 day MA based on "trading" days. I'm looking for 200d MA based on the last 200 days (trading days and holidays included).
Appreciate any help !
Thanks !
- 4b.
-
Re: 200 day MA for SPY
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Thu Nov 10, 2011 9:32 pm (PST)
You're in luck. I usually avoid Yahoo's 200-day moving average because it's
based on calendar days instead of trading days:
=RCHGetYahooQuotes("SPY","m4" )
On Thu, Nov 10, 2011 at 10:18 PM, vegantom <vegantom@yahoo.com > wrote:
>
> Where can I retrieve the 200 day SMA for SPY from ? I couldn't find it at
> yahoo or msn.
>
> I can generate it based on yahoo historical prices, but that will be the
> 200 day MA based on "trading" days. I'm looking for 200d MA based on the
> last 200 days (trading days and holidays included).
>
- 5a.
-
rchgetwebdata
Posted by: "kkkutty" kkkutty@yahoo.com kkkutty
Thu Nov 10, 2011 10:14 pm (PST)
Hi Randy,
I have a quick question....I've been using the following formula for some time now to get information. It was working fine, but all of a sudden it seems that the length of the data that was being retrieved got truncated. Do you have any idea why that would happen all of a sudden since I haven't changed anything? Is there way to increase the number of characters being retrieved? Do you know how many characters are supposed to be retrieved using the formula below? Thanks again for all your help.
RCHGetWebData(CONCATENATE( "http://www.reuters. com/finance/ ",B4,"%20",C4,stocks/lookup? searchType= any&search= "%20",D4, "%20",E4) ,1)
- 5b.
-
Re: rchgetwebdata
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Thu Nov 10, 2011 10:43 pm (PST)
The default is to retrieve no more than 32,767 bytes -- the limit of a
string size that can be returned to EXCEL. You should be able to check that
by checking the length -- LEN() -- of what is returned.
But the "1" parameter is your starting point. So you can retrieve the next
32,767 characters by changing that "1" to 32768. Check the documentation
for more info on the function parameters.
The most reasonable explanation of why you're not getting something you
used to is that Reuters added some code or advertising to the web page, so
the source code you're looking for got pushed further out and maybe beyond
the 32,767-byte threshold.
On Thu, Nov 10, 2011 at 11:14 PM, kkkutty <kkkutty@yahoo.com > wrote:
>
> I have a quick question....I've been using the following formula for some
> time now to get information. It was working fine, but all of a sudden it
> seems that the length of the data that was being retrieved got truncated.
> Do you have any idea why that would happen all of a sudden since I haven't
> changed anything? Is there way to increase the number of characters being
> retrieved? Do you know how many characters are supposed to be retrieved
> using the formula below? Thanks again for all your help.
>
> RCHGetWebData(CONCATENATE( "
> http://www.reuters.com/finance/ stocks/lookup? searchType= any&search=
> ",B4,"%20",C4,"%20",D4, "%20",E4) ,1)
>
Need to Reply?
Click one of the "Reply" links to respond to a specific message in the Daily Digest.
MARKETPLACE
Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Individual | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe
Tidak ada komentar:
Posting Komentar