GoldenGem data conventions


GoldenGem works with a very wide variety of data conventions. It will understand whether you use Unix, Mac, or Windows conventions automatically, and you do not need to worry about this. The basic rule is that columns are separated by commas and symbols like quotation marks, tabs, and spaces are completely ignored by the program.

1. Default conventions

If you do not change any settings, GoldenGem works with a convention used by Trademan, StockDownloader, and many other programs, of plain text files with names ending in .txt and with columns separated by commas, and numbered like this

Ticker
column 2
Date
column 1
Close
column 6
Volume
column 7


This table of 4 numbers appears somewhere at the lower right corner of the GoldenGem console.

You do not need to change these numbers if you plan to use StockDownloader to load data from the internet, or to use or Trademan files from http://biz.swcp.com/stocks or elsewhere, and so you do not need to read further.

2. Internet data

Internet data from Stock Downloader comes from a number free data sources, including Google, Yahoo, and MSN. Leave the file position numbers as the default values

Ticker
column 2
Date
column 1
Close
column 6
Volume
column 7


We attempt to include URL encoding to match any ticker that is available on any of those sites, if a ticker you know of does not come up, inform us and we will include it. Incidentally, it is also possible to go to the same sites directly and obtain .csv files, but these have data in reverse order. The main function of StockDownloader is actually to correct the fact that Google, Yahoo and MSN all use reverse-order .csv files.

Using saved internet data.


After each internet search, a file called Internet.txt is created with these same default conventions. That way, even if you are later offline, you can browse to using the file browser, to reaload the same data. The file is automatically over-written each time you obtain new internet data, but if you want to save the results of more than one internet search, the file can be renamed or saved by choosing 'Browse for new file' and right clicking Internet.txt, so you can create a library of data files if you like in this way. The most effective use of this feature is to use StockDownloader to download a large list of related shares, and to load various small subsets from Internet.txt to test later on.

3. Loading a folder of .csv files

Many websites such as the Forex site (see point 5. below) provide you with .csv files which can be read by GoldenGem. Go to Control Panel, Folder Options and uncheck 'hide extensions for known file types' so you can read the names of your files. They have names like IBM.csv, MSFT.csv and so-on (or if the names are not so suggestive you can change them). Put all the files in a single folder, and look in the files to make sure dates are in increasing order. Almost always the first column will be the date. GoldenGem will insert the ticker in column 2. Look at the data to determine which column position has close, and if there is volume included, which position has volume. Set the field position numbers in GoldenGem so that the tick number is 2, because the ticker will be added in column 2. GoldenGem will shift subsequent columnts to the right, so that if for example close and volume are in columns 5 and 6, then close and vol in GoldenGem want to be set to 6 and 7. If there is no volume just leave the volume field position number in GoldenGem blank. Enter into the 'related group of tickers' window the first subset of the ticker names you would like to load, without the letters .csv, so the window will show things like IBM, MSFT. Select 'Import from a folder' from the File menu and the program will be running.

Using a file of combined imported data

Just like the results of the last internet search are automatically saved, so the results of the last import from a folder are saved, in a file called Combined.txt. You can browse to this file and reload it later if you like, or right click it and rename it, in order to save it or email it. Compared to your separate .csv files, the tickers are in column 2, and successive columns all moved to the right.

4. Bank of England

Choose columnar csv file from the Bank of England website (you can search for what daily data is available), set file position numbers as
Ticker
column 2
Date
column 1
Close
column 3
Volume
(leave blank)

Then use 'Browse for new file' to find your data file from GoldenGem.

5. Forex

At www.forexrate.co.uk choose 1 day, split by comma, 500 data points, and create a folder of .csv files of both Forex and stock data. Set the four file position numbers as
Ticker
column 2
Date
column 1
Close
column 6
Volume
column 4

6. Importing a whole folder and choosing tickers later

It is also possible to be more creative, if you import ALL the .csv files from a folder, to make a file Combined.txt, then you can Browse to this file and load various subsets of the tickers. This file contains all the data from the .csv files, and can be emailed or used in a database program.

7. Making a single custom .txt file

