Any way to import historical statements from HSBC to spreadsheet?

dougz_2

I want to get my historical statements going back several years into a spreadsheet somehow.

The download function on HSBC online banking is not available for historical statements. Manual copy-paste of each page is unsatisfactory as the page view only shows the beginning of each transaction description, to get it all you have to actually click on each one to reveal the rest, and then copy-paste the line individually.

Is there some software (or script) that will do that automatically?

I do also have them in paper form, so alternatively has anyone tried OCR software to do this kind of thing?

fredie

HSBC dont make it easy to place historic statements into spreadsheets. The cut & paste output is missing info to allow import diractly. The following works for me.

1. Cut and paste the web page table contents into a text file, dont worry that the row data appears on separate lines. I do the same for a whole year's set of statements, just appending the data, one set after another. make sure you start in the first cell & go to the last cell of the table.

I use notepad++ (free off the web http notepad-plus-plus dot org

2.Save the file & work ooff a backup just in case you make a mistake.

Use the text replace mechanism within 'notepad++' as follows to make a file that can be cut and paste directly into Excell.

3. Ctrl-H (replace)
radio button - search mode: Extended (\n, \r....) - this is important
find: \r\n
replace with: (leave it empty)
check box - Replace all

- text goes to a single line, takes 20 seconds or so.

3. Ctrl-H (replace)
radio button - Regular expression - this is important
find: (\d\d [A-Z][a-z][a-z]) - type it all in carefully inc brackets
replace with: \r\n\1
check box - Replace all

- chops line into multiple lines by placing a new line infront of every date - it can be a suprise when it just looks right at this point - I was when I came back to it after a year to do this years data.

4. Copy the file into a worksheet, Excell is clever enough to parse it with no problems.

5. HSBC uses differing row formatting if there are multiple rows for one day. So I make use of the Excell pivot table feature to sumerise the hsbc statement by day, effectively merging multiple rows for a single day together. It a little difficult to describe, but the principle is to create a pivot table against all the column data. setting the date as a 'row label' and credit, debit & balance as summation fields (using 'sum of' for credit & debits & 'max' for balance). If this does not make sense to you then pivots are probably not for you, but you are no worse off as you have the hsbc data in a spreadsheet to do as you please.

Motty1971

A brilliant way to copy and paste the details onto Excel

The thing is I got it to work once, and then after that it doesn't separate the amount debited from the person who was paid out, and I can't tell what I am doing wrong.

The only thing I can think of is that I am copying and pasting the find and replace letters and symbols rather than typing them out.

I know you posted this a long time ago but if you could clear up what could be wrong it would be appreciated.

martin_hansen-lennox

It's doing it for me too.

First, make sure you are not copying and pasting spaces after the expressions like "\r\n" and "([a-z])".

Second, I found it worked by replacing "\r\n" with "\t" (rather than nothing). This replaces each new line with a tab spacing. Excel recognises this as the start of a new cell, thus separating your amount from the person paid, transaction date, etc.

Then proceed as above.