|
PureBytes Links
Trading Reference Links
|
Also this time see answers below.
Have also included more info at the bottom.
Regards,
Ton Maas
ms-irb@xxxxxxxxxxxxx
Dismiss the ".nospam" bit (including the dot) when replying.
=========================================
----- Original Message -----
From: Leo
To: A.J. Maas
Sent: dinsdag 18 mei 1999 23:02
Subject: Re: excel-files
> Good Morning,
>
> I simplify the excel file to clarify the problem. So I take one excel file with 4
> different tickers and the quote of a single day. It's like the file you receive from
> yahoo or different data providers. This is the content of the excel file :
>
> TICKER DATE OPEN HIGH LOW CLOSE VOLUME
> X12040 05/14/1999 239.5 242.80 232.2 240.8 100200
> X12592 05/14/1999 48.36 48.8 47.9 48.01 364000
> X12007 05/14/1999 153 156.3 148 150.5 204100
> X13000 05/14/1999 121.70 123.30 116.5 116.5 587000
This is a correct file, for use in Excel.
But not for use when converting from Excel to MetaStock, see the next bit below.
> Now I load this file using the downloader( option create new files), and I only
> obtain 3 different files in the test folder. There are Emaster, F1, and Master.
>
> So first problem : It miss the F2,F3,F4 files.
It misses these files, as you were not converting 4 securities(tickers) from an Excel-file.
To convert 4 securities from Excel to MetaStock you must also have 4 Excel-files, eg
one(1) for each single security. One(1) Excel file = one(1) MetaStock File. This is
standard procedure when converting using the Downloader.
> Now second problem.When I use the dowloader to look the data in the test folder, I
> find the following values for the stock:
>
> Name and Symbol : X12040
> First Date : 5/14/99
> Date Open High Low Close Volume
> 5/14/99 121.7 123.3 116.5 116.5 587000
>
> As you see you have the ticker of the first stock and the quote of the last one.
This is correct. The Downloader will see the whole file as if it were one(1) ticker
(=security) only. Then it will convert all rows, and if there is data present for one singly
day, then you will get the data from the last converted row.
> I do another time the loading of the excel file before I send you this Email and I
> obtain the same result.
>
> I attach the four files to this email, the excel file and the 3 metastock files.
They were received in good order. Now from the Excel file, if I wish to have
the 4 securities to be present in the MetaStock files, I would have to split-up the
Excel-file into 3 more Exel-files (making a total of 4 xls-files, see further above).
Using the Visual Basic for Applications that comes with Excel, you can arrange this
(eg in the Excel workbook, insert 3 more sheets for each of the remaining tickers and
Save as 4.0-format using the tickercode for filename).
> Thank you for your time on this problem.
>
> My conclusion is that it is caused by a bug of the new 6.52 version. But may be you
> have a different opinion ?
This above is a shortcoming in the DL-Convert Tool and not a bug, i.e. the program is
written that way and can only be adjusted by Equis.
Apart from the solution/option given above, you can also consider to save the Excel file
(holding 4 or more (multiple) securities as an ASCII txt-file. In Excel choose File|Save As
and save the file as Text-document. For this you will also have to adjust the file's columns
and (column+file) arrangement accordingly. Should you wish to use a header for each
column then see the column layout given in the example below and also see the DL-manual
(p. 70-72), but do note now that this manual does contain bugs, i.e. compare notes.
> Best Regards.
>
> Robert Vialet
Either way can be automated (aslo, eg apart from the VBA in Excel) using SriptIt or AutoIt.
See my homepage for more info
http://home.wxs.nl/~anthmaas
================================================
The Ascii file's proper column headers + arrangement
<TICKER>,<PER>,<DTYYMMDD>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<O/I>
(Note that there is no seperating comma placed at the end)
CSV
(comma seperated values, wonder why TOO many programs start and 'save as'
semi-colon values, wich results in SCV!!!, when one expects.............. comma's)
To use with CSV
-apply the proper colom headers(+arranging see below)
To use with Excel
-for proper colom arranging, first look into the dl manual p.72-73
"Quatro/1-2-3 and Excel Source File Layout" and too, for the correct
"spreadsheet" files layouts and other conditions
(VERY VERY important! for not getting irretating errors)
-open your csv-file in Excel and make the appropeate adjustments in each colom,
either for 'saving as' a ASCI or Spreadsheet layout(TXT or CSV or XLS) but make
your choice upfront as f.i. ASCI layout in XLS-file doesn't convert
-GIVE each colom its own header
-use for historical updating per SINGLE security ONLY
To use with ASCI
-DO NOT EVER APPLY coloms headings wich are written in dl manual on p.70
(THESE ARE ONE OF THE CAUSE OF METASTOCKS' UNFRIENDLY ERRORS)
but first make a normal 'any security will do' conversion from Metastock type to "your type",
this, as header arrangement and its 'naming' differs f.i. in the way the "<date>" header
ought to be written. This MUST be <DTYYMMDD> and not <DATE> or <date>(=ERRORS).
-in each file seperately, apply the apropiate colom-headers(especialy when receiving
more of these STUPID IRRETATING "can't do" errors, even though the files' layout and its
contents are in the correct order or layout)
-delete AND don't ever apply the superflous, usually in the header, <time> colom
(don't know for whatever reasons files get back-converted with a header and a colom as such as
the "standard" MSwin is not RT compatible, but THIS TOO causes plenty of ERRORS)
-when files are back-converted, rename them as files are automatically WRONG named with
7 positions of the ticker symbol, and the "period" D at the names' 8 position filename(so much
for Win95 compatibility, no the Downloader for Windows95 v6.5 is only TRUE DOS compatible)
-delete all empty lines at a files' bottom(don't know why this have to matter BUT IT DOES)
-check on and delete all superflous "tabs","spaces","comma's"(especialy the last one right of
the 7th data(10th "normal") colom(usualy "open intrest") and all other useless but non-the less
added(WORD97) characters, a lot too are "invisible"(CR etc.) BUT ALL WILL CREATE ERRORS
-use for daily+historical updating per MULTIPLE and SINGLE securities
-Notepad has a limit to file lengths and ALSO in its 'copy' or making manual adjustments
functioning, thus Wordpad for the long lengthy files, but then make sure Wordpad doesn't
correct a file by itselve(re-arranging, adding not visable characters or blank lines), as that
happens too often too.
================================================
True. This is not directly to be found in the Downloader. However, you are able to
"Insert Rows" in a data file in the Downloader, as well as you can "Delete Rows"
from a file.
"Open" a datafile, click on "Edit" and scroll down to the bottom end of the then
unfolding menu.
In between 2 consequetive dates(periods) you can usualy automatically add the
right dates, otherwise a manual input is desired. Here my dates are expressed
as 981015 while when/if I have to do a manual input I click on Insert Row, (and if
the date is not already been given) then highlight the date cell and have to this
manual input 98/10/15.
If I do not use the forward slashes, an error is presented. Why goes beyond my
computer/Downloader knowledge. But this as long as I get it to work, will not
futher bother about it too much. An input consists of at least the date.
The Open|High|Low|Close|Volume|OpenIntrest cells will be automatically get
filled with a " 0 " for that particular date.
Thats one way of doing it.
Another way is to produce(create) an separate update file in Excel.
(You can do this for multiple tickers and multiple dates in just one file, but make
sure to use the normal date sequence per ticker, eg oldest date at the top of the
sequence, latest date at the bottom).
"Open" Excel and on the first spreadsheet, in the first colom(field), in the first cell,
input the ticker/symbol.
Then, by "Select"ing that cell, it will then have bold black borders, move the
mouse towards cells lower right hand corner, this will then change the mouse-pointer
from the standard bold big white cross into a small cross " + ".
When the mouse-pointer does so, left click an hold that click pressed down, and
now drag this new mouse pointer, the " + " , downwards down the colom over the
other by you required cells.
On releasing the left click, you will now have all the cells containing the same ticker.
Do this as often as is required. It is known as multi-seleceting.
In the next colom(2nd) you input a " , " (comma), then in the next(3td) colom a " D "
and so on until it looks like this example:
wlake,D,981013,0,0,0,0,0,0
wlake,D,981014,0,0,0,0,0,0
wlake,D,981015,0,0,0,0,0,0
So no headers are used,each row is considered to be 1 line, each detail(cell) is
separated by a comma. For the dates:
they must be in sequence going up in date-time and down the colom for each of
the records row(line).
One line then reads as(see also DL-manual on Asci-files):
ticker,period,date,open,high,low,close,volume,openintrest
Note that there is no comma being put at the rows end(the last colom is the
open intrest).
Any subsequently following tickers can be inputted on the next row, in this either
the same tickers or tickers corresponding with other securities. As long as you
keep the latest date at the bottom of that securities rows, and the oldest date at
the top of that securities group of rows.
Now fill the spreadsheet with as much as the by you as dates/records as is
required and make sure to delete any of the empty rows(lines) right below the
last row.
"Save as" this spreadsheet as a "Text (TAB is separator) (*.txt)" file, eg walter.txt
and next run a program like Word(or Wordpad). "Open" in Word the above file,
and you will big spaces(tabs) between the former cells contents(the records
details). It will look like this:
wlake , D , 981013 , 0 , 0 , 0 , 0 , 0 , 0
wlake , D , 981014 , 0 , 0 , 0 , 0 , 0 , 0
wlake , D , 981015 , 0 , 0 , 0 , 0 , 0 , 0
Sometimes it will look a bit messier then this.
Now the visual spaces -in this case- represents the tabs(like hitting
the TAB key on the keyboard).
Select with the mousepointer, eg start by placing the it right behind the " e " in
'wlake' and then drag by holding down the left-mouse-button, to the right until you
meet up with the first comma but end this dragging only at the left side of the comma.
In this you are selecting the invisable tab-function, wich is hidden in the file, and
now will turn into blue or black and can be long or short, depending on a selection.
When this selction is blue(black) hold the mouse-pointer flowing over it, do
1x right-click, and in the unfolding quick menu choose "Copy"(this will copy
selected contents to clipboard).
Go to the workmenu's "Edit" and scroll down to and click "Change" or
"Replace"(should be right below "Search"). In the tiny dialog window put in
the Find line the mousepointer and hit "Ctrl"+"V" keys on the keyboard(=pasting).
The blinker will now have moved a few centimeters(cm) to the right.
Leave the other Replace input line blanc and PRESS the Replace button.
All of the TABs positions will get replaced by "nothing", eg all TABs will disapear,
leaving the file's contents lines to be joint together, but separated by comma's.
Close the Word program('X' upper right corner) and when asked "To save
changes" answer by pressing the Yes-button.
Open the Downloader and start a conversion session from "ASCI-txt"(Source)
to "Metatstock"(Destination) as the conversion file types.
Before doing this on your good data files, do a simulation "test-run" , eg by
converting to your desktop folder or to any temp folder.
Make sure 'create new files' is applied as "Option" in the Convert sub-programs
Dialog Window.
Also "Replace and add to end of file" needs to be applied on the Destination Tab.
Ones you get the hang of it, and when working in Excel, any file manipulations
sessions or file creationing will become much more easier and pleasent to do and
will then go very quickly too.
Best regards,
Ton Maas
|