I have copied and pasted historic interest rates from http://www.loansense.com.au/historical-rates.html into notepad++

I want to get it into a spreadsheet. Calc.

It comes as one long column in Notepad++. I can select a section and Ctrl-J makes that a row.

Then the next selection the same.

That give column headers and the data for each column.

But they're different sizes. Different number of character headers and data.

They don't match up.

How should I do this operation?


I’ve tried two approaches. At first I looked into the source of the page. I have found this section: <table title="Australian Interest Rates" summary="Source RBA" align="center" border="0" width="640">. This is the table with data. It contains only one row. This row contains 12 columns out of which 8 contains MONTH-YEAR and RATE data. The data are separated by a <br /> tag. Overall this isn’t a properly structured table. As the data is semi-structured I think it would be too much work to build anything robust to get a return from this investment.

Instead, I’ve tried a second aproach relying on old copy&paste technique. I checked this in Windows 7 + Chrome and Ubuntu 16.10 + Firefox.

  • On the website click with left mouse button right before MONTH-YEAR (nothing should be selected yet).
  • Scroll down to the bottom of the table.
  • Press Shift on the keyboard and hold it.
  • Click with left mouse button right after ‘May-1974’.
  • The whole column should now become selected.
  • Ctrl+C
  • In Calc → Edit → Paste special → Unformatted text → In Fields section click on Standard → Change column type to Text → OK

Repeat the steps for each column with data.

The whole operation should take about a minute or two.

Last thing is to get a mapping between the rows in last two columns but this is rather a separate topic about the type of the source...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.