Tutorial: Import Stock Transactions from Google Sheets
This tutorial explains how to import your stock transactions from a Google Sheets spreadsheet. You can use the import functionality to import large transaction histories as well as previously created backups.
Let’s start:
- Open the menu drawer by tapping on the burger menu in the upper left corner or swiping from left to right over the screen
- Now, open the “Import…” menu.
- The 22 Dividends app expects the transaction data in the spreadsheet to be in a certain format. To simplify the this process the app can create an empty spreadsheet template that you can fill out. After you entered the data you can import it in a second step.
Create Empty Import Template
- Tap on “Create empty import template” and login to your google account.
- The app creates a spreadsheet called “22 Dividends – Import“.
- As soon as the process finished the app opens a result screen that allows you to copy or share the URL of the newly created spreadsheet.
- The spreadsheet contains one entry for each stock position that you created in your app. Just delete the rows you don’t need.
- Close the dialog.
- You can also open your spreadsheets in the browser: Google Sheets
Entering Stock Transaction Data
Now, that you created an empty import template you can fill-in your transaction data. Each column heading of the template has a hint that provides you examples for entering the transaction data.
Here a description of the columns:
1. Symbol
The ticker symbol of the stock as used by the stock exchange. For example, “AAPL” for apple.
Hint: You can use the 22 Dividend app’s search function to search the symbol of the stock you want to import.
2. Exchange
The symbol of the stock exchange. Following stock exchanges are supported:
- Stock Exchange Symbol: The list of supported stock exchanges can be found in the FAQ. Here are two examples:
Symbols | Name | Country Code |
NYS, NYSE, XNYS | New York Stock Exchange | US |
NAS, NASDAQ, XNAS | NASDAQ | US |
- Country Code: To simplify this process you can also use the country code of the stock exchange instead of the stock exchange itself (e.g. “US” for United States of America).
It is possible to mix stock exchange symbols in the import spreadsheet: e.g. AAPL.NAS, AAPL.XNAS, and AAPL.US are all valid and mapped to the same stock.
3. Type
The type of transaction: either “BUY” or “SELL“.
4. Shares
The number of shares of the transaction: e.g. “3.0”
5. Prices
The price per share of the buy or sell transaction in the currency of the stock exchange: e.g. “104.20”
6. Costs
The costs of your broker for executing the transaction in the currency of the stock exchange: e.g. “2.0”
7. Date
The date the transaction was executed. The date has following format:
Format | Example |
YYYY-MM-DD | 2019-01-24 |
YYYY-MM-DD hh:mm | 2020-10-05 13:45 |
Important: If you have multiple transactions for the same stock you have to specify date and time for each transaction. Otherwise, the app is not able to recognize the transaction order. If you have conflicting transactions in your spreadsheet the import process skips the inconsistent transactions. Moreover, the new transactions will also be checked against the transactions you already have in your app.
Import Spreadsheet
After you filled-out the import template you can import it into the 22 Dividends app.
- Open the menu drawer by clicking in the upper left corner or swiping from left to right
- Now, open the “Import…” menu.
- Select “Import data from filled-out template…”
- Login to your Google Sheets account
- Now, you see a list of the spreadsheets in your account. Select the one you want to import.
Hint: You can re-import the same spreadsheet over and over again. Already existing transactions are skipped.
After the import process finished you will see a summary of the imported transactions. The screen lists all skipped transactions and transactions with errors.
Now, you have imported all transaction data you app. You can also export the transactions to a Google Sheets spreadsheet using the exporter.