Australian (ASX) Stock Market Forum

Writing my own code (software) - I need an Excel guru

Jump to new
L

Larbec

Joined
15 June 2014
Posts
2
Reactions
0
I am not sure if this is a good forum to ask and I will try my best to explain what I am wanting to do without giving away too much secrecy. I need an excel guru. I am trying to mimic other columns and rows and have attached a video on what my ideas are. Of course its not really E's and O's but good enough for making widgets for now LOL. I appreciate anyone willing to take a stab at this

https://app.box.com/s/6nrn38ndxsaefz6s9vsc

https://app.box.com/s/uxrmbe7d70b2kq7ttm7e
 
Larbec said:
I am not sure if this is a good forum to ask and I will try my best to explain what I am wanting to do without giving away too much secrecy. I need an excel guru. I am trying to mimic other columns and rows and have attached a video on what my ideas are. Of course its not really E's and O's but good enough for making widgets for now LOL. I appreciate anyone willing to take a stab at this

https://app.box.com/s/6nrn38ndxsaefz6s9vsc

https://app.box.com/s/uxrmbe7d70b2kq7ttm7e
Click to expand...

You need a software developer, not excel guru.

With 60 pages and 20 tables each [?], once it's done in excel, it will most likely be unworkable - too slow, can't share, and data and function could easily corrupt.

Since an excel guru will most likely charge you the same or slightly cheaper than a proper developer, best to go and build a software - Windows or Web - with a proper database.

Trust me, if you build that in excel and it eventually work and you use it often or the data grows or minor design changes or new features/modules require, you'll sooner or later will want a database/software version.

with a proper application, you could get a better more intuitive user interface design than what you do now because that's what excel allows; also get proper reporting/charting if needed; flexibility for future modules and changes/versions etc.

If it's for personal or small team, a better solution would be MS Access.
 
hmmmm. unworkable? too slow? Neither apply for my needs.


luutzu said:
You need a software developer, not excel guru.

With 60 pages and 20 tables each [?], once it's done in excel, it will most likely be unworkable - too slow, can't share, and data and function could easily corrupt.

Since an excel guru will most likely charge you the same or slightly cheaper than a proper developer, best to go and build a software - Windows or Web - with a proper database.

Trust me, if you build that in excel and it eventually work and you use it often or the data grows or minor design changes or new features/modules require, you'll sooner or later will want a database/software version.

with a proper application, you could get a better more intuitive user interface design than what you do now because that's what excel allows; also get proper reporting/charting if needed; flexibility for future modules and changes/versions etc.

If it's for personal or small team, a better solution would be MS Access.
Click to expand...
 
Larbec said:
I am not sure if this is a good forum to ask and I will try my best to explain what I am wanting to do without giving away too much secrecy. I need an excel guru. I am trying to mimic other columns and rows and have attached a video on what my ideas are. Of course its not really E's and O's but good enough for making widgets for now LOL. I appreciate anyone willing to take a stab at this

https://app.box.com/s/6nrn38ndxsaefz6s9vsc

https://app.box.com/s/uxrmbe7d70b2kq7ttm7e
Click to expand...

Hi Larbec,

I've only looked at it briefly, so I didn't understand all the details of what you need to do. It's certainly doable.

I do, however, see that doing it via Excel formulas will big one big mess.

Your Recalculate button should point to a VBA macro, which would then do all the calculations. It can be structured in a much neater way there.

Sorry, although it looks like an interesting challenge, it looks a little too time consuming for me to have a go at the procedure myself.

KTP
 
Just to say:
I am in IT, did/do a bit of programming in a very wide set of areas.
And I concur:
give up Excel on such attempt..
You do what you want but luutzu took the time to give you an informed decision, for free and with no self interest; do not discard it IMHO
 
qldfrog said:
You do what you want but luutzu took the time to give you an informed decision, for free and with no self interest; do not discard it IMHO
Click to expand...

Larbec, me three on this. The fact you are asking the question indicates you have no skill in the area (no offense, just statement of fact), which means you should not lightly discard the opinion of those who do. Excel is not the way to do this. It might look like it is, but that's inexperience speaking. You don't build aircraft carriers out of rubber ducks, even though on first glance rubber ducks do indeed float. If a marine architect advises you against rubber ducks, take his advice !

You could make what seems like a fast start in Excel, but it will rise up and strangle you. Not the advice you want to hear.
 
