Quicken QIF files to Power Query

rasinc · Jun 1, 2024

I have a client wanting a spreadsheet that can take a quicken qif export file from their investment account and analyse it in Excel. I asked about Power BI and they are looking into it but right now just in Excel for more extensive graphing.

I can import the file and see the data, etc. Each row in the file is identified by a single character that indicates the type of data (eg. Y = Name of Company/Investment, D = date, N = Transaction Type).

Each transaction record can span 4-8 rows or so. Each record is ended with a ^ as the only character on the line.

For an initial step I have promoted the !Type:Invst row to a column header and split the first character off into it's own column.

I would like to take the initial character column and somehow pivot those into column headers. Ideally with each series of rows that make up one record will show up as one row under the proper columns. Not all column headers are in each record.

I tried to use the code from *AlienSX and alansidman in the post here but this code appears to assume the same number of rows for each record and instead of putting null in when column data doesn't exist, it combines the rows from records below with those above when they are missing.

QIF Data will look something like:
!Type:Invst
D12/14' 9 - note this is Dec 14, 2009
NBuy
YRoyal Bank
I124.55 - unit price
Q10
U1255.45
T1255.45
O9.95
^
D2/ 9'21
NContribX
U6,000.00
T6,000.00
M2021 Contribution
L[Savings]
$6,000.00
^
D2/15'24
NDiv
YRoyal Bank
U48.14
T48.14
^

for three records. After the ^, it just starts at the D row again.
The D, N, Y, I Q, U, T, and O should be the column headers and any others that show up in other records. The are in their own column right now. Occasionally I see an M for Memo and L row but not in all records (see middle transaction above).

Can anyone point me in the right direction? I was going to look at using VBA to parse the data but thought I would try Power Query first because I am trying to learn how to use it properly. Right now I only have one sample file of about 6000 records for about 10 years in one investment account but I am told there are 20 years of data he is looking at using from about 8 different accounts.

TIA rasinc

rasinc · Jun 2, 2024

PowerQueryPortfolioAnalysis.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Column

D

N

Y

I

Q

U

T

O

M

L

$

2

!Type:Invst

2/ 9'21

ContribX

6,000.00

2021 Contribution

[Savings]

6000

3

D2/ 9'21

12/14' 9

Buy

Royal Bank

124.55

10

1255.45

9.95

4

NContribX

12/14' 9

Div

Royal Bank

48.14

5

U6,000.00

6

T6,000.00

7

M2021 Contribution

8

L[Savings]

9

$6000

10

^

11

D12/14' 9

12

NBuy

13

YRoyal Bank

14

I124.55

15

Q10

16

U1255.45

17

T1255.45

18

O9.95

19

^

20

D12/14' 9

21

NDiv

22

YRoyal Bank

23

U48.14

24

T48.14

25

^

Table4

Above is some data and sample output. The code I've tried does not match all record lines to the correct fields within the same record. I believe the reason is because it assumes there is only one transaction per date. However, there can be many transactions per date, especially for day traders.

I've been able to assign a record number (though not incremental) to all lines of each record. I think I have now combined this with the methods you suggested and after some initial testing, I think it is working. This code below pulls the data directly from a qif file exported from Quicken, not a table imported into Excel. I've got more work to do to learn about how the grouping of columns work. I think this was my stumbling block. Thank you for showing me how to use it in this scenario. I originally thought it was to aggregate data but didn't realise it has more to it.

Power Query:

