Jan 19 2021 05:34 PM
Jan 19 2021 05:34 PM
Would love some help writing this macro
i tried attaching a file with more explanations within the file if it helps make it clearer. (in yellow highlights & red text)
I have two sheets. One titled 'Money', the second titled 'Data'
In the 'Money' tab, there's a section which currently begins from row 41 where the macro would be populating the data it's pulling. For clarity's sake, i'll refer to this section as the Expense section.
Essentially, the macro is pulling data from the 'Data' tab based on certain criteria (the date). If the criteria is met, it will copy the data of its respective row from the 'Data' tab; will go to the 'Money' tab, look for the last row that contains data in the first portion of the Expense section, creates a new row and pastes values in this new row; making sure not to be pasting over any previous data
written out as steps:
if the concatenation of N & O of 'Data' sheet, viewed as Date format, matches the month of today's date; then copy and paste values of the concatenation into the first available row in column B of 'Money' sheet (beginning from B42 onwards)
then still referencing that same row in 'Data' sheet, copy columns P thru S and paste values into columns C thru F of 'Money' sheet in the same row that was previously referenced.
then still referencing that same row in 'Data' sheet, copy columns T and U and paste values into columns H & I of 'Money' sheet in the same row that was previously referenced.
then still referencing that same row in 'Data' sheet, copy column V and paste values into column G of 'Money' sheet in the same row that was previously referenced
i hope i explained that decently. please let me know if the Excel file isn't any clearer and i'll add more clarity.
Jan 20 2021 07:16 AM - edited Jan 20 2021 07:18 AM
@Deleted
Well, as of this writing, your question has 60 views and 0 replies. I'm going to break the pattern. I'm also going to take the risk of trying, perhaps, to explain why nobody has replied.
It looks as if you're trying to create some kind of income and expense tracking workbook. That's admirable. I have one myself that I created. Microsoft even has a very advanced (although also somewhat limited) major product called Money in Excel that you can find by searching here on this website. Money in Excel is a major Excel template; it can draw data from your bank accounts (so long as they're not behind dual factor authentication--the limitation I alluded to in the last sentence).
You are approaching this commendable task--IMHO--in an altogether convoluted way, using brute force (also known as VBA or macro) to accomplish things that Excel can do very elegantly by means of functions calling on a central Table that contains all of your transactions, along with identifiers such as vendor, budget category, etc. There's also the Pivot Table that can create automatically a summary of transactions by category by month (or year); you can click on any cell in the Pivot Table to get a detailed list of the transactions that have gone into the summary number.
In short, Excel can do the heavy lifting for you; you don't need to write macros. But it does require re-thinking the whole design. I'm attaching a very simple example of what I'm describing. Very simple. My own raw data base contains a lot more columns (with identifiers for each row of the credit card (4 of them) or bank account (3 of them); identifiers for budget categories two levels deep)... This example just shows how Excel can take a data table and use the Pivot Table to make sense of the raw data.
Jan 20 2021 07:47 AM
@Deleted
I agree with Mr. @mathetes it is not an easy job to create a macros like this. Try to create your report for one date with sufficient extra space and link the date of the report to a list of the dates. Then you can create much more easy macros, thal will change the date in the report, calculate and copy the result to a new sheet or book
Jan 20 2021 10:31 AM - edited Jan 20 2021 10:35 AM
I just want to make sure there's no confusion here. You wrote I agree with Mr. @mathetes it is not an easy job to create a macros like this, but in fact we're not in agreement.
I was not saying that it's "not easy" to create a macro....my fundamental point was that creating a macro was the wrong way to approach the task of creating a spreadsheet to track income and expenses. Excel can do wonders without relying on macros, especially in dealing with databases of financial transactions. The Pivot Table is one tool that requires no macros to analyze a database. There are functions like the recently released FILTER that can pull records from one sheet to another based on a variety of criteria.
Macros do have a place. I don't categorically oppose them. But Excel's built-in functions provide reliable ways to accomplish a task like this. It's worth while to spend time learning how to use those built-in functions rather than spending the time to figure out how to write an unnecessary routine to accomplish the same task.
Jan 20 2021 10:52 AM
Sure, my idea is to try to solve the problem with some other way, using simple formulas combined with simple macro, or built-ins.
Jan 20 2021 06:05 PM
thanks a lot for the help @mathetes
yeah, i also downloaded Money to see if i could try to come to my solution from that, but that didn't work out. i'll be making use of it down the road though.
part of the reason why i wanted to take this route is because it helps me learn. i'm not too advanced with excel, but i marvel at its power. i created some formulas in here that's i'm pretty proud of, and what i'm trying to do now is likely the most complicated portion of the workbook i'm ultimately trying to create that i hope to use to manage my money, health and investments.
i was hoping to have a macro involved because i wanted to build off it for some of the other things i wanted to incorporate in the file.
i also thought about using a pivot table but as an absolute last resort. i'm hoping it doesn't get to that point. i'm thinking over the next couple months if i can create formulas and a macro that helps me do this and considers different dates i'll not just get the file to a place i love, but also feel more comfortable using excel
thanks for sharing your file though, i can use it for some ideas. and thanks again for the input.
Jan 20 2021 06:07 PM
thanks for the input @AnaBoyko .
yeah i was thinking last night that i should probably take that route...simplify what i want the macro to do and then just try to evolve it.
Jan 21 2021 07:18 AM
@Deleted
Some thoughts in response to what you wrote (which I'll quote in italics)
yeah, i also downloaded Money to see if i could try to come to my solution from that, but that didn't work out. i'll be making use of it down the road though.
I was disappointed in Money in Excel on several points. First, because my bank and stock accounts at Fidelity are all behind dual factor authentication, MiE could not get that data. I had gotten all the credit card data before trying..... But from a sheer design point of view, their budget categories seemed to have been very poorly conceived, and there was only a limited amount of flexibility in modifying them.
part of the reason why i wanted to take this route is because it helps me learn. i'm not too advanced with excel, but i marvel at its power.
Keep at it. You're right about its power. I would just recommend learning to do as much as you can without reverting to macros. The built-in functions are many and varied. Learn to take advantage of them to sort through data. Learn to create tables that reference one another by means of functions like XLOOKUP, INDEX, MATCH, and the like. If you're in the Windows environment, learn Power Query.
Macros are good for recording repetitive actions, and, yes, you might also be able to do some data manipulation. But often I suspect you'll learn later that the data manipulation you can do with a macro is better done, more reliably and faster, with a built in function.
An anecdote to illustrate my point. Years ago (decades ago) I was in charge of the HR database for a major corporation. We had to do a monthly headcount report, summarizing employee counts by division, location, employee category, etc., etc. One of the programmers in IT had written a massive macro that produced the spreadsheet report. It took about 30 minutes to run. I spent some time using nothing but built-in functions and was able to produce accurate results in a small fraction of the time, more reliably. That programmer was simply using the skills she knew and assumed would be more powerful, more suited to the task since it required going through so many records.
That was all on Lotus 1-2-3 and happened in the 1990s..... Excel now is far more powerful than Lotus was then. The data you're working with is far less complicated than what we were working with in tracking transfers, hires, terminations across many thousands of employees. I am suggesting that if you learn how to create well-designed Tables of your transactional data you won't need macros.
FWIW, I have one large workbook that tracks our income and expenses, drawing on data from four credit card companies and three bank accounts. I also have a couple spreadsheets to track investments, one of which is devoted to options trades. No macros are used in any of them. Only built-in functions and tools like Pivot Table. Speaking of which,
i also thought about using a pivot table but as an absolute last resort. i'm hoping it doesn't get to that point.
Why "as an absolute last resort"? You write as if it's a very bitter pill, an unpleasant task to be avoided at all costs. Most people, when they discover how easily it can produce useful cross-tabulated summaries of such things as expenses, are delighted. There's a small learning curve, but it's really small. And well worth delving into. Here: this is a website that does a great job of explaining not only the Pivot Table, but many other built-in functions. https://exceljet.net/excel-pivot-tables
I highly recommend that site (and there are others) as something you can make use of in your learning. YouTube also has some excellent videos, some of them by people you'll encounter on these pages.
Jan 22 2021 06:59 PM
thanks @mathetes
the main reason for not using pivot tables here is because it wouldn't match the aesthetics i laid out.
however, it doesn't looks like i can accomplish what i'm trying for without using them in place of (at least to my knowledge)
i tried writing out what the formulas would do, but i can't get around it
is it possible to have a formula that does this:
if column N from 'Data' sheet equals the same month as today, then concatenate columns N & O from 'Data' sheet
but with that, it would have to do this search through all of column N without picking the same row twice
i think i might be able to manage from there
Jan 22 2021 09:47 PM - edited Jan 22 2021 10:20 PM
@Deleted
I added a formula to column L of the data tab to concatenate the month/day for today's month as an example of one way I believe what you're asking can be done.
Jan 23 2021 09:56 AM
I took the formula that @JMB17 had written and modified it so that the result of the formula is actually a full date in Excel's date format. As you'll see, the condition in the first part of the IF function remains the same, but the rest, in my version, no longer uses concatenation; rather it uses the DATE function to take the numbers of day, month, and year, to produce the standard numeric value that the date format then turns into a date. This is then consistent with your dates for holidays, paydays, etc.
Here's the new formula, as it appears in cell L2.
=IF(OR(N2=MONTH(TODAY()),N2="xx"),DATE(YEAR(TODAY()),MONTH(TODAY()),O2),"")
I still have to say, however, that it's not clear to me what you're doing with the rest of the workbook. But if you can make this work, go for it.
Jan 23 2021 12:18 PM - edited Jan 23 2021 01:42 PM
wow, thanks @JMB17 . beautiful formula.
thanks as well @mathetes. for this column i'll probably end up keeping the format as '00 - 00' but i can use the knowledge from your formula as well.
now, i wanted to see if it was possible to have a formula that would list out all the dates that fall in the same month as Today. i figure from there i should be able to finish up this section with index/match.
i'm trying to toy around with the below formulas, but i'm getting either #Value, #Calc or #Spill errors.
is this possible, or am i spinning my wheels?
=FILTER(Data!P:P,VALUE(LEFT(Data!M:M,2)=MONTH(TODAY())))
=FILTER(Data!P:P,LEFT(Data!M:M,2)=MONTH(TODAY()))
=FILTER(Data!P:P,MONTH(Data!M:M=MONTH(TODAY())))
Jan 23 2021 02:03 PM
@Deleted
As I've said before, I'm mystified by how you're approaching this, unable to figure out the goal.
I can, nevertheless, tell you that the SPILL error with FILTER means something is blocking the function from delivering all the results, not that there's something wrong with the formula. After trying to delete rows and columns adjacent to your own formula, I took your formula and copied it over to a new sheet--you can see the result here.
Clearly some more refinement is needed to the criteria to be applied in the FILTER function.
Jan 23 2021 03:20 PM
i've attached an updated file.
in the Data sheet, range )32:V33 is what i'm trying to accomplish. it looks like the formulas work on the Data sheet, but they don't work when i try to transfer over onto the Money sheet.
On the Money sheet, i can't get column L to transfer over. the text doesn't appear even those the formulas should be correct
Jan 23 2021 03:34 PM
Jan 23 2021 03:34 PM
Jan 23 2021 08:31 PM - edited Jan 23 2021 08:32 PM
Jan 23 2021 08:31 PM - edited Jan 23 2021 08:32 PM
just finished the entire expense section..current month's bills, next month's and a custom date range...
managed to get it done without a macro or pivot table..just formulas...very happy with it.
much more aesthetically pleasing in my view
Jan 23 2021 09:40 PM
@Deleted
I'm glad you managed. I would like to clarify the point that a Pivot Table is useful not for doing what you've been doing--which (if I understand it) is actually creating the expense records at a granular (by transaction) level. But the Pivot Table is for the purpose of summarizing expenses (or whatever the database happens to be) in a cross-tabulated way. So in mine, for example, all payments for groceries are summarized by month, each month showing a single total for groceries. Same for expenses for gas, car repair and maintenance. The database contains all the details; the pivot table takes those details and shows patterns over time, or patterns by category. Things like streaming audio and streaming video and other mainly discretionary expenses are sub-totaled as Discretionary, even while summarized by those more specific categories.
I create my database by downloading the details of checks written, bills paid, credit card transactions from the various financial institutions. Those all comprise the database. There are thousands of rows of data in a year. The pivot table creates manageable, intelligible summaries.