|
importing transactions video transcript:
This video will show you how easy it is to import transactions into xlyourfinances. We set up our accounts in the previous video and now we're going to begin downloading transactions. We will show you utilizing Chase Credit Card's website. One of the advantages of going to your actual credit card website and downloading the transactions yourself, is you can see the current balance of the credit card, and after you download the transactions into your import tab, you can see that you're in balance before you post the transactions. When you use a website that consolidates all your transactions for you they do not necessarily give you the balance of each credit card, so that you can verify that you got everything downloaded correctly. An additional advantage of going directly to your bank and your credit cards, is you're not sharing your password with a third party so it's a little more secure. And the last advantage, you're going to see in this process, is we're going to go through the transactions, and we're going to put our category labels on them. A lot of websites that aggregate the transactions for you, will assign categories, but it's not necessarily what you want. Sometimes they'll have up to 200 categories, which isn't effective when you're trying to summarize and analyze your finances. This is Chase Freedom that we're looking at here in the background. You see this download arrow that says "Download Account Activity". Every credit card is a little bit different, but everyone that I've dealt with has a download transactions section. So we're going to click on 'download activity' and it brings us to this screen. I'm going to download it as is, however, when you get into your own routine, you would most likely put a date range. You will see that xlyourfinances easily identifies duplicates for you, so a lot of times I'll purposely overlap my date range to include transactions I know I downloaded previously and then allow xlyourfinances to remove the duplicates so that way I know I didn't miss any transactions. For this example we're just going to hit download, and in Google Chrome you can see that the file appears at the bottom left and you can double click on it. Once the file opens in Excel with your transactions, the three main things you're looking for are the posting date, description and amount. I always use the posting date because transactions date can sometimes change, whereas the posting date is consistent. Also, the posting date, I have found is more relevant because that's the date it got charged to your credit card, but everyone might have their own preference. Whichever you choose, I would suggest that you stay consistent with it, you don't want to one time use a transaction date and another time use posting date because xlyourfinances will think this is two different transactions because the one time you downloaded it, you said it was on June 28 and the second time you downloaded it you said it was on June 30 and there's no way to verify that its the same transaction. So I highly recommend just use posting date every time.
We're gonna highlight these transactions, copy them and paste them into xlyourfinances on the import screen. I'm going to use paste special values, I have a shortcut, or you can just pull this drop down and click Paste Special Values.
I'm not concerned that it's not a perfect format because in the import screen these buttons make it really easy to get things aligned properly. The buttons across the top allow you to swap the data in this column with what's in this column. So if I click "swap" it just put the amounts in the description, I can click it again, you can see I'm moving these amounts the whole way over to the left. Obviously that's not what we want, so we're going to keep clicking that button. So now I have the amount in the correct column. As you can see, these buttons are not going to mess up the spreadsheet, they are just moving the data, left to right. So no matter what format your transactions show up in, it's easy once you get into the import screen to get it aligned correctly. This button, "flip amount", takes whatever amounts are shown, and puts the opposite sign on it. The purpose of this is that some downloads, have the transaction set up as positive, but if it's an expense we want it showing up as a negative. Just to show you the functionality of this. Let's just say this was a positive amount, when I hit "flip amount", you can see how it flips everything from positive to negatives. To put it back to the way it was. I hit "flip amount" again and those numbers now show up as negatives. Next we need to pull in the credit card name. When you click on this drop down, if you do not see the name of the credit card? For example, Chase, Discover, Capital One etc, you need to add them on the Summary tab and be sure to click the button "Click Here to refresh after editing". This will add accounts and categories to the drop-down options inside of the Import tab and updates the whole spreadsheet with your categories. So now when I come back to the Import tab I can see all those categories. So, whatever you choose from the drop-down at the top will fill in as the account when you click "Copy down account". It's going to take that account name, and it's going to put it into every line item that has a dollar amount in it.
After you get some data into the spreadsheet, one of the best features is "Auto-fill categories", click the button "Auto-fill Ctgys", it goes through your transactions and finds transactions that had similar descriptions imported and auto-labels them for you. It doesn't have to be a one to one match which is very helpful, but you do need to go through and make sure the categories are what you want for each of the transactions. So take a moment and scroll down and make sure that everything's labeled the way you want it to be. The next thing you do is add in any comments that you would like. So, this is an example, you went on a vacation to Disney and so you could type in "Disney" for each transaction related to that trip which will make it easier to filter and see the total spent on that trip. It is entirely up to you and each person will find their own way as to what they want to show up in the description. This is data that got pulled together from mint.com, and they truncated some of the credit card description so it's nice and clean, but it loses some of the detail. So, you'll find your own way, depending on where you download your information and how you want to sort it and keep track of what's going on. So if you are downloading from mint.com when you download to the transactions, paste them in here. You can literally click mint format, and it will take everything and put it into this proper format. Please watch the video "Download from Mint" that walks you through this process of downloading from Mint.com
If you notice, there's a button that says "Remove already posted". If you have a transaction in the import screen that's already been posted to the transactions tab, xlyourfinances will pick up on that, and drop in the label "already posted". When you click "Remove already posted", any line that has the words 'already posted' beside it, will be removed.
The other label you might notice is "flip sign". What this is saying is you have a transaction which should typically be the opposite sign (positive or negative) of what is about to post. This usually happens when you return something...so here something was bought and then we returned it. Xlyourfinances is just saying 'hey, normally this is a negative balance, are you sure this should be posted as a positive?'. Where this is helpful, is when you download transactions from a source, and they don't have the sign going the right way by default, and you'll see all these "flip sign" descriptions over here, and if you try to post it, it won't post any of them. Then you realize, you have the sign going the wrong way. So if this was returned, and you want to post it as is, you just put an 'X' in column K, and the 'X' tells xlyourfinances, post it anyway. I think I saw a couple other flip sign labels that we will over-ride with an 'X' and now at this point everything is labeled as "ok" to post.
A convenient feature on the Import tab is the ability to see the last transaction that was posted for that credit card or bank account. When I pull up an account, it shows the latest date of all the transactions posted for that account. I personally like to see the actual transactions that were posted on that date and in order to see them on this page, click the button "show", and it pops up a section at the top which displays the last 20 transactions for that account. Once you see what you need, you can just click "hide" and it hides those rows so that more of the screen can be seen for continuing to import transactions.
A powerful feature happening on the Import tab is the ability to see the account balance WITH the transactions about to be posted. If you recall, when we were looking at the Chase account online, the credit card balance was 1,616.36. The current balance in xlyourfinances is 2,307.02, however, with these transactions that we are about to post, the new balance will be 1,616.36. We know we can post these transactions and be reconciled to our online credit card balance and post with confidence. Just to show you how this works, if I delete this item, you can see how the balance went down to 1,096. So, this is what's been posted to xlyourfinances and this is your balance with what's on your import tab. So I'm gonna hit undo, but let's just say you were off a few dollars. You can come in here, you can actually type the online credit card balance to see exactly how far you're off. So, this is very helpful when you're trying to post transactions and make sure everything's in there like it should be. Just to show you one more example of how this works. Let's grab a Chase transaction that previously posted. And we're going to put it on the Import tab. See how it says already posted, I come up here and now I'm off, this was already posted and so I click "Remove already posted". It removes that one item, and now I'm back in line.
The "Duplicate" button. What this does is it goes down through and it looks for items that posted for that amount, and it tells you the date that are posted for that amount. So if you have recurring transaction you might see a date there that posted recently within a day or two, you're gonna get a note that says duplicate with a question mark for you to look into and make sure that it is truly a different transaction and not a duplicate.
No I will move on and hit "Post" so that everything with the label, "ok", will now post. I'm confident to do this because my balances tied out to my credit card. When I hit post, it takes a second because xlyourfinances needs to remove any filters out of the transactions tab to make sure it's ready to add them to the Transactions tab, it comes back and clears out the Import tab. So if I go to transactions tab and now go to the bottom, those transactions that we just downloaded from Chase are now appearing at the bottom. So you can see, my Chase balance of 1,616.36 is now showing on the summary tab and it's a good feeling once you reconcile and tie out completely. Now you can move on to your next credit card or bank account.
The other thing I wanted to show you is the validation error messages that pop up to let you know if a transaction is valid and ready to post. To indicate that a transaction is "already posted", it is looking at the account, the date and the amount. If these match up, it identifies the transaction as "already posted", even if it is a different category. The chances that you spent the exact same dollar amount on this day in a different category is slim. If you really do have a transaction like this and need to post it, you can just type an 'X' and it'll override the "already posted" message. Other validation items include "Description", valid "Account", valid "Category", valid date and valid amount. If you don't put anything in the description, that's a required field. So, it's not going to let you post with no description. If you go to hit post with no transactions labeled as "Ok" to post, it says, "No entries that have been validated as ok to post". So, let's just say you have a description, but you don't have a valid account, it now says "financial account not found", or let's say you have the financial account, but you have no date, "invalid date" label pops up. Let's put that date back in with no amount, "invalid amount", or if you have something that's not a number, it's an "invalid amount". So these labels help you identify what's wrong with your information before you post. This ensures only valid transactions are posted!
So that wraps it up for the Import tab. I truly believe this is one of the most powerful features of xlyourfinances. The tools on this tab make it easy to manipulate the data, get it into the right format, auto-fill your categories and remove transactions that have already been posted. Personally one of my favorite features of the entire workbook because so many times you're flipping back and forth between the Credit Card website and your spreadsheet trying to figure out if the transaction is already been posted or not. And it works so well that 99.9% of the time, I download the transactions, it shows me what's already been posted, I hit "Remove already posted" the balance ties, I auto-fill the categories, verify they're what I want them to be and hit post. And that's why I say in 10 to 20 minutes a week you can download all your transactions have them labeled in the right categories and have your bank and credit cards reconciled. And now you're spending your time looking at the summary, seeing where your money's going, looking at your budget and adjusting your spending habits instead of wasting time just consolidating your transactions. This wraps up our video on the Import tab. Thank you!
We're gonna highlight these transactions, copy them and paste them into xlyourfinances on the import screen. I'm going to use paste special values, I have a shortcut, or you can just pull this drop down and click Paste Special Values.
I'm not concerned that it's not a perfect format because in the import screen these buttons make it really easy to get things aligned properly. The buttons across the top allow you to swap the data in this column with what's in this column. So if I click "swap" it just put the amounts in the description, I can click it again, you can see I'm moving these amounts the whole way over to the left. Obviously that's not what we want, so we're going to keep clicking that button. So now I have the amount in the correct column. As you can see, these buttons are not going to mess up the spreadsheet, they are just moving the data, left to right. So no matter what format your transactions show up in, it's easy once you get into the import screen to get it aligned correctly. This button, "flip amount", takes whatever amounts are shown, and puts the opposite sign on it. The purpose of this is that some downloads, have the transaction set up as positive, but if it's an expense we want it showing up as a negative. Just to show you the functionality of this. Let's just say this was a positive amount, when I hit "flip amount", you can see how it flips everything from positive to negatives. To put it back to the way it was. I hit "flip amount" again and those numbers now show up as negatives. Next we need to pull in the credit card name. When you click on this drop down, if you do not see the name of the credit card? For example, Chase, Discover, Capital One etc, you need to add them on the Summary tab and be sure to click the button "Click Here to refresh after editing". This will add accounts and categories to the drop-down options inside of the Import tab and updates the whole spreadsheet with your categories. So now when I come back to the Import tab I can see all those categories. So, whatever you choose from the drop-down at the top will fill in as the account when you click "Copy down account". It's going to take that account name, and it's going to put it into every line item that has a dollar amount in it.
After you get some data into the spreadsheet, one of the best features is "Auto-fill categories", click the button "Auto-fill Ctgys", it goes through your transactions and finds transactions that had similar descriptions imported and auto-labels them for you. It doesn't have to be a one to one match which is very helpful, but you do need to go through and make sure the categories are what you want for each of the transactions. So take a moment and scroll down and make sure that everything's labeled the way you want it to be. The next thing you do is add in any comments that you would like. So, this is an example, you went on a vacation to Disney and so you could type in "Disney" for each transaction related to that trip which will make it easier to filter and see the total spent on that trip. It is entirely up to you and each person will find their own way as to what they want to show up in the description. This is data that got pulled together from mint.com, and they truncated some of the credit card description so it's nice and clean, but it loses some of the detail. So, you'll find your own way, depending on where you download your information and how you want to sort it and keep track of what's going on. So if you are downloading from mint.com when you download to the transactions, paste them in here. You can literally click mint format, and it will take everything and put it into this proper format. Please watch the video "Download from Mint" that walks you through this process of downloading from Mint.com
If you notice, there's a button that says "Remove already posted". If you have a transaction in the import screen that's already been posted to the transactions tab, xlyourfinances will pick up on that, and drop in the label "already posted". When you click "Remove already posted", any line that has the words 'already posted' beside it, will be removed.
The other label you might notice is "flip sign". What this is saying is you have a transaction which should typically be the opposite sign (positive or negative) of what is about to post. This usually happens when you return something...so here something was bought and then we returned it. Xlyourfinances is just saying 'hey, normally this is a negative balance, are you sure this should be posted as a positive?'. Where this is helpful, is when you download transactions from a source, and they don't have the sign going the right way by default, and you'll see all these "flip sign" descriptions over here, and if you try to post it, it won't post any of them. Then you realize, you have the sign going the wrong way. So if this was returned, and you want to post it as is, you just put an 'X' in column K, and the 'X' tells xlyourfinances, post it anyway. I think I saw a couple other flip sign labels that we will over-ride with an 'X' and now at this point everything is labeled as "ok" to post.
A convenient feature on the Import tab is the ability to see the last transaction that was posted for that credit card or bank account. When I pull up an account, it shows the latest date of all the transactions posted for that account. I personally like to see the actual transactions that were posted on that date and in order to see them on this page, click the button "show", and it pops up a section at the top which displays the last 20 transactions for that account. Once you see what you need, you can just click "hide" and it hides those rows so that more of the screen can be seen for continuing to import transactions.
A powerful feature happening on the Import tab is the ability to see the account balance WITH the transactions about to be posted. If you recall, when we were looking at the Chase account online, the credit card balance was 1,616.36. The current balance in xlyourfinances is 2,307.02, however, with these transactions that we are about to post, the new balance will be 1,616.36. We know we can post these transactions and be reconciled to our online credit card balance and post with confidence. Just to show you how this works, if I delete this item, you can see how the balance went down to 1,096. So, this is what's been posted to xlyourfinances and this is your balance with what's on your import tab. So I'm gonna hit undo, but let's just say you were off a few dollars. You can come in here, you can actually type the online credit card balance to see exactly how far you're off. So, this is very helpful when you're trying to post transactions and make sure everything's in there like it should be. Just to show you one more example of how this works. Let's grab a Chase transaction that previously posted. And we're going to put it on the Import tab. See how it says already posted, I come up here and now I'm off, this was already posted and so I click "Remove already posted". It removes that one item, and now I'm back in line.
The "Duplicate" button. What this does is it goes down through and it looks for items that posted for that amount, and it tells you the date that are posted for that amount. So if you have recurring transaction you might see a date there that posted recently within a day or two, you're gonna get a note that says duplicate with a question mark for you to look into and make sure that it is truly a different transaction and not a duplicate.
No I will move on and hit "Post" so that everything with the label, "ok", will now post. I'm confident to do this because my balances tied out to my credit card. When I hit post, it takes a second because xlyourfinances needs to remove any filters out of the transactions tab to make sure it's ready to add them to the Transactions tab, it comes back and clears out the Import tab. So if I go to transactions tab and now go to the bottom, those transactions that we just downloaded from Chase are now appearing at the bottom. So you can see, my Chase balance of 1,616.36 is now showing on the summary tab and it's a good feeling once you reconcile and tie out completely. Now you can move on to your next credit card or bank account.
The other thing I wanted to show you is the validation error messages that pop up to let you know if a transaction is valid and ready to post. To indicate that a transaction is "already posted", it is looking at the account, the date and the amount. If these match up, it identifies the transaction as "already posted", even if it is a different category. The chances that you spent the exact same dollar amount on this day in a different category is slim. If you really do have a transaction like this and need to post it, you can just type an 'X' and it'll override the "already posted" message. Other validation items include "Description", valid "Account", valid "Category", valid date and valid amount. If you don't put anything in the description, that's a required field. So, it's not going to let you post with no description. If you go to hit post with no transactions labeled as "Ok" to post, it says, "No entries that have been validated as ok to post". So, let's just say you have a description, but you don't have a valid account, it now says "financial account not found", or let's say you have the financial account, but you have no date, "invalid date" label pops up. Let's put that date back in with no amount, "invalid amount", or if you have something that's not a number, it's an "invalid amount". So these labels help you identify what's wrong with your information before you post. This ensures only valid transactions are posted!
So that wraps it up for the Import tab. I truly believe this is one of the most powerful features of xlyourfinances. The tools on this tab make it easy to manipulate the data, get it into the right format, auto-fill your categories and remove transactions that have already been posted. Personally one of my favorite features of the entire workbook because so many times you're flipping back and forth between the Credit Card website and your spreadsheet trying to figure out if the transaction is already been posted or not. And it works so well that 99.9% of the time, I download the transactions, it shows me what's already been posted, I hit "Remove already posted" the balance ties, I auto-fill the categories, verify they're what I want them to be and hit post. And that's why I say in 10 to 20 minutes a week you can download all your transactions have them labeled in the right categories and have your bank and credit cards reconciled. And now you're spending your time looking at the summary, seeing where your money's going, looking at your budget and adjusting your spending habits instead of wasting time just consolidating your transactions. This wraps up our video on the Import tab. Thank you!
Notice:
All of XLYourFinances' spreadsheets are protected by copyright laws. Breaking into the spreadsheet’s macros or breaching its password protection not only makes guarantees of the spreadsheet’s usability and support for the spreadsheet null, but is also considered a copyright infringement, as the methodologies and techniques utilized are proprietary and owned by XLYourFinances, LLC. Please refer to the terms agreement for more details on the terms of use.
All of XLYourFinances' spreadsheets are protected by copyright laws. Breaking into the spreadsheet’s macros or breaching its password protection not only makes guarantees of the spreadsheet’s usability and support for the spreadsheet null, but is also considered a copyright infringement, as the methodologies and techniques utilized are proprietary and owned by XLYourFinances, LLC. Please refer to the terms agreement for more details on the terms of use.
| terms_agreement.pdf |
XLYourFinances.com is owned and operated by XLYourFinances, LLC.
All XLYourFinances.com spreadsheets are copyright protected, © 2010-2023.
All XLYourFinances.com spreadsheets are copyright protected, © 2010-2023.