A valid way to create a single data file, called, for example, mydata.txt, using notepad or wordpad. It is very important to be sure to save the file as an actual plain text file, not just an rtf file with a name ending in .txt. The file saving window has a small white window at the bottom that lets you select which type of file it will be, and you must use this window, so that the letters .txt are added automatically, not typed in by you. Leave the file position numbers in GoldenGem at the default numbers
Ticker
column 2
Date
column 1
Close
column 6
Volume
column 7


Then each day, to add more rows to your plain text file like this

January 12, shoes,   ,  ,   , 12.35
January 12, coats ,   ,   ,  , 1.30
January 13, shoes,   ,   ,   , 12.20
January 13, coats ,   ,   ,   , 1.24


etcetera. The commas are only to put the 'close' price in column 6. Spaces, tabs and quotation marks can be used to organize the display of your file, as they are are ignored by the program. If there is volume data, and you want to use column 7 for volume, so you may write

January 12, IBM,   ,   ,   , 34.2, 128884

etcetera.

8. Creating a folder of custom .csv files

Another very easy way of entering data into GoldenGem is to create a folder containing your own separate .csv file for each variable of interest, with just a single column of numbers in each file. These can be interest rates, prices, volumes or any other data, each with its own filename like for example, for IBM volume you might want to use IBMVOL.csv. Here is a sample of how some close data might look in the file

34.92
35.89
34.94
36.45
37.54
36.76


etcetera.


The folder can include many tickers you might be interested in, and during each run you would enter only a subset of these ticker names into GoldenGem, like IBMVOL etcetera, in searching to find a small subset of related variables. An important reason for using your own data files is that you may include data that is specific to a particular industry, and not being considered by run-of-the-mill traders.

For single column .csv files set the numbers tick, date, close, vol like this
Ticker
column 2
Date
column 3
Close
column 1
Volume
(leave blank)


rather than the default, and here is why: Since your .csv files have only column 1, the file Combined.txt will have only two columns, the close and ticker columns, which will be columns 1 and 2 in the imported data and in the combined data file Combined.txt. The date and volume field 3 will not be used because there will be no data in column 3. As a little extra, You can if you wish, optionally create a second column of your .csv files and put dates in that column. Then your .csv file will have close and date in columns 1 and 2. When the data is imported, the ticker will be put in column 2 and the date will be displaced to column 3, so you will have tick, date, close in columns 2, 3, 1. Using a date field in this way is optional, and the only function of that field is that the last loaded value will be displayed in the 'loaded until' window, and also when you 'view loaded data' you will see the dates for your own reference.

Because the program does not actually read the date field, it is important to be sure that the data is in increasing order of time, and that the same set of days is used for all variables.

What is the difference between a .csv and a .txt file?


It is best, on your computer, to go to the control panel, choose Folder Options, and uncheck 'hide file extensions for known file types.' This will allow you to see the file extensions of your data files. Some data files have the extension .csv and some have the extension .txt. Usually .txt files contain the ticker as a data field, and contain data for more than one ticker in a single file; and usually the .csv files do not include the ticker, and have the ticker name as filename with a separate file for each ticker. When you 'Browse to a new file' from GoldenGem, you will almost always be looking for a .txt file, whereas if you 'Browse for a folder' you will be looking for a folder of .csv files. This use of extensions is not always followed, and an exception is that if you take data from the Bank of England, choosing Columnar you will find yourself, unusually, downloading a .csv file which contains the ticker in column 2 and contains data for many variables in one single file. To open a columnar Bank of England file using GoldenGem, then, you would select 'Browse for new file' and then it is not hard to figure out what to do next: you need to change the file type in the bottom of the window where files are opened from .txt to .csv in order to be able to see the filename and click on it.

Including tickers without volumes in a .txt file

If you use data which does not have an associated volume, the number for the volume column can remain at the default value of 7. Then just be sure your data file has no more than 6 columns, or, if it has a seventh column just place 0 in each entry, or set the vol number to a higher number than any existing column number, or leave the vol number blank.

9. Standard conventions for AOL and Metastock:


Ticker
column 1
Date
column 3
Close
column 7
Volume
column 8


10. Quicken:

Ticker
column 1
Date
column 3
Close
column 2
Volume
column 6


and browse to the folder of .csv files.

return to main page