Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15
Like Tree5Likes

Is My Economic Model too Difficult for Excel to Handle?

This is a discussion on Is My Economic Model too Difficult for Excel to Handle? within the Excel Questions forums, part of the Question Forums category; Sorry I've been unable to spend much time looking at the spreadsheet you sent me. I'd echo the above comments ...

  1. #11
    Board Regular
    Join Date
    Dec 2008
    Posts
    140

    Default Re: Is My Economic Model too Difficult for Excel to Handle?

    Sorry I've been unable to spend much time looking at the spreadsheet you sent me. I'd echo the above comments about VBA's capabilities. I encounter a lot of people who underestimate the scope of problems that recent versions of Excel can handle.

    In some of the MatLab work I've done for my dissertation, I use the fprintf command to send my output to a text file which I then import to Excel for easy manipulation. I assume Gnu Octave would have a similar if not identical command.

  2. #12
    Board Regular
    Join Date
    Sep 2014
    Posts
    90

    Default Re: Is My Economic Model too Difficult for Excel to Handle?

    Thanks mrmickle1. I've finished the first time period of 40 (I might reduce it to 30), and the Gnu Octave program functioned well. However, it took me more than an hour to transcribe the data into my excel file, and as the data list gets longer, its only going to get harder to transcribe the data. One big advantage Gnu Octave has over Excel is that its easier to spot the bugs and mistakes. Its hard looking into an excel cell and figure out what is what. My final program in excel was just a mess. I discovered many mistakes by writing the program on gnu octave. Programming for the second period will be somewhat more difficult than the first period, because now I'll have to account for depreciation and build up of capital and human capital stock. However, once the second period is finished, the second period program can serve as a template for the remaining periods.

    In my test run for just the first period, I instructed the program to do up to 100 iterations until one of the aggregate variables changes by less than .1% between successive approximations. In the test run, it took three iterations for convergence to occur. However, I'm worried that convergence will be a lot tougher to get once stock quantities begin to accumulate. During the iteration, my first guess at the value of the variable is done by ignoring certain effects that make the calculation complicated. Because in the early periods, there is little accumulation of stock variables, the initial guess is quite good. Once we get in the later period, a guess too far from the actual value may not result in convergence. I suppose, I could learn how to do more complicated iteration processes.

    Anyway, what downloads do I need to use VBA on Windows 8 and Office 2010? Is there a user manual? I've just spent two weeks learning Gnu Octave, do you think its worth it to change programs again? How easy is it to learn VBA? I can see myself doing 40+ hours of work just to transcribe the gnu octave data output into excel for a given test run. Thus, just conducting three or four runs with different parameters could take weeks. In a similar but simpler program in excel, I was able to do 20+ runs per day.
    Last edited by Ed Song; Sep 19th, 2014 at 07:20 PM.

  3. #13
    Board Regular
    Join Date
    Nov 2007
    Location
    USA
    Posts
    403

    Default Re: Is My Economic Model too Difficult for Excel to Handle?

    Your project and last post raise several issues.
    I am not familiar with Gnu Octave. And why so long to get the output transferred to Excel. Are you using clipboard to transfer? Once in Excel VBA could again be helpful in reformatting the data.

    There is a recent post of a list of Excel resources by hiker95. Hang around this forum and you will soon find helpful info like this.
    http://www.mrexcel.com/forum/excel-q...ml#post3925232

    The list is long. If you prefer book over video, one book like Walkenbach's power programming with Excel book may be enough.
    Excel comes with two help systems, one on the sheet, and a different one from the code window.

    Convergence may not always happen, or could different starting numbers result in converging to different result? You may have to test to find out.

    What exactly are stock quantities?

    How easy is it to learn VBA depends. VBA is similar to some other programming. It has standard variable types, many functions corresponding to the worksheet functions, simple and array variables, loops. If you are already familiar with these concepts it would go much faster learning to transform cell type formulas into code. Some people revel in getting Cell Formulas to work. But this approach is not always practical.

    There are various debugging techniques for VBA code explained in the references. Some may use one simple technique, while others may use a variety of techniques. Debugging Cell Formulas or VBA code involves first spotting a problem, then figuring out what is wrong, and how to fix it. It should get easier and faster with practice.

    With such a project with no answer key how do you test it? Organization factors such as compartmentalization into modular parts factor into design. Start simple and add functionality. Could a cell formula and code version be built and compared, or an Excel and Gnu Octave version? With complex climate modeling, different models can be compared. What value is the result unless there is confidence it works as planned and how is that demonstrated? Similarly a math problem can often be solved by two different methods to show the same answer. So if you ask if it is worth it to learn another method these issues come to mind. Without some kind of testing or verification or validation you may just be building a house of cards.

    Other books, not specifically on Excel, may also give you ideas on approaching complex projects.
    The pragmatic programmer by Andrew Hunt published 1999.
    This book discusses issues such as building a prototype, and adding complexity.
    Ed Song likes this.

  4. #14
    Board Regular
    Join Date
    Nov 2007
    Location
    USA
    Posts
    403

    Default Re: Is My Economic Model too Difficult for Excel to Handle?

    I meant to address this in your other post #9 also.

    "But I've heard that there are limitations on the amount of code VBA can handle."

    Macros do have a size limitation. Each module can have over 1000 lines of code. If the limit is exceeded the subs can be put in different modules.

    Here is a link:
    Maximum Length Limit for a Macro (Microsoft Excel)

  5. #15
    Board Regular mrmmickle1's Avatar
    Join Date
    May 2012
    Location
    Charlotte, NC
    Posts
    2,393

    Default Re: Is My Economic Model too Difficult for Excel to Handle?

    Edsong,

    You have been referring to vectors and scalars. I am not too familiar with these, but here is a website that may prove of help with scalars:

    VBA Internals: Getting Pointers | Byte Comb


    This website discusses e :

    MS Excel: EXP Function (WS, VBA)

    As I mentioned before I will be glad to assist you in your project. I thought this may give you a little bit of VBA background on some of the subject matter your project encompasses.

    Jackbean,

    You bring up some good points. Early as I was learning VBA I recorded a few macros that exceeded the maximum limit. The funny thing is that I never knew what it was.... I just knew I had surpassed it. Thanks for the information!
    Matt Mickle
    Using Excel & Access 2010, 2013 & 2016 | Windows 7 | 64 Bit

    Post a screen shot with HTML Maker

Page 2 of 2 FirstFirst 12

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com