[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: XL stuff

PureBytes Links

Trading Reference Links

 
<x-html>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META content="MSHTML 5.00.2614.3500" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Tahoma>Thanks a lot for the stuff you give us 
Walter...</FONT></DIV>
<DIV><FONT face=Tahoma>I'm really wondering how you can find all these smart 
links ;-))</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=Tahoma>Jean-Roland</FONT></DIV>
<BLOCKQUOTE 
style="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">
  <DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
  <DIV 
  style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B> 
  Walter Lake </DIV>
  <DIV style="FONT: 10pt arial"><B>To:</B> <A 
  href="mailto:metastock@xxxxxxxxxxxxx"; title=metastock@xxxxxxxxxxxxx>Metastock 
  bulletin board</A> </DIV>
  <DIV style="FONT: 10pt arial"><B>Sent:</B> Thursday, August 24, 2000 4:59 
  PM</DIV>
  <DIV style="FONT: 10pt arial"><B>Subject:</B> XL stuff</DIV>
  <DIV><BR></DIV>Thanks for your emails<BR><BR>Look at the Fritch site for a 
  very large XL workbook for TA. Lots of<BR>excellent VBA code to adapt and 
  ideas for changing of the Excel interface to<BR>put your own package 
  together.<BR><BR><A 
  href="http://home.earthlink.net/~jfritch/";>http://home.earthlink.net/~jfritch/</A><BR><BR>ProUtilities 
  for XL is a backtesting system for XL just starting out.<BR><BR>Also enclosed 
  is some VBA code for over-writing formulas from another List.<BR>The ideas 
  should be helpful in solving your problem.<BR><BR>Best 
  regards<BR><BR>Walter<BR><BR>=======================<BR><BR>"... This isn't 
  super-technical but it gets around the age-old problem of<BR>users wanting to 
  type over formula cells.&nbsp; We use the Capital Asset Pricing<BR>Model for 
  some stock valuations (I work at a stockbroker).&nbsp; The user wanted<BR>to 
  be able to either put in the inputs to the CAPM formula and get 
  the<BR>discount rate OR just to type in any discount rate directly.&nbsp; 
  Typing in a<BR>rate would, of course, overwrite the formula (unless we had a 
  second cell<BR>for user input - ugly).&nbsp; The Worksheet_Change event got 
  around this, as<BR>below:<BR><BR>Private Sub Worksheet_Change(ByVal Target As 
  Excel.Range)<BR>'Allows typeover of Discount rate formula; reenters formula 
  when required.<BR>'Sets comment text to reflect current status; changes font 
  colour<BR><BR>Const Formula = 
  "=RiskFreeRate+StockBeta*(MarketReturn-RiskFreeRate)"<BR><BR>&nbsp;&nbsp;&nbsp; 
  If ActiveCell.Column = 7 Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If 
  ActiveCell.Row &gt;= 7 And ActiveCell.Row &lt;= 9 
  Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  With 
  Range("DiscountRate")<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  .FormulaR1C1 = 
  Formula<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  .Font.ColorIndex = 10 
  'Green<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  With 
  .Comment<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  .Text Text:="Discount Rate is now per 
  CAPM."<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  .Visible = 
  False<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  End With<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  End With<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ElseIf ActiveCell.Row = 
  10 And ActiveCell.Column = 7 
  Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  With 
  ActiveCell<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  .Font.ColorIndex = 6 
  'Yellow<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  With 
  .Comment<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  .Text Text:="Discount Rate is now 
  user-defined."<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  .Visible = 
  True<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  With 
  .Shape<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  .Width = 
  150<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  .Height = 
  12<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  End 
  With<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  End With<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  End With<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  Cells(10, 7).Activate<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End 
  If<BR>&nbsp;&nbsp;&nbsp; End If<BR>End Sub<BR><BR>Whenever he reinputs a 
  component of the CAPM rate the CAPM formula is<BR>reentered for him.&nbsp; I 
  thought this was kinda neat, and the guy was<BR>impressed.&nbsp; It won't set 
  the programming world on fire but it deals with a<BR>common user/Excel 
  interaction - IMNSHO nicely, I thought. 
..."<BR><BR><BR><BR><BR></BLOCKQUOTE></BODY></HTML>

</x-html>