let
    Source = Csv.Document(File.Contents("Path to Qif file.QIF"),[Delimiter="#(tab)", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"!Type:Invst", type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type1", "!Type:Invst", Splitter.SplitTextByPositions({0, 1}, false), {"!Type:Invst.1", "!Type:Invst.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"!Type:Invst.1", type text}, {"!Type:Invst.2", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type2", "Index", 1, 1, Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "RecordNo", each if [#"!Type:Invst.1"] = "D" then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"RecordNo"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"RecordNo"}, {{"Count", each _, type table [#"!Type:Invst.1"=nullable text, #"!Type:Invst.2"=nullable text, RecordNo=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index", 1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"!Type:Invst.1", "!Type:Invst.2", "RecordNo", "Index"}, {"Custom.!Type:Invst.1", "Custom.!Type:Invst.2", "Custom.RecordNo", "Custom.Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Custom.Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"Custom.!Type:Invst.1"]), "Custom.!Type:Invst.1", "Custom.!Type:Invst.2"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Custom.RecordNo", "RecordNo"}, {"D", "Date"}, {"N", "Transaction"}, {"U", "Amount"}, {"T", "Amount2"}, {"M", "Memo"}, {"L", "Account"}, {"$", "Contribution"}, {"Y", "Investment"}, {"I", "SharePrice"}, {"Q", "Qty"}, {"O", "Commission"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","' ","/200",Replacer.ReplaceText,{"Date"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","/ ","/0",Replacer.ReplaceText,{"Date"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","'","/20",Replacer.ReplaceText,{"Date"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value2",{{"Date", type date}, {"Amount", Currency.Type}, {"Amount2", Currency.Type}, {"Contribution", Currency.Type}, {"SharePrice", type number},  {"Qty", type number}, {"Commission", Currency.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type3", "Qty2", each if [Transaction] = "Sell" then [Qty] * -1 else [Qty]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"RecordNo", "Date", "Transaction", "Amount", "Amount2", "Memo", "Account", "Contribution", "^", "Investment", "SharePrice", "Qty", "Qty2", "Commission"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns",{"Qty"})
in
    #"Removed Columns2"

AlienSx · Jun 2, 2024

Hello, @rasinc . Try this after "promote headers" step

Power Query:

    split = Table.SplitColumn(
        #"Promoted Headers", 
        "!Type:Invst",  
        Splitter.SplitTextByPositions({0, 1}), 
        {"cat", "value"}, 
        null
    ), 
    group = Table.Group(
        Table.ReverseRows(split), 
        "cat", 
        {"x", (x) => Table.Pivot(Table.Skip(x), List.Skip(List.Distinct(x[cat])), "cat", "value")}, 
        GroupKind.Local,
        (s, c) => Number.From(c = "^")
    ),
    cmb = Table.Combine(group[x])

and rename columns to your liking. It can be done easily if you have a mapping table like "char, new_name"

smozgur · Jun 2, 2024

An Alternative version with some manual M coding:

Power Query:

let
    // I used table data to help future readers - Replace the source line in your computer
    // Source = Csv.Document(File.Contents("Path to Qif file.QIF"),[Delimiter="#(tab)", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY3LCsIwEEV/JRaXJU6GRI3L2k3BB2oLSqxQQSQoqdgH9O+ViaCC4O6ey8w9xgS9tLudJolrqzrIQxPEOGB63wDgCAU1i2np6rs9bomyYQgAHIAo/aI5Agr2Om9qWzqqZ2ZTtNadq5ywTz+M8sE7BQ6E9FKmvTRqOgq7ddkVVxYV7kKcCJRcKcor4b2ZQKW49GX6CUvNtfpjim3705TJMRfSb77jcyd/AA==", BinaryEncoding.Base64), Compression.Deflate))),

    RemoveTypeRow = Table.Skip(Source,1),
    SplitColumn = Table.SplitColumn(RemoveTypeRow, "Column1", Splitter.SplitTextByPositions({0, 1}, false), {"Key", "Value"}),
    Index = Table.AddIndexColumn(SplitColumn, "Index", 0, 1, Int64.Type),
    SeparatorKey = "^",
    GroupIndex = Table.AddColumn(Index, "Group Index", each if [Key] = SeparatorKey then 1 else 0),
    BufferedGroupIndex = List.Buffer(GroupIndex[Group Index]),

    GrouppedIndex = Table.FromRecords(Table.TransformRows(GroupIndex,
                        (r) => Record.TransformFields(r, {"Group Index",
                                        each if r[Key] = SeparatorKey then null else _ + List.Sum(List.FirstN(BufferedGroupIndex, r[Index] + 1)) }))),
    RemoveSeparators = Table.SelectRows(GrouppedIndex, each ([Group Index] <> null)),
    ColumnNames = List.Buffer(List.Distinct(RemoveSeparators[Key])),
    Groups = Table.Group(RemoveSeparators, {"Group Index"}, {{"Groups", each _}}),
    RemoveIndexes = Table.TransformColumns(Groups, {"Groups", each Table.SelectColumns(_, {"Key", "Value"}) }),
    PivotTables = Table.TransformColumns(RemoveIndexes, {"Groups", each Table.Pivot(_, List.Distinct(_[Key]), "Key", "Value")}),
    ExpandedGroups = Table.ExpandTableColumn(PivotTables, "Groups", ColumnNames),
    Result = Table.RemoveColumns(ExpandedGroups,{"Group Index"})
in
    Result



D	N	U	T	M	L	$	Y	I	Q	O
2/ 9'21	ContribX	6,000.00	6,000.00	2021 Contribution	[Savings]	6,000
12/14' 9	Buy	1255.45	1255.45				Royal Bank	124.55	10	9.95
12/14' 9	Div	48.14	48.14				Royal Bank

Column orders need to be changed as you would need.

Alternatively, the following field listing for Investment Acccounts could be used as well:

Items for Investment Accounts
Field Indicator Explanation

D Date
N Action
Y Security
I Price
Q Quantity (number of shares or split ratio)
T Transaction amount
C Cleared status
P Text in the first line for transfers and reminders
M Memo
O Commission
L Account for the transfer
$ Amount transferred

In this case, the code should be modified as shown below:

Power Query:

let
    // I used table data to help future readers - Replace the source line in your computer
    // Source = Csv.Document(File.Contents("Path to Qif file.QIF"),[Delimiter="#(tab)", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY3LCsIwEEV/JRaXJU6GRI3L2k3BB2oLSqxQQSQoqdgH9O+ViaCC4O6ey8w9xgS9tLudJolrqzrIQxPEOGB63wDgCAU1i2np6rs9bomyYQgAHIAo/aI5Agr2Om9qWzqqZ2ZTtNadq5ywTz+M8sE7BQ6E9FKmvTRqOgq7ddkVVxYV7kKcCJRcKcor4b2ZQKW49GX6CUvNtfpjim3705TJMRfSb77jcyd/AA==", BinaryEncoding.Base64), Compression.Deflate))),

    RemoveTypeRow = Table.Skip(Source,1),
    SplitColumn = Table.SplitColumn(RemoveTypeRow, "Column1", Splitter.SplitTextByPositions({0, 1}, false), {"Key", "Value"}),
    Index = Table.AddIndexColumn(SplitColumn, "Index", 0, 1, Int64.Type),
    SeparatorKey = "^",
    GroupIndex = Table.AddColumn(Index, "Group Index", each if [Key] = SeparatorKey then 1 else 0),
    BufferedGroupIndex = List.Buffer(GroupIndex[Group Index]),

    GrouppedIndex = Table.FromRecords(Table.TransformRows(GroupIndex,
                        (r) => Record.TransformFields(r, {"Group Index",
                                        each if r[Key] = SeparatorKey then null else _ + List.Sum(List.FirstN(BufferedGroupIndex, r[Index] + 1)) }))),
    RemoveSeparators = Table.SelectRows(GrouppedIndex, each ([Group Index] <> null)),

    // Fields for Investment Accounts. The second column is only for information. 
    // Only the field codes can be used as a list.
    Fields = #table({"Field", "Description"},
                {
                    {"D","Date"},
                    {"N","Action"},
                    {"Y","Security"},
                    {"I","Price"},
                    {"Q","Quantity (number of shares or split ratio)"},
                    {"T","Transaction amount"},
                    {"C","Cleared status"},
                    {"P","Text in the first line for transfers and reminders"},
                    {"M","Memo"},
                    {"O","Commission"},
                    {"L","Account for the transfer"},
                    {"$","Amount transferred"}
                }
            ),
    // ColumnNames = List.Buffer(List.Distinct(RemoveSeparators[Key])),
    ColumnNames = Fields[Field],

    Groups = Table.Group(RemoveSeparators, {"Group Index"}, {{"Groups", each _}}),
    RemoveIndexes = Table.TransformColumns(Groups, {"Groups", each Table.SelectColumns(_, {"Key", "Value"}) }),
    PivotTables = Table.TransformColumns(RemoveIndexes, {"Groups", each Table.Pivot(_, List.Distinct(_[Key]), "Key", "Value")}),
    ExpandedGroups = Table.ExpandTableColumn(PivotTables, "Groups", ColumnNames),
    Result = Table.RemoveColumns(ExpandedGroups,{"Group Index"})
in
    Result



D	N	Y	I	Q	T	C	P	M	O	L	$
2/ 9'21	ContribX				6,000.00			2021 Contribution		[Savings]	6,000
12/14' 9	Buy	Royal Bank	124.55	10	1255.45				9.95
12/14' 9	Div	Royal Bank			48.14

You'll notice there are some blank columns in this version since we are now using all possible fields in the QIF data.

rasinc · Jun 2, 2024

@AlienSx and @smozgur thank you for the additional code. I really didn't expect to see so much interest in this one, especially on the weekend. However I am very happy with the alternatives. There is so much in each post that I am going to have to take some time to figure out why it is working as it is. I've done some initial testing with each of your code, and they all seem to be giving me the results I need using different steps. Thank you for the help, I have some learning to do.

@smozgur you were correct, the P column in my client's dataset does not have any data, so the entire column is null but it does show up.

smozgur · Jun 3, 2024

AlienSx said:

Power Query:

    split = Table.SplitColumn(
        #"Promoted Headers", 
        "!Type:Invst",  
        Splitter.SplitTextByPositions({0, 1}), 
        {"cat", "value"}, 
        null
    ), 
    group = Table.Group(
        Table.ReverseRows(split), 
        "cat", 
        {"x", (x) => Table.Pivot(Table.Skip(x), List.Skip(List.Distinct(x[cat])), "cat", "value")}, 
        GroupKind.Local,
        (s, c) => Number.From(c = "^")
    ),
    cmb = Table.Combine(group[x])

@AlienSx - this is brilliant! Thank you!

Quicken QIF files to Power Query

rasinc

Board Regular

rasinc

Board Regular

Excel Facts

AlienSx

New Member

smozgur

BatCoder

rasinc

Board Regular

smozgur

BatCoder

Similar threads

Forum statistics

Share this page

Quicken QIF files to Power Query

rasinc

Board Regular

rasinc

Board Regular

Excel Facts

AlienSx

New Member

smozgur

BatCoder

rasinc

Board Regular

smozgur

BatCoder

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock