• C++ Programming for Financial Engineering
    Highly recommended by thousands of MFE students. Covers essential C++ topics with applications to financial engineering. Learn more Join!
    Python for Finance with Intro to Data Science
    Gain practical understanding of Python to read, understand, and write professional Python code for your first day on the job. Learn more Join!
    An Intuition-Based Options Primer for FE
    Ideal for entry level positions interviews and graduate studies, specializing in options trading arbitrage and options valuation models. Learn more Join!

Excel - Web interface?

Joined
8/6/10
Messages
283
Points
38
I have a project I need to get done quickly, and any advice would be most appreciated.

To cut to the short of it: I need to write an app that will take Purchase Orders, tidy them up and dump them into Excel. The front-end needs to be accessible via an intranet to non-tech types.

I'm thinking I can use VB for this and create some form of web front end (I presume VB.Net) that communicates with Excel and inserts the data.

I'd normally just store all the data in a DB and spit out the Excel/CSV doc, but the XLS file has a bunch of work done it already to manipulate the data, so I need to integrate with this.

Any thoughts on the best approach? I've only ever used Excel as a basic spreadsheet and never done any coding in it so I'm new to this.

Thanks
 
Tsotne - We use MySQL pretty much exclusively at my place. I'll use a DB if necessary in order to pass the data into Excel if that is a recommended route?

Andy - It will be accessed internally only, by around 12 people so it is a pretty small app really.

Thanks guys.
 
Yes why not?! If you have a data to be modified before imported into excel, store your data modification logic into SQL procedure and import the modified data into excel using the VBA commands.
 
Which languages do you know? This sounds like a simple CRUD application for the web. Ruby on Rails is famous for easy of use. If you don't know Ruby (I don't), I would use Python and the Django framework. You will be up and running in no time also. Dumping to XLS could be as simple as dumping a CSV unless you give more details.
 
Which languages do you know? This sounds like a simple CRUD application for the web. Ruby on Rails is famous for easy of use. If you don't know Ruby (I don't), I would use Python and the Django framework. You will be up and running in no time also. Dumping to XLS could be as simple as dumping a CSV unless you give more details.

Alain - I primarily work in PHP at the moment, mostly LAMP environments. I'll have a look into Python today them, that sounds interesting.
The content coming via the web front-end will basically needs be added incrementally to an XLS. The XLS is then setup to do financial functions for the accounting department.

Thanks
 
The content coming via the web front-end will basically needs be added incrementally to an XLS. The XLS is then setup to do financial functions for the accounting department.

This sounds like a bad idea all together. You are using Excel as your storage... bad bad bad.
 
This sounds like a bad idea all together. You are using Excel as your storage... bad bad bad.

I totally agree that using Excel as storage is bad, my preferred method would be to setup an Apache environment, pump the data into MySQL via a PHP front-end and then dump the data and have it imported to Excel.

My concern with the above method is getting the data from the DB into Excel without screwing anything up, and in a way so that the user doesn't have to perform any steps and preferably the data is added incrementally (or at least appears that way to the user).

That's why I'm a bit stumped on this, it's the portion of getting the data from the DB into Excel in such a manner as to achieve the above and which technology is best to achieve this.

Thanks
 
not big expert on this. but i use ADO to get data then manipulate it at VBA level and dump on spreadsheet. if u google for ADO u get tons of ref to get u started
 
Everyone,

Thanks for the feedback here. I've decided to go down the route of a C# asp.net web interface, that will store the data in a MSSQL DB. I'm then going to hook up the Excel spreadsheet to the DB so it pulls the data from it using some macros.
 
Back
Top