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

Re: Cleaning Data



PureBytes Links

Trading Reference Links

<x-html>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 5.50.3825.1300" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT size=2>A VBA macro in Word will easely do the job.</FONT></DIV>
<DIV><FONT size=2></FONT>&nbsp;</DIV>
<DIV><FONT size=2>Setting it up will be the hardest -and more of a (but then one 
time only) very time consuming-&nbsp; part.</FONT></DIV>
<DIV><FONT size=2></FONT>&nbsp;</DIV>
<DIV><FONT size=2>These are the basic steps that the macro should perform each 
time-before using DL's Convert:</FONT></DIV>
<DIV><FONT size=2></FONT>&nbsp;</DIV>
<DIV><FONT size=2>1. Open file at its current Temp location, 
eg&nbsp;"C:\Temp\Email\Data\yourfile.txt"</FONT></DIV>
<DIV><FONT size=2>2. Add to each line the missing Open Intrest(O/I) column's 
values,&nbsp;eg " ,0 "</FONT></DIV>
<DIV><FONT size=2>3.&nbsp;Then add the proper ASCII heading&nbsp;on&nbsp;the 
first line</FONT></DIV>
<DIV><FONT size=2>4. Then use the Find&nbsp;function and later Select each line 
that holds the " ,0,0 " values for both the Vol + O/I</FONT></DIV>
<DIV><FONT size=2>5. Then fully Delete each of these lines &nbsp;-found in # 
3-&nbsp; and upmove the line below that&nbsp;</FONT><FONT size=2>(not having any 
blanc lines left)</FONT></DIV>
<DIV><FONT size=2>6. Then Save As the file in PRN format now in a back up 
folder, "C:\YourProvider\yourfilewithdate.prn"</DIV></FONT>
<DIV><FONT size=2>7. Call the Downloader via the Run program 
command</FONT></DIV>
<DIV><FONT size=2></FONT>&nbsp;</DIV>
<DIV><FONT size=2>Running Word+sending the keystrokes to run the macro+Running 
the Downloader+sending the keystrokes to</FONT></DIV>
<DIV><FONT size=2>the Downloader&nbsp;for the Convert actions, can all&nbsp;be 
run easely done in&nbsp;the&nbsp;ScriptIt program.&nbsp;&nbsp;</DIV></FONT>
<DIV><FONT size=2></FONT><FONT size=2></FONT><BR>Regards,<BR>Ton Maas<BR><A 
href="mailto:ms-irb@xxxxxxxxxxxxxxxx";>ms-irb@xxxxxxxxxxxxxxxx</A><BR>Dismiss the 
".nospam" bit (including the dot) when replying.<BR>Homepage&nbsp; <A 
href="http://home.planet.nl/~anthmaas";>http://home.planet.nl/~anthmaas</A></DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<BLOCKQUOTE dir=ltr 
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
  <DIV 
  style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B> 
  igburgy@xxxxxxxxxxxxxxxx </DIV>
  <DIV style="FONT: 10pt arial"><B>To:</B> <A title=metastock@xxxxxxxxxxxxx 
  href="mailto:metastock@xxxxxxxxxxxxx";>metastock@xxxxxxxxxxxxx</A> </DIV>
  <DIV style="FONT: 10pt arial"><B>Sent:</B> dinsdag 12 september 2000 
  11:10</DIV>
  <DIV style="FONT: 10pt arial"><B>Subject:</B> Cleaning Data</DIV>
  <DIV><BR></DIV>
  <DIV><FONT color=#000080>I've just started downloading daily data from a new 
  supplier (see sample below).</FONT></DIV>
  <DIV><FONT color=#000080>The data is fine except that when a security has not 
  traded on the day and there is zero volume the supplier still provides the 
  O/H/L/C which I assume&nbsp;are the most recent prices the security traded at. 
  In the sample below, the security AAA hasn't traded since January this year. 
  So having a data point on my chart when the stock has not traded to me 
  is&nbsp;useless.</FONT></DIV>
  <DIV><FONT color=#000080>&nbsp;My previous data supplier only ever supplied 
  data which <STRONG>ACTUALLY </STRONG>traded on each day. </FONT></DIV>
  <DIV>&nbsp;</DIV>
  <DIV><FONT color=#000080><U>My question is:</U></FONT></DIV>
  <DIV><FONT color=#000080>Is there an easy way to "clean" this data and 
  eliminate those securities from the file which have zero volume before I 
  convert the file in the Downloader? I had a try in Excel without success and I 
  thought maybe someone on the list might have&nbsp;a solution.</FONT></DIV>
  <DIV>&nbsp;</DIV>
  <DIV><FONT color=#000080>regards........Ian</FONT></DIV>
  <DIV><FONT color=#000080></FONT>&nbsp;</DIV>
  <DIV><FONT 
  size=2>AAA,20000911,2.5,2.5,2.5,2.5,0<BR>AAAWMB,20000911,0.001,0.001,0.001,0.001,0<BR>AAAWXA,20000911,0.15,0.15,0.15,0.15,0<BR>AAI,20000911,59,59,59,59,0<BR>AAP,20000911,7.14,7.15,7.12,7.12,67120<BR>AAPWMA,20000911,1.02,1.02,1.02,1.02,0<BR>AAR,20000911,0.028,0.028,0.027,0.027,38704<BR>AAT,20000911,0.465,0.48,0.46,0.47,159920<BR>AATDA,20000911,1,1,1,1,0<BR>AATDB,20000911,0.5,0.5,0.5,0.5,0<BR>AATNA,20000911,1.95,1.95,1.95,1.95,0<BR>AATR,20000911,0.52,0.52,0.52,0.52,0<BR>AAU,20000911,1.7,1.7,1.7,1.7,18000<BR>AAV,20000911,0.11,0.11,0.11,0.11,0<BR>ABC,20000911,0.47,0.47,0.45,0.47,510843<BR>ABCG,20000911,2.07,2.07,2.07,2.07,29500<BR>ABCN,20000911,0.92,0.92,0.92,0.92,0<BR>ABCR,20000911,0.21,0.21,0.21,0.21,0<BR>ABD,20000911,0.28,0.28,0.28,0.28,0<BR>ABDDA,20000911,0.165,0.165,0.165,0.165,0<BR>ABDO,20000911,0.09,0.09,0.09,0.09,0<BR>ABF,20000911,2.85,2.85,2.85,2.85,0<BR>ABG,20000911,2.01,2.01,2.01,2.01,2000<BR>ABK,20000911,0.4,0.4,0.4,0.4,29450<BR>ABKO,20000911,0.13,0.13,0.13,0.13,0<BR>ABN,20000911,0.85,0.85,0.85,0.85,0<BR></FONT></DIV></BLOCKQUOTE></BODY></HTML>

</x-html>