Quicken QIF files to Power Query

[rasinc]

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

 

[alansidman]

Would you repost your sample data using XL2BB so that the formatting is known correctly and we don't have to reenter the data and guess at the formatting. Also, then for the three records, mock up how you would like the data shown after cleansing.

 

[alansidman]

If this is what you expect your data to look like then

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	!Type:Invst		Index	D	N	Y	I	Q	U	T	O	M	L	$
2	D12/14' 9		1	12/14' 9	Buy	Royal Bank	124.55 - unit price	10	1255.45	1255.45	9.95	2021 Contribution	[Savings]	6000
3	NBuy		2	2/ 9'21	ContribX	Royal Bank			6,000.00	6,000.00
4	YRoyal Bank		3	2/15'24	Div				48.14	48.14
5	I124.55 - unit price
6	Q10
7	U1255.45
8	T1255.45
9	O9.95
10	^
11	D2/ 9'21
12	NContribX
13	U6,000.00
14	T6,000.00
15	M2021 Contribution
16	L[Savings]
17	$6000
18	^
19	D2/15'24
20	NDiv
21	YRoyal Bank
22	U48.14
23	T48.14
24	^
Sheet1

Power Query Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([#"!Type:Invst"] <> "^")),
    #"Split Column by Position" = Table.SplitColumn(#"Filtered Rows", "!Type:Invst", Splitter.SplitTextByPositions({0, 1}, false), {"!Type:Invst.1", "!Type:Invst.2"}),
    #"Grouped Rows" = Table.Group(#"Split Column by Position", {"!Type:Invst.1"}, {{"Count", each _, type table [#"!Type:Invst.1"=nullable text, #"!Type:Invst.2"=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"!Type:Invst.2", "Index"}, {"!Type:Invst.2", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[#"!Type:Invst.1"]), "!Type:Invst.1", "!Type:Invst.2")
in
    #"Pivoted Column"

 

[alansidman]

I see an error in my output. Will try again, but I think I am close.

 

[alansidman]

I think I have it now

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([#"!Type:Invst"] <> "^")),
    #"Split Column by Position" = Table.SplitColumn(#"Filtered Rows", "!Type:Invst", Splitter.SplitTextByPositions({0, 1}, false), {"!Type:Invst.1", "!Type:Invst.2"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Position", "Date", each if Text.Contains([#"!Type:Invst.2"],"/") then [#"!Type:Invst.2"] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Date"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Date"}, {{"Count", each _, type table [#"!Type:Invst.1"=nullable text, #"!Type:Invst.2"=nullable text, Date=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index", 1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Count", "Date"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"!Type:Invst.1", "!Type:Invst.2", "Date", "Index"}, {"!Type:Invst.1", "!Type:Invst.2", "Date", "Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"!Type:Invst.1"]), "!Type:Invst.1", "!Type:Invst.2")
in
    #"Pivoted Column"

Book1 (version 1).xlsb
	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	D	N	Y	I	Q	U	T	O	M	L	$
2	12/14' 9	12/14' 9	Buy	Royal Bank	124.55 - unit price	10	1255.45	1255.45	9.95
3	2/ 9'21	2/ 9'21	ContribX				6,000.00	6,000.00		2021 Contribution	[Savings]	6000
4	2/15'24	2/15'24	Div	Royal Bank			48.14	48.14
Sheet1

 

[rasinc]

Thanks for the quick reply on the weekend. I'll try and figure out how to install the XL2BB. I tried a couple of years ago and it didn't install properly or I couldn't use it properly.

The sample I posted was just from Notepad not Excel. The note about unit price was supposed to be just for me, I should not have included it but it should not cause problems with the transformation if that column stays as text.

You are correct, there is still an issue. I just tried your code and it is pulling in data to the first row from other rows further down because of the discrepancy in fields per record.

I think it is the Grouped Rows step that is causing the problem here. Filtering out the ^ removed the flag on where a record ends.

Is there any way to add a pseudo Index column that starts at the top of the file and assigns 1 to each row until it his ^, then increments to 2 until the next ^. Then that index column specifies what is part of one record and it might be used after for grouping on the ultimate column header.

 

[rasinc]

Sorry didn't see your next post. I'll try that and see.

 

[rasinc]

Sorry there is still a problem. Let me look at it some more and I will try and provide some more data. I can't just post the full file because of confidentiality. There are some identifying data in it all over the place, so I have to pull and remove references to the owner. Thank you for your efforts so far.

One question, are any of the stops you posted, manually written or are they all created by the ribbon tools?

 

[alansidman]

All were in the UI. Wrote code in the Add Column functions of the UI. They are fairly straight forward though.

 

[rasinc]

Thanks. I can add a conditional column that adds something when the first column = "D". If I could get that to increment from 1 and up each time a D is encountered, then I could just fill down and get a record number for each full record, regardless of the number of columns in each record. I tried to add an index column that starts with 1 but haven't been able to embed that in an If statement yet.

Then I just have to figure out how to use it to combine everything.