Excel has a level of versatility that escapes the attention of many of its users. If one is willing to teach oneself more about excel formulae, macros, and VBA, then excel could indeed prove to be a workable solution.
I've seen bigger and more complex tasks handled by this software product during past employment.
 
cynic said:
Excel has a level of versatility that escapes the attention of many of its users. If one is willing to teach oneself more about excel formulae, macros, and VBA, then excel could indeed prove to be a workable solution.
I've seen bigger and more complex tasks handled by this software product during past employment.
Click to expand...

Excel will do it but it will run pretty slowly. I tend to agree that Access would be the best solution -- SQL is much better at handling big sets of data than VBA (you can of course embed SQL queries in VBA code). If you really wanted to you could just plug Excel into Access so the end user doesn't even see what's going on in the database.

ETA: It's been a while since I used either, so I might be hopelessly out of date! :)
 
McLovin said:
Quote Originally Posted by cynic
Excel has a level of versatility that escapes the attention of many of its users. If one is willing to teach oneself more about excel formulae, macros, and VBA, then excel could indeed prove to be a workable solution.
I've seen bigger and more complex tasks handled by this software product during past employment.

Excel will do it but it will run pretty slowly. I tend to agree that Access would be the best solution -- SQL is much better at handling big sets of data than VBA (you can of course embed SQL queries in VBA code). If you really wanted to you could just plug Excel into Access so the end user doesn't even see what's going on in the database.

ETA: It's been a while since I used either, so I might be hopelessly out of date! :)
Click to expand...

Get your hands on an old copy of Office 97; that has only a fraction of the bells and whistles that slow newer versions down to a crawl. But even 2007 can be made to do amazing things and do it pretty fast. I know. I'm using it for most of my analyses and reporting.
 
pixel said:
Get your hands on an old copy of Office 97; that has only a fraction of the bells and whistles that slow newer versions down to a crawl. But even 2007 can be made to do amazing things and do it pretty fast. I know. I'm using it for most of my analyses and reporting.
Click to expand...

I usually use 2003. But the OP mentions going all the way to column NZ, which means he can't use anything pre-2007 because those earlier versions only have 256 columns.
 
McLovin said:
I usually use 2003. But the OP mentions going all the way to column NZ, which means he can't use anything pre-2007 because those earlier versions only have 256 columns.
Click to expand...

It doesn't take an excel guru to find a workaround solution to this limitation on column numbers.

The adept user might choose to circumvent this limitation by splitting the data across multiple spreadsheets or alternatively using rows for columns (and vice versa) and adjusting the formulae accordingly. An excel guru, could further enhance the performance of such a workbook by including VBA encoded macros.
 
pixel said:
Get your hands on an old copy of Office 97; that has only a fraction of the bells and whistles that slow newer versions down to a crawl. But even 2007 can be made to do amazing things and do it pretty fast. I know. I'm using it for most of my analyses and reporting.
Click to expand...

+1

I often curse the way certain companies perpetuate their business by fixing things that aren't broken. Several times I've had to completely rewrite the VBA code for spreadsheet macros following upgrade to later versions of excel.
 
pixel said:
Get your hands on an old copy of Office 97; that has only a fraction of the bells and whistles that slow newer versions down to a crawl. But even 2007 can be made to do amazing things and do it pretty fast. I know. I'm using it for most of my analyses and reporting.
Click to expand...

I used to use Excel 2000 for a large spreadsheet. Switched to Excel 2010 and it was much faster due to multiprocessor support using all 8 cores instead of one (yes I know there are only 4 real cores but Excel uses all virtual cores), and loads and saves faster due to much smaller file size (about 80% smaller). My Excel spreadsheet is now about 10MB (was about 50).

I still have auto-calc off though and manually recalculate.

My 2c on Excel for large spreadsheets and large amounts of data: easy and quick to develop. Easy to do charts and pivot tables, data filtering, lookups etc.

Also easy and quick to make mistakes, corrupt your spreadsheet without noticing until later, no separation of code and data, Excel spreadsheets tend to be unreadable, VBA is pretty randomly designed. Hard to roll-back part of your spreadsheet (e.g. you stuffed up some formulas, but then worked on some others - you have a hard time rolling back the broken ones because it is all in one file).

My hard-core investment coding is done in Java, but Excel is good for a lot of things, especially filtering, visualizing and formatting output.
 
Top