+ Reply to Thread
Results 1 to 4 of 4

Easiest way to generate tables automatically from another .xls file ?

  1. #1
    Forum Contributor
    Join Date
    08-28-2015
    Location
    Montreal, Canada
    MS-Off Ver
    2013 on PC, 2011 on MAC
    Posts
    159

    Easiest way to generate tables automatically from another .xls file ?

    Hey guys,

    I only recently started working with tables, and not sure how to solve my current problem. Basically, I'm trying to generate tables automatically, pulling data from another excel file, downloaded from the www.morningstar.ca website. The website allows us to download portefolios in .xls format...however, the presentation is rather weak, which is why I'd like to import the data into my own template, in an another .xlsx file.

    Morning Star File: Courtage BN.xls
    To be imported in: 03 - Gestion ADV.xlsx

    I'm looking for a way to pull data from 'Courtage BN', and put it in the right columns in '03 - Gestion ADV'. That I can do...I'm not sure I have the best way to do it though, so if you have any input on the matter, you're more than welcome to share. One thing I can't figure out though is how to make sure the table in '03 - Gestion ADV' will take ALL the data, adding or removing lines (without the total line in 'Courtage BN') if necessary each time I download a new version of 'Courtage BN', as the number of stocks might change from month to month ?

    So basically, is there an easy way to link a table to a very plain .xls file ?

    I did the first line (03 - Gestion ADV file), just to show you how I thought to go about it. If you guys know a better way, any input would be appreciated.

    IMPORTANT NOTE: Right now, there's only one template, for one folio. But eventually, there's gonna be more than just the one, on that same page. So I would need the lines to be 'added', to make sure the tables won't overlap.
    Attached Files Attached Files
    Last edited by KomicJ; 10-07-2015 at 02:10 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Easiest way to generate tables automatically from another .xls file ?

    Personally, I think I'd approach it like this:

    1. Open both workbooks in the same instance of Excel, for convenience.
    2. Create a separate sheet in the receiving workbook for the raw data as is (one off exercise)
    3. Delete the contents of all the cells (do not delete the cells) on the new sheet (each time the source data is to be copied across)
    4. Copy and paste the raw data (as is) from the source workbook to the new sheet (again, each time the source data is updated)

    At that stage, your analysis workbook is "self-contained". You can close, and perhaps archive, the source data workbook. That, in itself, should make it easier, simpler and quicker to set up and manage your other analyses sheets.

    5. Copy the Symbol column from the new sheet (a copy of the source data) to your re-structured worksheet
    6. In a separate helper column, use MATCH to determine the ROW number for each of the symbols on the raw data worksheet
    7. Use the ROW number in the helper column to INDEX the other fields. It will make the formulae simpler and more efficient as you don't need to keep recalculating.

    Anyway, just my thoughts on an initial design.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    08-28-2015
    Location
    Montreal, Canada
    MS-Off Ver
    2013 on PC, 2011 on MAC
    Posts
    159

    Re: Easiest way to generate tables automatically from another .xls file ?

    Yeah, I had something similar in my older version. I would basically copy the raw data, paste (value only) directly in the table. Symbols and names would be used as is, then I had a bunch of hidden columns, and just use them as reference for my new layout. It was working fine...but thought there might be a quicker way to extract the data than just copy the raw values manually.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Easiest way to generate tables automatically from another .xls file ?

    Well, I'd automate the whole process using VBA but I think it would be simpler in the long run.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Easiest way to convert excel file into pdf?
    By jlabruno in forum Excel General
    Replies: 3
    Last Post: 05-30-2013, 12:01 AM
  2. Looking For A VBA Solution To Replace Having To Generate Additional Tables
    By igor0415 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-12-2013, 08:16 PM
  3. Replies: 4
    Last Post: 09-13-2012, 09:08 AM
  4. Help! Macro to Generate email in Lotus Notes and automatically attach a file
    By elpollo87 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-27-2012, 10:19 AM
  5. generate stmt based on tables vaules
    By kaffal in forum Excel General
    Replies: 1
    Last Post: 01-18-2009, 04:38 PM
  6. Need to compare 2 tables to generate 3rd table
    By Kevin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-14-2005, 06:05 PM
  7. [SOLVED] easiest way to convert an XLS file into a CVS?
    By Tito in forum Excel General
    Replies: 1
    Last Post: 07-14-2005, 10:05 AM
  8. [SOLVED] Generate tables
    By Dee in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-17-2005, 03:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1