Consulting

Results 1 to 8 of 8

Thread: EXCEL/Quickbooks HELP!!!

  1. #1
    VBAX Regular
    Joined
    Apr 2014
    Posts
    8
    Location

    EXCEL/Quickbooks HELP!!!

    Union Report :SamT

    Hello
    I have three separate questions and this is Part 1 Question...
    we are a union contractor, every month i need to provide a benefit report when paying in dues. instead of me doing this manually i would like to have it come from quickbooks to excel.

    i know how to get the following data into excel from quickbooks.


    • col #1 employee name,
    • col #2 soc sec#,
    • col#3 - #7 (depending on if 4 wks or 5wks) start of wk,
    • under start of wk they are looking for end of wk, under end of wk they are looking for pay date,
    • col #8 proof hours (which would add col #3-#7 up and subtract col #12 - if result is zero then the column can be hidden if greater than zero then there is an error somewhere),
    • col #9 regular hours (taken from paychecks),
    • col #10 OT hours (taken from paychecks),
    • col #11 DT (taken from pay checks),
    • Col #12 total hours, adds up col #9-#11,
    • the next 11 cols (col #12 - col #22) can be taken from paychecks...it is for each fund and the amount of money each fund gets per employee.


    thoughts, comments, need more info??
    Thank you so much...Im sure this can be done but not sure exactly how as they want it set up in the columns above and I don't know much about vba or sql or obdc...

    Thanks again and HELP....
    Andrea
    Last edited by SamT; 04-09-2014 at 06:59 AM.

  2. #2
    VBAX Regular
    Joined
    Apr 2014
    Posts
    8
    Location

    EXCEL/Quickbooks Question #2

    Workers Comp Report: SamT

    Hello
    Excel/Quickbooks Question #2:
    column labels
    payroll expenses: bonus payroll expenses: Gross wages Payroll expenses: Pay adj Payroll expenses: wages - officers
    row labels bonuses DT
    OT
    RT Vaca Pay adj other salary total/net
    Name of employee
    date of check (ie 01/06/2013
    01/14/2013
    01/21/2013
    01/21/2013
    750.

    I can pull the above information from quickbooks into excel by using the reports/summarize payroll into excel/ then using the employee journal by check tab and customizing the pivot table however I need more info than just the above and i am hoping for it to calc by itself.
    I need five more columns...
    with the following labels and they will be doing the following:
    OT/DT prem adj. - this col will be ( OT /3)+(DT/2) rounding to 2
    Gross minus OT/DT Prem - this col will be Total/Net - OT/DT prem Adj.
    Bonus - this col will add paychecks up (not counting the paycheck for the bonus)/number it comes up with and then evenly distribute the number throughout the bonus column. (ie: the above is bonus amount/(4-1) then evenly distributed in bonus column on each check date...250 check 1, 250 check 2, 250 check 3
    wages after bonus distribution - Gross minus OT/DT Prem + Bonus
    amount chargeable for week - somewhere on the sheet needs to be a spot to enter a max value (ie: 1,188.10) this max value will be compared to wages after bonus distribution - if more than the max value then enter max value - if less than the max value then enter the amount chargeable for week value.

    Currently i do this in a by hand with some manual calculations after i have summarized the data into excel from quickbooks.

    my goal is to have it set up to not have to manual do it every time....I would like to manual just enter the max value.

    HELP - Thanks!
    Andrea
    Last edited by SamT; 04-09-2014 at 07:01 AM.

  3. #3
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,840
    Location
    What's the question?

    Your post is like giving a carpenter nothing but the materials list and telling him to build it.

    PS; 30 years in the trades, from broom & shovel to contractor.
    Please take the time to read the Forum FAQ

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,840
    Location
    Andrea, please edit your post. under the editor, you will see a "Go Advanced" button. this will move your post to the Advanced Editor, which has Icons (buttons) to insert and modify tables. Please use those tables to show the layouts you need. It also has a Preview Post button so you can see it before you post it.

    On the other hand, under the Advanced Editor, is the Manage Attachments button you can build those tables in Excel and attach the workbook to a post so we can see your tables and formulas. Just be sure and tell us what you have now and what you need.

    On the other other hand, you can copy a table in Excel and paste it in the Advanced Editor.

    Because, honestly, nothing in the top of your post can be understood.

    BTW Andrea, Welcome to VBA Express. you will find solutions to your Excel problems here.
    Please take the time to read the Forum FAQ

  5. #5
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,529
    Location
    Also, since you do it by hand, you know your formulas

    Assuming that QB can export a CSV file (or something) Excel can probably open it, and a macro can re-create your manual formulas

    So attaching CSV file (small one) and an Excel workbook with your formulas will make it much easier

    If by chance you don't use formulas, then spell out in very detailed and specific language with examples of the desired result

    Paul


    BTW - it makes it easer to follow a thread if you merely [Reply] using the button in the bottom right, instead of starting a Question #2

  6. #6
    VBAX Regular
    Joined
    Apr 2014
    Posts
    8
    Location

    EXCEL/Quickbooks HELP!!! Reporting benefits

    Union Report :SamT

    VBA Union report w formulas shown.xlsx

    Every month I need to fill this form out with this layout. This layout needs to stay the same, the local wants to have everything in a row as the attachment shows.

    GOAL:
    To utilize GET DATA from QUICKBOOKS.
    In quickbooks I would do the following:
    reports/employees & payroll/summarize payroll data in excel

    I know this can be done I just don't know if it is through VBA/SQL or OLEDB. All the info in entered into the employees timecard or paystub.

    Hours are entered on time cards in Quickbooks
    The employee deductions for dues are listed individually by each fund - with the amount of money for each being deducted.
    The employer contribution are listed individually by each fund - with the amount of money going each

    Hopefully this is more understandable now.
    Thanks!
    Last edited by SamT; 04-09-2014 at 07:00 AM.

  7. #7
    VBAX Regular
    Joined
    Apr 2014
    Posts
    8
    Location

    EXCEL/Quickbooks HELP ~ Workers Compensation Reports for NYS

    Workers Comp Report: SamT

    VBA WC Report w formulas shown.xlsx

    Similar to last problem I sent in....While utilizing Quickbooks through reports/employee/summarize payroll in excel.
    Through a pivot table i can obtain the columns A-H which is a start. However I am looking to establish the 5 columns which aren't in a pivot table and I did manually.

    The workers compensation cap amount will change yearly.

    The bonus amount (not all will receive) needs to be evenly distributed based on the number of weeks worked (not number of paychecks)

    The GOAL would be to have it all automatic from quickbooks using a GET DATA. or utilize the pivot table and then figure something so I wont have to enter every thing over every year in the last 5 columns.

    Thank you for all your help.
    Last edited by SamT; 04-09-2014 at 07:02 AM.

  8. #8
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,840
    Location
    Andrea, I have merged your two threads into one thread because the two questions are really the same: "Get Data from Quicken into a Workbook"

    I also merged the Worksheets from the two attachemnts in to one workbook because separate answers might cause a conflict. This does not meant that the two reports will need to be in the same workbook, just that it is better that we consider both solutions at the same time.

    In the attachment below on sheet "Weekly Workers Comp Report" In Cell (M9) is a formula I think you should consider.
    Attached Files Attached Files
    Please take the time to read the Forum FAQ

Tags for this Thread

Posting Permissions

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