Automate data upload from QuickBooks to Excel
- or -
Post a project like this- Posted:
- Proposals: 6
- Remote
- #1808297
- OPPORTUNITY
- Awarded
Description
In file B, the date is recorded in column H. As you can see, there is a starting date of 1/1/11, which must stay as is. After that, the date must increase by one day per row up till 12/31/17. Based on either a macro or an advanced search formula, I need columns D to N (each of which have a name header that exactly matches the name in QB) to auto populate with the same information in column V of file A. Because file A does not update every day, that means some of the dates in file B would be empty. In that case, they need to auto populate with the number above them. Often, file A will have more than one transaction per day. In that case, file B needs to auto select the LAST amount in column V of file A that is associated with that date. Note that because each column in file B represents a different person, that means you have to auto select the last amount associated with that date for EACH PERSON.
Columns N through AN in file B all have formulas derived from the data in K,L and M. These need to be auto-populated every time a new row with a new date is added.
The model should be populated with every day from 1/1/11 until 12/31/17. I would like a macro to automatically add the next date on request and to update the formulas at the bottom. It is likely that a table will be the best way to accomplish this, but I'm open to suggestions.
I also need the ability to add new customer names (see note below)
This model requires someone who not only understands Excel, but also the logic behind what I am trying to achieve. US based candidates are preferred, because I anticipate the need to discuss further on the phone.
Extra notes: The data in file B doesn't match file A. Don't worry about that. Assume the data in file A to be right and overwrite file B with it.
Don't concern yourself with the data in columns A,B,C,D,E,F,I&J in file B for now.
File A has many customer names, but file B only has 3, which I have used as an illustration to get the process started. You'd need to add the other names alongside my 3 examples in file B. and also provide the ability to add new names every time a new one has been added in QB
Geoffrey K.
100% (2)New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
Hi Geoffrey, I've completed some of Qickbooks technical courses so I could most likely look at this for you. Are you likely to want this completed pre this weekend? If not I'd prefare to look at it in detail over the weekend to try to ensure I can get my head around the technical aspects of it..?
-
Hello Geoffrey,
Can you please provide the "Working example2" (File B) asset? Once you have uploaded the missing example, I would be more than happy to submit a proposal that will include macro-based methods for handling this transformation and automated methods to programmatically transform your Quickbooks data dump into a clean excel sheet.
I look forward to the opportunity to work with you!
Thanks
Mark B.Geoffrey K.29 Nov 2017Hi Mark. Thanks for your prompt response. I am out all morning but will get to this in the afternoon. I believe that the file you're referring to is already uploaded. Regards, Geoff
Mark B.29 Nov 2017Geoffrey,
Thank you for your response. According to your RFP, there should be two file attachments, correct?
PPH is only displaying one file attachment: Data_Dump2.xlsx - I have reviewed that document and know I can provide you with what you're requesting, but it wouldn't be right for me to submit a proposal if I haven't reviewed the full scope of your request and assessed it accordingly. I would hope other proposals would assess the full scope of your needs and ensure they can deliver what you're requesting as well.
I just want to ensure the highest quality deliverable to your specifications.
Thank you again,
Mark B. -
Hello Mr Geoffrey
I wish i had 3 credits to send proposal. Will you still excel model for dates after 12/31/2017?