• Australian (ASX) Stock Market Forum

Hello and welcome to Aussie Stock Forums!

To gain full access you must register. Registration is free and takes only a few seconds to complete.

Already a member? Log in here.

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

Discussion in 'Software and Data' started by Larbec, Jun 15, 2014.

  1. Larbec

    Larbec

    Posts:
    2
    Likes Received:
    0
    Joined:
    Jun 15, 2014
    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
     
  2. luutzu

    luutzu

    Posts:
    7,956
    Likes Received:
    1,050
    Joined:
    Apr 21, 2014
    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.
     
  3. Larbec

    Larbec

    Posts:
    2
    Likes Received:
    0
    Joined:
    Jun 15, 2014
    hmmmm. unworkable? too slow? Neither apply for my needs.


     
  4. luutzu

    luutzu

    Posts:
    7,956
    Likes Received:
    1,050
    Joined:
    Apr 21, 2014
    As in it won't be slow and work fine?

    Yea, as long as you're the only one using it.
     
  5. KnowThePast

    KnowThePast

    Posts:
    456
    Likes Received:
    52
    Joined:
    Jun 3, 2013
    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
     
  6. qldfrog

    qldfrog

    Posts:
    2,589
    Likes Received:
    830
    Joined:
    Jun 8, 2008
    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
     
  7. waimate01

    waimate01

    Posts:
    205
    Likes Received:
    1
    Joined:
    Nov 22, 2010
    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.
     
  8. cynic

    cynic

    Posts:
    3,801
    Likes Received:
    411
    Joined:
    Feb 25, 2011
    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.
     
  9. McLovin

    McLovin

    Posts:
    5,341
    Likes Received:
    229
    Joined:
    Jun 2, 2011
    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! :)
     
  10. pixel

    pixel DIY Trader

    Posts:
    5,200
    Likes Received:
    318
    Joined:
    Feb 3, 2010
    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.
     
  11. McLovin

    McLovin

    Posts:
    5,341
    Likes Received:
    229
    Joined:
    Jun 2, 2011
    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.
     
  12. cynic

    cynic

    Posts:
    3,801
    Likes Received:
    411
    Joined:
    Feb 25, 2011
    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.
     
  13. cynic

    cynic

    Posts:
    3,801
    Likes Received:
    411
    Joined:
    Feb 25, 2011
    +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.
     
  14. ajcode

    ajcode

    Posts:
    137
    Likes Received:
    19
    Joined:
    Feb 12, 2014
    Hi Larbec,

    Here is my attempt at trying to understand your problem:

    View attachment AJC_OEUDAutoCounters.xls

    I like clicking on buttons and seeing the solution :)

    Even if it's insanely slow LOL :)

    Cheers,

    Andrew
     
  15. lclfze

    lclfze

    Posts:
    22
    Likes Received:
    0
    Joined:
    Jan 25, 2013
    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.
     
Loading...

Share This Page