Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm looking for a software (or set of software) that will let me combine spreadsheet and database workflows. Data entry in spreadsheet to enable simple entry from clipboard, analysis based on joins, unions and aggregates and pivot/data pilot summaries.

Edit: Preferably free and multi platform software.

So far I've only found either spreadsheets OR db applications but no good combination. OO base with calc for tables doesn't support aggregates f.ex. Google Spreadsheet + Visualizaion API doesn't support unions or joins, zoho db doesn't let me paste from clipboard.

Any hints on software that could be used?

Basically I'm trying to do some analysis of my personal bank transactions.

Problem 1, ETL. The data has to be moved from my bank to a database. My current solution is to manually copy and paste the data into one spread sheet per account from my internet bank.

Pains: Not very scriptable. Lots of scrolling to reach the point to paste. Have to apply sorting and formatting to the pasted data each time.

Problem 2, analysis. I then want to aggregate the different accounts in one sweep to track transfers per type of transfer over all accounts. The actual aggregation is still unsolved because I can't find a UNION equivalent in the spreadsheets I've tried.

share|improve this question

3 Answers 3

Any database engine that supports linking to other databases via ODBC will solve your problems. As Chris pointed out, there is Microsoft Access (create a linked table), although that is quite painful to do complex analysis on (it does deal with basic analysis quite well).

You should use the Microsoft OLE DB Provider for Jet with an Excel spreadsheet.

My personal preference would be SQL Server with the Excel spreadsheet as a linked server.

EXEC sp_addlinkedserver
    'ExcelSource',
    'Jet 4.0',
    'Microsoft.Jet.OLEDB.4.0',
    'c:\MyData\DistExcl.xls',
    NULL,
    'Excel 5.0';

GO

SELECT *
FROM ExcelSource...SalesData;
share|improve this answer
    
Unfortunately it's not free software. But if nothing else shows up. Can you also go the other direction and have sheets built on updatable views with this solution? I'm thinking that would be useful for maintaining categories. "select description, category from (select decription from transaction group by description order by count(*) desc) left outer join categories using (description)" –  John Nilsson May 29 '10 at 21:00
    
SQL Server Express is free. I believe the latest version is 2008 R2 (it just RTM'd, otherwise SQL Server Express 2008 is definitely available). As for going the other way, you can try something like this: support.microsoft.com/kb/306125 - although if you want a balls-to-the-wall data analysis solution I would take a look at using SQL Server with SQL Server Reporting Services to deliver analysis of your data. It may be overkill for your needs, unless you know what you are doing you may want to use MS Access and the linked tables/reports functionality it provides. –  ta.speot.is May 29 '10 at 22:52

Not a direct answer, but are you aware of Mint.com?

We download and categorize your balances and transactions automatically every day— making it effortless to see graphs of your spending, income, balances, and net worth.

share|improve this answer
    
Mint.com looks sweet! But "Currently Mint only supports US-based financial institutions" –  John Nilsson May 29 '10 at 20:46
    
Sorry, should have looked at your profile before answering! Here in the UK moneydashboard.com has just started. Not sure if there is any equivalent in Sweden. –  Shevek May 31 '10 at 21:04

You can link to Excel Spreadsheets in MS Access and I've done plenty of analysis that way...

and a lot of banks let you download transactions as csv or Excel.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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