Excel formulas not calculating

Copper Contributor

Hi everyone,

I'm a trader and I use Excel for my trading journal. I'm currently facing an issue where Excel is not calculating my total contracts anymore. I input my entry price, stop loss, 1% risk, and take profit. Previously, Excel would automatically calculate the number of contracts I could use, but it's not doing that anymore. Any suggestions on what I can do to fix this issue?

My thoughts: I have a few lines where I only enter data for take profit 2 and trade 3. This shouldn't be causing the error, right? Additionally, I've attempted to resolve it by going to Formulas and changing the 'calculation options' to 'automatic,' but that doesn't seem to solve the problem.

Any guidance or advice would be much appreciated. Thanks!

Edit: It's the words:  Aantal contracten

Tradingjournal pic.png

13 Replies


Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@Hans Vogelaar 


First time using this method. Curious to see if it works. Since the file is currently vulnerable, I'll delete it once the issue is resolved. I suspect the file is meant to be online to investigate the issue. Otherwise, I'd appreciate an explanation of what you mean exactly. There arent much personal things here for me.


Thanks, I was able to download the workbook (I'll delete it as soon as possible).

The worksheet is protected by a password. Could you provide that password? If you'd rather not post it in public, you can send it to me in a private message (click on my user picture).

Are you sure there's a password on it? I didn't put a password on it. When I go to the file and check access management, your name, Hans Vogelaar, is listed. Next to your name, it says 'can edit.' It should work fine, right? Otherwise, please let me know where I can find that password because I don't see it anywhere.


This is what I see in the Review tab of the ribbon:


When I click Unprotect Sheet:


I don't know the password, obviously.

I took a look. I didn't personally create this Excel file. The admin doesn't know how to resolve this either, that's the reason i came to this forum.. He doesn't share the password. So, this means it's not possible. Are there any other options for me to resolve it on my own?


See the attached workbook; I removed the protection. Be careful - it is now easy to mess things up.

You should be able to change values in for example column D or column I, and see column M change.

Huh, how did you manage to remove the protection?

Anyway, it still doesn't work for me. When I enter my entry, stop loss, 1% risk, and finally my TP1, it still doesn't calculate my order size and number of contracts.

From what I gather, it does calculate automatically for you? Not for me. Or maybe I misunderstood you. Yesterday, I ensured that my decimal symbol in the control panel matches the one in Excel options. But everything seems correct. What's odd is that other people use a comma in the P&NL collum, and I can use both points and commas, and both work for me. Could this be the issue? They all find it strange, and it shouldn't be like this.



That looks to me as if it was created for options trading: is that right?


In any event, since you're having trouble with it anyway, why not create your own? Create it to do your own tracking as you want it to. If you're still relatively speaking a beginner with Excel, one of the best ways to learn Excel is to create a spreadsheet that tracks something you care about. Come here to ask for help if you come up against a wall of some kind. But tracking options trades -- I do it too -- is relatively easy.


I'm afraid I cannot help with this - I have no idea what the formulas should do...

Thanks, @Hans Vogelaar, for all the help and time. I had hoped it was a small issue that might be easily resolved, but unfortunately.

@mathetes, thanks for the info. Indeed, I engage in options trading. I think I'll create my own trading journal in Excel. I found this one ideal, but unfortunately, it's having issues now. However, with some effort, I can make my own.

I'm going to do some research on Google, but to make it easier for myself, since you guys have more knowledge, which tutorial is the best for this situation to learn the specifics of Excel to create such a trading journal? Thanks in advance.

best response confirmed by Hans Vogelaar (MVP)



to make it easier for myself, since you guys have more knowledge, which tutorial is the best for this situation to learn the specifics of Excel to create such a trading journal?


Rather than a is a good resource (and it does have some  tutorials) for free references on various formulas and functions. What you're talking about for tracking options trades is mostly very basic, unless you get to the point of high level summaries of, say, a year's trades. But even that, if you design well, can be pretty basic.  There are many YouTube video tutorials on basic and advanced Excel techniques. And of course there are basic books. Any bookstore, or Amazon, can be a source of books.


I'd begin with whatever you do on paper, just automating some aspects of it...



Just wanted to drop a quick note to say thanks for pointing me in the right direction regarding my Excel challenge. Your suggestions did the trick.

Following your advice, I've set up a trading journal in Excel, automating some of the stuff I used to scribble down on paper. Thanks for taking the time to share your insights.