![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
If your financial software does not properly support downloading investment transactions, you can use QIF Wizard to extract QIF from your investment firm's transaction history web page and import that QIF into your financial software. Investment information is not typically produced in QIF format by the investment firms; to get QIF you may need to use QIF Wizard to extract the transactions from the web site and convert them into QIF. To use QIF Wizard in this manner, you copy the transactions from the web site into the clipboard and then have QIF Wizard extract QIF from them. If you prefer, you can put the contents of the clipboard into a file and process that file. To be able to accomplish this extraction, QIF Wizard needs to learn the format your investment website uses for its transaction history display. QIF Wizard has the ability to learn about a wide range of websites. However, no guarantee can be made that any particular website can be learned. Since different browsers encode web page information copied into the clipboard in different ways, the specific web browser used is a factor in determining whether QIF Wizard can learn a particular website. QIF Wizard works with most web browsers. QIF Wizard cannot learn or extract from websites that:
Click here for more information on defining the format of investment transactions. Click here for more information on extracting QIF from an investment website transaction history once its format has been defined. Click here for information on how to edit a format once you have defined it. |
To be able to extract transactions from text, you must teach QIF Wizard about the format of the transactions in the text. The format depends on 1) the way the website displays the transactions and 2) the way the web browser puts selected information into the clipboard or file, or the format of CSV produced. Therefore, if you use the same web site with two different browsers, you will need to define two formats. In general, you need to define a format for each website/browser combination.
To define a format:
Here is an example Control dialog after a format name has been entered.
When you hit return, the following dialog appears.
This dialog contains example text from the clipboard for a hypothetical brokerage firm. You specify four items in this dialog. The first is the type of transactions, in this case Investment. Second, you specify some text from the input that uniquely identifies this text format. In the example above the account number was selected. The account number makes sense if you process only one account from this brokerage firm. If you process multiple accounts, you might want to select some other text that would be unique to this website, like "Review account transactions". QIF Wizard will use this text to guess which format applies to the text in the clipboard when you specify Process Clipboard. This guess does not have to be perfect because you always have the opportunity to select the proper format. The third item you specify is whether the first line contains a transaction, which it does not in this case. The fourth item you specify is the date in the first transaction in the clipboard. In many cases there are other dates in the clipboard. In the example above, "January 1,2000" appears in the clipboard, but does not start a transaction. In this case, the input contains dates with the month followed by the day. If however, the dates in the input have day before month (such that 10/12/2002 means December 10, 2002), then you would check the "Interpret dates with day before month" option. Note that this option specifies only how dates in the input are interpreted, it does not control how dates are output by QIF Wizard. Dates are always output in the format mm/dd/yyyy. When you press NEXT in this dialog, the following one appears.
From this point forward in the format definition process, you can
use the "Back" button to go back one screen in case you answered
on of the questions wrong.
This is a very important dialog. You need to first make sure that each row in the table contains a single transaction. You should experiment with the setting of the Assume each transaction is on a separate line checkbox to make sure there is one transaction per line. In this example you leave the box unchecked. If there is more than one date in each transaction (for example a trade date and a settlement date) you would want to check this box to be able to choose which date you would want to use. You then need to choose a method to separate fields that replicates the fields displayed in your web browser. It is important that all transactions (rows) be divided into fields in the same and correct manner. If the field division does not look right when this dialog appears, try the other methods and click NEXT when you have chosen the best method. Don't be surprised of multiple methods yield the same results. In that case, choose any with the correct result. Sometimes non-US characters can confuse the splitting into fields, so you can check the Text contains non-US characters box to see if that corrects the columns. If no method divides the fields properly, then QIF Wizard will not be able to extract from the web site with the browser you used. Try another browser. When you click NEXT, the following dialog appears.
This dialog merely contains instructions. If the column widths don't allow you to see all of each column, you can adjust the widths here, or in the dialogs that follow. When you click NEXT, the following dialog appears.
Starting with this dialog, you are asked a series of questions for each investment transaction field: transaction type, security symbol, security name, amount, number of shares, price. The specific questions you are asked depends on the answer to your questions. Your transactions do not have to contain all of these types of fields. Some brokerages provide history transactions on a per-security basis. In other words, the name of the security never appears in the transactions themselves. If this case applies to you, click here to see how QIF Wizard handles this situation. Had you checked the Assume each transaction is on a separate line box you would be asked which column contains the date. Otherwise QIF Wizard assumes that the first column contains the date.
In the case above, you would click on YES because there is a transaction type, in this case, BUY, SELL, or CHECK RECEIVED. When you click on YES, the following dialog appears.
In this case, the transaction type starts at the beginning of the third field, so you would click on YES, yielding the following dialog.
You would then click on the column header above the third column. When you click there, the following dialog appears.
Note that "Type" has been placed above the field you selected. This dialog starts the questions about the security symbol. The security symbol is not the same as the security name. Some brokerages include only the symbol in their transactions, some only the name, and some both. It does not matter that the name your brokerage uses is not exactly the same as the name you called the security in your financial software. You will associate the two names later. In this case only a security symbol. So you would click on YES in this example, and get the following dialog.
In this case the symbol does start any field. It is in field 3, but not at the start, so you would click NO, yielding the following dialog.
Because you have told QIF Wizard that the symbol is present, but not at the start of a field, QIF Wizard asks you about other ways to find the symbol. In this case, you are asked whether the same string of characters precedes each security symbol. In this example, "SHARES OF" precedes each symbol, so you would click YES, yielding the following dialog.
Here, you would select "SHARES OF" in any transaction. To do this, double click on the "BUY 100 SHARES OF AAPL @ 15.25" fields, and then you will be able to select specific characters within that field. Then click NEXT. The following dialog then appears.
Notice that the location of the symbol you specified is indicated
in the dialog.
Now you are asked about the security name, but there is none, so you would click NO. The following dialog then appears.
You would click YES because there is a single Amount column. The result would be this dialog.
Click YES here because the amount starts field 2.
Click the column 2 header here just as you selected the "Type" field before. The result is the following dialog.
Note that "Amount" has been placed above field 2. Click YES because the number of shares appears in field 3.
Click NO here, since the number of shares is within the field, not at its start.
In one transaction, the number of shares is preceded by "BUY", and in another by "SELL". So you would click NO here.
Note that the number of shares is always followed by "SHARES", so you would answer this questions by clicking YES, yielding the following dialog.
Here you would select "SHARES" in any transaction, just as you selected "SHARES OF" before.
You would click YES here because there is a price in field 3.
You would click NO here because the price is within field 3, not at its start.
In this case, the price is preceded by "@", so you would click YES.
Here you would select the "@" in any transaction and click NEXT.
Click NEXT because there is no memo.
Click YES if you are using a version of Quicken or most other Mac financial software that supports QIF Check/Deposit/Withdrawal/ATM/POS transactions.
Click NEXT because there is no description (the third column looks like a description, but was used to convey the type of transaction.
Click NO because there is no check number.
Click NEXT because there is no category.
At this point you have answered all of the relevant questions, so you are done.
Some brokerages provide history transactions on a per-security basis. In other words, the name of the security never appears in the transactions themselves. In defining the format for such transactions, you would indicate that the transactions contain neither a security symbol nor a security name. In this case, QIF Wizard will prompt you to specify the security that applies implicitly to all transactions with the following dialog.
While entering the security, the dialog will autocomplete the security name you start typing from the list of securities you imported from your financial software. You can also "scroll" through the list of securities by using the up and down cursor keys.
If you process a QFX or OFX file that contains investment transactions, the control dialog will be similar to following.
When you process the transactions, QIF Wizard will operate in a manner identical to extracted investment transactions.
At this time, only Buy, Sell, Reinvest, Transfer, Income, Return Of Capital, Margin Interest, and Interest Expense QFX/OFX investment transactions are supported. Other transaction types are silently ignored.
QIF Wizard allows you to confirm each investment transaction that has been extracted from text. You cannot avoid confirming each extracted transaction, because it is possible that an erroneous transaction could be extracted. This does not usually occur, but could occur if the text contains a date that precedes the first date that starts a transaction, or if the format was defined incorrectly, or the website does not present transactions in a consistent manner.
You will be asked to supply any additional information that QIF Wizard needs to interpret the transactions. Specifically, QIF Wizard needs to understand how the transaction types in the transactions correspond to the transactions that your financial software understands. Furthermore, QIF Wizard needs to understand how the security symbols or security names in the transactions correspond to the securities in your financial software. For more information about how QIF Wizard prompts for the meaning of transaction types, click here. For more information about how QIF Wizard prompts for the meaning of securities, click here.
Assuming that the transaction type and security of a transaction are understood by QIF Wizard, the investment transaction confirmation dialog would look as follows.
The portion of the dialog above the first line shows the values that QIF Wizard has extracted from the text. Items in parentheses are the QIF type and security that QIF Wizard will use based on the type and security in the transaction itself.
It has also added the "Fees" item because the amount in the transaction did not equal shares times the price. The warning below the first line explains how the fees were computed. The warning also mentions a case that can occur when reporting bond transactions. Bond are usually purchased in lots of 100 bonds, and the number of shares reported are in total number of bonds, but the price reported is for a lot of 100. Therefore, to properly enter the bond transaction into your financial software, you must record the reported shares divided by 100 in your financial software. The checkbox performs this adjustment. If QIF Wizard thinks this is a bond transaction, it checks the box automatically. Of course, you can check or uncheck the box as you see fit.
The memo field is automatically filled in with a copy of the type field, but you can change this to anything you want.
As mentioned above, this example assumes that the transactions type is already understood. If however, you realize that you defined the transaction type incorrectly in the past, you can redefine the transaction type by selecting the "Redefine Type" button. This button immediately "forgets" the previous type definition that matched this transaction, and allows you to define a new type.
As mentioned above, this example assumes that the security is already understood. If however, you realize that you defined the security incorrectly in the past, you can redefine the security by selecting the "Redefine Security" button. This button immediately "forgets" the previous security definition that matched this transaction, and allows you to define a new security. This button appears only if the security in the transactions was recognized because you previous told QIF Wizard how to recognize the security. If the security in a transaction has a name identical to a security in your financial software (based on the securities you export) or if the security matches a security symbol, this button will not appear.
Below the second line you have 5 options. You can extract the transaction and place it in the output file. This is the default if you hit Return. You can click Cancel to stop processing the text in the clipboard completely, leaving the output file unchanged. You can also skip this transaction and proceed to the next one, or you can request that transactions matching this type always be skipped. Finally you can click "Edit Input", which allows you to edit the input data for this transaction. This option will be explained below.
You can adjust the date by clicking the control in front of Date. If you do adjust the date, the dialog changes to indicate both the original date and the adjusted date, as shown below.
If you are confirming all transactions you can go back to previous transactions by clicking the Back button. This button appears only if you are confirming all transactions, and is dimmed for the first transaction because there is nothing to go back to. The Back button also appears in the Create Instruction dialog.
There are rare instances when a web site might produce transaction output that puts certain values in the wrong column for certain transaction types. In the example below, the amount ($12.34) was put in the price column. Thus, the transaction had no amount nor number of shares. All transactions must have either an amount or a number of shares, so this transaction cannot be assigned a type.
If you click the "Edit Input" button, the result is shown below.
The resulting sheet that comes down shows all of the input data that QIF Wizard extracted for this transaction, and allows you to change any value. Since the $12.34 should really be the amount, you can change the amount to $12.34, and change the price to 0 or blank. After doing so and clicking NEXT, the following dialog results.
Because the input now contains an amount, you can define a type. Note that in this case the type itself is blank. This is NEXT, because you can associate a type with a blank type.
When QIF Wizard encounters a transaction type it does not understand, it prompts for with the following dialog.
The portion of the dialog above the first line shows the values that QIF Wizard has extracted from the text. In the example above, QIF Wizard understands the security symbol "AAPL", and has signaled this by putting the name your financial software knows in parentheses "(Apple Computer)".
Below the
line, you
are told that the transaction type is not understood. In the text
box, you can specify the text that QIF Wizard must match to find
this transaction type. In this example you would shorten the text
shown to "BUY". If you did not shorten the text, then
you would be prompted each time QIF Wizard encountered a BUY
transaction
for any stock. In this example we will leave "Assume security"
unchecked. More on this later. Finally, you would pull down the
menu to choose the appropriate transaction type. The pull
down menu is shown at the right. It contains all transaction types
that QIF can handle. When you pull down this menu, you will
be allowed to select only those types that make sense given the
information extracted from the text. More on this below. In the
above example, you would choose "Buy" as the
type.
If you did not check the Allowing banking transactions within investment QIF preference, the types starting with "Return of Capital" will be dimmed, and you cannot use them.
The dialog below shows a different example
of defining a type.
This example shows the case where a brokerage firm has a "core" account that holds all free cash in the account. Often, brokerages with core accounts do not include the name of the fund that holds the core funds in transactions involving the core account. QIF Wizard has specific accommodations for this case. In the dialog above, note that the transaction type is "CHECK RECEIVED". It is assumed in this example that you intend the meaning of this transaction to be that the money received goes directly into the core account. If you were to pull down the menu, you would find that the only non-dimmed choices are "Miscellaneous Income" and Miscellaneous Expense". Those are the only transactions that can be handled without a security or price. Because no prices was found in the transaction, the "Assume Share Price of $1.00" checkbox appears in the dialog. Core accounts typically maintain a share price of $1.00. So you would check this box in this example. you would also check the "Assume security" box to allow you to specify the name of the security that holds the core account funds. The result would be the following dialog.
Note that now a price and number of shares now appears above the line. Also, after you enter a security, the pull down QIF type menu would now have all QIF types available. In this example you would choose "Move Shares In". Choosing "Buy", which you might have considered, would take $1000 out of the account's cash. "Move Shares In" does not change the cash. The result is as follows.
At this point you can click "Define Type" to associate "Move Shares In" with the type "CHECK RECEIVED" for this and future transactions. If you click "Use Type Here Only" then "Move Shares In" will be associated for this transaction only.
If your transactions contain a security symbol, QIF Wizard will automatically match that to the security that your financial software knows with the same symbol. This is possible only if you fill in the security symbol information in your financial software. If you don't, or if your transactions contain only a security name, then QIF Wizard will need to know by what name your financial software the security in the transaction. If there is a security defined in your financial software that exactly matches a security name in the transaction, that security will automatically be used. If not, QIF Wizard display the following dialog to ask you what security to use.
The portion of the dialog above the first line shows the values that QIF Wizard has extracted from the text. In the example above, QIF Wizard understands the transaction type, and has signaled this by putting the QIF type in parentheses "(Buy)".
Below the line, you are told that the security is not understood. In the text box, you can specify the security to be associated with the symbol "AAPL".