Excel can help you reconcile your accounts and prepaids with ease. And below, I’m going to go over an updated version of my prepaid expenses template. This new template will make it easier to add items and years, by going across rather than vertically.
There are only a few columns where you’ll need to enter any inputs in, and that’s in columns A:D. This is where you enter the type of prepaid you want to track, the starting balance, along with how many periods you want to amortize it over (duration), and the first period the amortization should begin. Do not change the values in the period cost field as they are calculated by formulas.
Below, you can also enter the actual GL balance, to help you calculate any differences between the balances and the GL. In the example above, I have a variance of 1,000.
Once you enter the inputs for the prepaids, the rest of the template will populate, with the first month representing the first period, followed by period 2, and so on. In the below screenshot I have set Jan 2025 as my first month but that can also be adjusted and it is highlighted in yellow to let you know that this is a field which you can change.
There are a total of 60 periods in the template to accommodate up to five years of data, but this can be expanded.
The template will calculate your total amortized cost for the period, as well as the year-to-date (YTD) amounts since the start of the year, and those will reset again after every 12 periods. This way, you can track both your individual monthly amortization costs as well as your running total for the year. This is why you may want to have your first period be January, even if you don’t want to start amortizing in that month — you can simply change the first period which amortization starts, to ensure the YTD values calculate properly.
How to get the template
You can download this template for free, here. The free version is locked and is limited to tracking five items. If you want more, you can get the full version from here, which is unlocked.
If you like the 2025 Prepaid Expenses Template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
It’s a new year and with the help of a good calendar, you can ensure you stay on top of not only your deadlines and tasks but goals as well. I have a free template which you can download and use for such purposes. With just a few clicks, you can populate a new month, select a custom image, and also have key dates and deadlines appear. And all this is available in a format which is easy to print out.
How the calendar template works
The calendar will populate the days of the month you specify, including the days before and after the end (these dates will be highlighted in grey). You can change the picture for the month and adjust its size depending on how big or small you want it to be. To do this, you can just right-click on the picture and select Change Picture. I’ve also left an area off to the left as blank, giving you room to make any notes.
If you want to change the month, year, or the day that the week starts (either Monday or Sunday), go to the Inputs tab where you can modify those settings.
This is also the tab where you can enter any tasks or deadlines you want to track. They don’t have to be for the current month. Suppose, for example, you have a property tax bill that’s due in July. You can put that task there along with any others you want to track:
These dates will show on the calendar depending on the month selected. If January 2025 is selected, then only dates related to that month will appear.
The advantage with this template is that you can enter all your tasks and deadlines for the year and they will show only for the month you specify. This can make it easier to stay on top of tasks without having to remember to enter them all in for that specific month; you can enter them ahead of time and setup recurring ones as well. There is room to accommodate up to five tasks/deadlines for each day.
If you like this Free Calendar and Task Manager Template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
Staying on top of your credit cards is an important goal when trying to get from out of debt. A credit card payment calculator is a valuable tool that can help you do just that, helping you to become debt-free sooner. The credit card payment calculator template I’ll go over in this post is free and user-friendly as well, designed to be simple to use, even for novice Excel users.
How to use the credit card payment calculator
The credit card payment calculator template in Excel is designed to provide a clear picture of your credit card debt and help you with developing a strategy to pay it off. All the fields in yellow require user input:
For the monthly payment, you need to enter at least a minimum balance. This is if you plan to pay the same amount every month. You can also specify a percentage of the balance you want to pay down each month. However, if you enter a percentage, you’ll also want to enter a minimum. Otherwise, your balance will never end up going to zero.
What’s new in this template compared to the previous version of the calculator are a couple of things. The first is the ability to add an extra payment:
As with the yellow input fields, you can enter any one-off payments in the extra payment field. This will get applied directly to your balance and reduce your principal. It will also update the rest of your payment schedule.
Another new feature of this template is the ability to enter a goal for when you want to pay the credit card off by:
In the above example, I have entered a date of Jan. 1, 2025. And based on my starting dating of Oct. 1, 2023, it calculates that I will need to make a monthly payment of at least $344.55 to have the credit card balance paid off by that date. Now, if I go back and enter this as my minimum payment, this is what the calculator looks like:
There’s only a minimal balance remaining by the end of Dec. 2024 based on the new minimum payment but it is effectively paid off by that point.
Why managing your credit card balances is important
One of the pillars of personal finance is understanding and managing debt effectively. Credit cards, while useful, can become a financial problem when not handled effectively. This calculator highlights how much in total interest you will pay based on your payment schedule, to help show just how much the debt is costing you. And the greater the debt and the higher the rate of interest, the more costly it will be.
For instance, consider a credit card balance of $20,000 with an annual interest rate of 15%. With a monthly payment of $300, it would take approximately 12 years to pay off the debt, accruing more than $23,300 in interest — that’s more than the initial balance. If you increased the payment by just $50, that would mean paying off the credit card in about 8.4 years and the interest costs would drop to $15,330. And if you paid $400 per month, you would pay off the card in less than seven years, while incurring interest costs of $11,600. You can do all these calculations right within this calculator. You can see that even with a $50 increase you can drastically speed up your repayment schedule.
Credit card debt can be a significant hurdle when aspiring to larger financial goals like purchasing a home. Lenders scrutinize your debt-to-income ratio, and a high ratio could result in unfavorable loan terms or even disqualification. By using the credit card payment calculator to expedite your debt payoff, you enhance your credit profile, bringing you a step closer to home ownership and other financial goals.
Tailoring your payoff strategy
The what-if scenario feature can be especially useful, allowing you to determine a strategy that aligns with your financial objectives so that you know how much you need to pay every month. Whether you aim to be debt-free before a significant life event or simply wish to reduce the interest burden, this calculator lays the groundwork for a well-informed plan.
The credit card payment calculator template is more than just a tool—it’s a catalyst for proactive debt management. It nudges you to think beyond the minimum payment, encouraging a more aggressive approach towards debt elimination. By visualizing the impact of your payment strategy, you are better positioned to make choices that propel you towards financial freedom faster.
If you like this Free Credit Card Payment Calculator template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
In the fast-paced world of investing, identifying trending stocks in Excel can provide a valuable edge for investors seeking profitable opportunities. Fortunately, with the power of Excel’s Power Query and the ability to connect to a website’s API, accessing real-time data and uncovering trending stocks has become more accessible than ever. In this article, I will go through the process of using Power Query to connect to a website’s API and importing in trending stock information.
Why should investors try to identify trending stocks?
As an investor, it is crucial to identify trending and popular stocks for several reasons:
Profit Potential: Trending and popular stocks often have significant profit potential. When a stock is gaining popularity, it usually attracts more investors, leading to increased demand and potentially driving up the stock price. By identifying these stocks early, you can position yourself to benefit from the price appreciation and generate higher returns on your investment.
Liquidity: Popular stocks tend to have higher liquidity, meaning there is a larger pool of buyers and sellers in the market. This liquidity allows you to enter and exit positions more easily, ensuring that you can buy or sell shares without significantly impacting the stock’s price. Investing in liquid stocks provides flexibility and reduces the risk of being unable to execute trades at desired prices.
Market Validation: The popularity of a stock often reflects positive market sentiment and investor confidence. When a company is trending and gaining attention, it may indicate that the market believes in its growth prospects and overall performance. By identifying such stocks, you can align your investment choices with market sentiment and increase the likelihood of investing in companies with strong fundamentals and future growth potential.
InformationAvailability: Popular stocks generally attract more media coverage, research reports, and analyst attention. This increased coverage provides you with a wealth of information and analysis to make more informed investment decisions. You can leverage these resources to understand the company’s financial health, competitive position, industry trends, and other relevant factors that can impact the stock’s performance.
How to get trending stocks in Excel
To get trending stock data into Excel, you should start with finding a good source that you can rely on for trending data. For this example, I’m going to use apewidsom.io, which provides free access to its API using the following url: https://apewisdom.io/api/. Here’s how I’m going to use that to pull in trending data:
Extract the data using Power Query. To get started, I’ll select the Data tab in Excel and click on the From Web option.
Next, there will be a field to enter the URL, this is where I will paste the link that the API references:
After clicking OK, Power Query will launch. When the screen opens up, the following table appears. I click on List to open up another table.
After clicking that, there’s another list of records.
Here, I’ll select the option to convert to table and leave the default settings and click OK. Then, there is another list of records. Clicking on the button with the arrows going in opposite directions will expand them:
After expanding out those records, the table will now looks like a list of stocks and metrics relating to mentions, upvotes, and overall rank popularity:
Now that this has been setup, I will convert this into a Power Query function. To do that, I’ll click on the Advanced Editor button:
In the editor, I will add a line at the top to specify the name of the function. And at the bottom, I will add a line to circle back to it. Lastly, I’ll add a variable for the URL as well, and put that where the link used to be:
Next, with the custom function created, I’m going to go back into Excel and create a list of all the URLs I want to use this function on. In this situation, I’m going to adjust the page number at the end of the URL so that I have pages 1 through 5:
I’ll load this table, called URLtable, into Power Query using the From Table/Range button when selecting data. Next, I’ll select the Add Column tab and select Invoke Custom Function:
Then, I reference the query as well as the URL variable that is to be used:
Then, there will be a field with the results, in table format. Again, this needs to be expanded out:
That will leave a list of stocks starting from page 1 all the way through page 5. You can remove the URL field, which is no longer needed:
If you don’t want to follow through all those steps yourself, you can download the template I’ve created here.
If you liked this post on Get Trending Stocks Into Excel Using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
The World Cup is starting next month and I have a free template for you to use whether you want to track the matches or make predictions with a group of friends. The entire schedule is downloaded within the file, and you can enter the the scores — both actuals and predictions.
How the template works
There are four tabs on the template:
Actuals: This sheet name needs to remain intact as when comparing your predictions, this is the source data that the prediction results will compare against.
Prediction.Blank: This is a blank sheet that is the same as the actuals that you can use for making predictions.
Prediction.Results: This is where you should post your final prediction results. Column A (name) is for the name of the person whose predictions they are. In columns B:D, you just need to copy those fields from the Predction.Blank page for any predictions you or someone else makes. It’s important the values are in the correct fields for everything to be recorded correctly. Also, make sure to paste them as values to ensure the formulas aren’t being copied over.
I’ve also added a section below the main prediction table that shows the teams you predicted to advance to each elimination stage:
This should also be copied over to the Prediction.Results tab. The stage should go in column B and the team will go in column C. I’ve left an example of how the Prediction.Results tab should be filled in within the template so that you can follow along. The table will automatically calculate and compare against the actuals.
Scoring.Rules: Here you can set up how points are allocated if the score, result, and total number of goals are correct. You can also specify if the teams need to be correct (applicable for knockout rounds) and how many points to assign if you’ve got the right team in the right elimination stage.
The basic idea is that you can make a copy of either the Actuals or Prediction.Blank sheet and give to someone to fill in. Once you get back their predictions, you can paste them into the Prediction.Results table. Then, once the tournament begins, you populate the Actuals. After that, it’s just a matter of updating the pivot table on the Prediction.Results page to see who has the most points.
Enter scores in the 0:0 format
One important item to note is that when entering scores, there should be a number followed by a colon, then by another number. There should be no spaces any no different characters, otherwise you will get an error and the value won’t be read properly.
In the case of shootouts and extra time, there is no extra field to enter these values in. For the purpose of minimizing the complexity of formulas, only one column is used for scores. The workaround for this is if a team wins in extra time or a penalty shootout, simply add 1 to their score so that instead of entering it as 1:1 (5:4) it would just be 2:1.
Tables will update automatically
The benefit of using the template is that the tables will automatically update based on your selections. This will help you in determining which teams play one another in the knockout rounds.
The template does factor in tiebreakers but in the event that a rare situation comes up where the tiebreaker doesn’t calculate correctly (e.g. it comes down to fair play points) then you have the option to override the values.
Next to the tables, there is another table for overrides:
In this example, I have overridden the results so that these will be the standings for Group B regardless of what the spreadsheet has calculated. You don’t need to list every team in a group and only need to list the teams that are tied. You also don’t need to specify the group letter.
Adjust the times for your time zone
Another feature that I’ve added here is to adjust the game times for your particular time zone. On the left-hand side you can specify how many hours you need to adjust for GMT. Remember that the value here will need to factor in for any daylight savings that may be in effect for your location (Qatar doesn’t adjust its time).
For example, in my region, my GMT adjustment is -7 but after factoring in for daylight savings in November, it becomes -8. So please remember to adjust for any daylight savings adjustment that might happen between now and the start of the tournament. For GMT -8, the local time would adjust by 11 hours (Qatar is +3), and so a match starting at 7pm local time would be playing at 8am in my time zone.
Highlight teams with a Watchlist
You can also highlight teams you want to track on the schedule by adding them to a Watchlist next to the schedule. For example, suppose I want to follow any games involving Canada and the USA:
Any values that are entered under the Watchlist will highlight corresponding matches on the schedule. For this to work, you need to enter the team names the same way that they are spelled out on the match schedule.
Download
This template is available free of charge, and you can download it here. It is locked to ensure that the formulas remain intact and nothing gets accidentally erased or overwritten.
If you like the Free World Cup 2022 Prediction Template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Warren Buffett fans like to track the billionaire investor’s moves, and a good way to do that is through Berkshire Hathaway’s 13f filings. His company reports its holdings every quarter, showing where there were changes in its positions. By looking at multiple filings, investors can track the changes from one period to another. With this free template, you can do that quickly and easily all on your own. All you have to do is specify the specific filings that you want to compare to one another.
How the template works
This template uses PowerQuery to grab the data from the 13f filings. It will then compare the two to find the changes in share count.
There are only two inputs you need for the template, both are on the Current.Holdings tab. You’ll need to paste the URL for the current 13f filing and the previous one (or whichever filings you want to compare against one another). It can sometimes be a bit tricky to get exactly what you’re after. Here’s how you can find the 13f filings for Berkshire Hathaway to use in this template:
Do a search for 13f so that you can see just the 13f filings on that page:
4. Find the reporting period you want and click on the Filing button — don’t click on the actual description next to it.
5. When you open up the link, you should see multiple files you can open. Select the information table that is in html format:
When you open the file, you should see the holdings in a table format:
If this is what you see, then the link you’ve downloaded will work. Note, on older 13f filings (e.g. 2013 and older), the format is in a text file and they won’t work with this template.
6. Copy the link and enter it into one of the fields on this template, either for the new filing or the old one.
7. Once you’ve filled in both the new and old links, then go to the Data tab in Excel and click on Refresh All. This will update the queries that the template relies on, and calculate the changes.
Now the different sheets will update:
Current.Holdings: this will show you the current holdings as per the New13f file
Change.In.Holdings: this will show you the change between the two filings. The change will be reflected in total shares and as a % of change in shares.
Old.Holdings: this will show the number of shares held per the Old13f file.
If you like the Berkshire Hathaway 13f Template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
