PDA

View Full Version : EXCEL/Quickbooks HELP!!!



awade74
04-03-2014, 06:40 AM
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

awade74
04-03-2014, 07:09 AM
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

SamT
04-03-2014, 11:36 AM
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.

SamT
04-03-2014, 11:57 AM
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.

Paul_Hossler
04-05-2014, 07:21 PM
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

awade74
04-09-2014, 02:46 AM
Union Report :SamT

11541

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!

awade74
04-09-2014, 03:48 AM
Workers Comp Report: SamT

11543

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.

SamT
04-09-2014, 07:13 AM
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.