Tutorial: Importing Data
In this tutorial, we look at the different ways of importing data into ShareScope. This may be useful if, for example, you have holdings which are not listed in the program (e.g. overseas instruments) but wish to add them to ShareScope portfolios.
This is particularly useful if you have the price data contained within another program e.g. Excel and want to transfer it into ShareScope.
There are three methods of importing data covered in this tutorial:
Accepted File Types
ShareScope can import a number of different file types. We will focus on the two most commonly used file types within this tutorial: Excel (.csv) files and Notepad (.txt) files.
Excel CSV files
ShareScope will only be able to import a file if the information within is correctly formatted. The example below shows the possible fundamentals that can be imported and the correct formatting within an Excel .csv file:
| Share name | Can be any text but do not use a colon, comma or any character used as a field separator. |
|---|---|
| Exchange Market Size (EMS) | Any number rounded to the nearest half e.g. 20.7 would be entered as 20.5 |
| Type | Choose from ord, warrant, ADR, convertible, preference, zero preference, index, capital, income, gilt, rate, bond, ETF. Abbreviations may be used instead e.g. ord, war, ADR, con, pre, zer, ind, cap, inc, gil, rat, bon, ETF |
| Listing | Choose from Full, AIM, Index, Other |
| Currency | Any currency name or currency code as listed in Tools: Options: Currencies |
| Number of shares in issue | Any number |
| Event type | Choose from making offer, rights or suspended |
Actually, you do not need any information other than the dates and prices, the rest is optional. So the Excel file could look like this:
Date formatting
ShareScope can recognise a number of different date formats. Useful if your Excel file contains US data using the US date format e.g. MM/DD/YY. Just tell ShareScope what date format to use when importing the file. (We will cover this part of the process later in this tutorial).
Price formatting
Dates must be in order with the oldest date first.
Three-letter months are supported e.g. 30/Mar/14, to allow data to be imported from a variety of sources, such as Yahoo Finance.
Prices are any number with a maximum of five digits with two decimal places, typically the format might be 107 or 23.25.
Do not use a comma in larger numbers (i.e. "two thousand" should be expressed as 2000 - not 2,000).
Importing OHLCV data
If there are fewer than 4 columns shown to the right of the date, the first column will be read as the Close, and the other columns ignored.
If there are 4 columns to the right of the date, these will be read as Open, High, Low and Close (in that order).
If there are 5 or more columns to the right of the date, these will be read as Open, High, Low, Close and Volume (in that order). Any extra data will be ignored.
Here's an example of a CSV file showing OHLCV data:
Notepad files
The formatting for the share fundamentals is exactly the same as required in a CSV file however the date and price formatting requirements differ in Notepad.
As you can see the date and price is separated by a semi-colon (;). Here's an example where OHLCV data is to be imported:
Note: The separator used in this example is a semi-colon by choice; you can in fact choose any character to act as a column separator. Avoid using alphanumerical characters as these will likely be in use within the share fundamentals, dates or prices.
Now you have a file that is ready to be imported in to ShareScope.
Import a Share
Importing a CSV file
To import the file, go to the File menu, Import, then Import a Share.
The file browser will appear, select the file you wish to import and then click Open. The Import Preview dialog will be displayed.
Items that appear in red are not in a recognised format. In this example, ShareScope does not recognise the four digit year. This is easily remedied by ticking the box next to Four digit year.
| Other options | |
|---|---|
| Use file name for share name | If this box is unticked, ShareScope will use the name as entered within the CSV or text file. In the event that there is not a name within the file, you are prompted to name the share. |
| Use file name for share's EPIC | Assigns an EPIC code to the imported share. |
| Allow comma as decimal point | Useful when importing from a data source which uses commas in place of decimals e.g. European data sources. |
| Prices in major currency | Tick this box to have the prices imported in the major currency e.g. pounds or dollars. |
Overwrite rules | |
| Append if share exists | Tick this box to update an existing imported share. |
| Replace data for matching dates | Tick this box to update already existing price history for the imported share. |
When importing a CSV file, the field separator is greyed out and cannot be changed. CSV is an acronym for comma-separated values hence the field separator is fixed as a comma.
Importing a TXT (Notepad) file
Importing a txt file only differs from importing a CSV file in that you can define the field separator being used.
In the example Notepad file shown earlier in the tutorial, semi-colon (;) had been used as the field separator. This must be entered to allow ShareScope to recognise the format of the data being imported.
Import all shares in a folder
In the event that you have several files you would like to import, you can save them all to a single folder. This then allows all the files to imported in one go.
Here I have two CSV files in a folder and I wish to import them both.
Go to File: Import: Import all files in a folder. Use the Browse button to locate and select the folder. Import file shows csv (change to txt if you are importing Notepad files).
Clicking on OK takes you to the Import Preview dialog. Use the Import Preview to ensure ShareScope can understand the format of the data.
All the csv files in the folder will be imported.
Import all shares in a file
It is possible to import prices for multiple shares via a single csv or Notepad file. This is useful if you wish to append a number of existing imported shares.
Once again there are specific formatting rules.
Each line must start with the share's name followed by the date.
Following the date column:
If there are fewer than 4 columns shown to the right of the date, the first column will be read as the Close, and the other columns ignored.
If there are 4 columns to the right of the date, these will be read as Open, High, Low and Close (in that order).
If there are 5 or more columns to the right of the date, these will be read as Open, High, Low, Close and Volume (in that order). Any extra data will be ignored.
Here's an example of an Excel csv file:
Here's the same data formatted as a Notepad txt file.
Remember, if you have any trouble finding or using any of these features, please don't hesitate to contact our Customer Support team. They will be delighted to help.