TradingJournal Dashboard - Dynamic Decimal Points/Spaces

Hi all,

As per the topic, what I'm trying to achieve is to select a particular currency from dropdown list (column E) and the columns L, M, N, O & X shoud be formated to have specific decimal places as per the currency selected. So far, I was only able to achieve this by conditional formatting but the list of currency is long so having so many conditions doesn't seem like doable approach.

Workbook with sample data is included and this is a macro enabled workbook as I have VBA formulas so profit/loss calculations. More information is included in the workbook.

What determines the number of decimal places to use? Is there some cell that acts as a reference to know the appropriate number of decimal places for the selected currency?

Originally Posted by deadlyduck

What determines the number of decimal places to use? Is there some cell that acts as a reference to know the appropriate number of decimal places for the selected currency?

No, there's no cell to act as a reference point. Basically I'm using this Journal and I'm aware of how many decimal place would a symbol have. So, was hoping that if I provide the 3 major currency symbols as sample data as they would have the similar decimal points. And if someone could help me code a solution I could expand the formula to include the other currencies.

For example:
1. EURUSD, GBPUSD, AUDUSD will have 5 decimal points i.e. #.#####
2. XAUUSD, XAUEUR, USOil, UKOil will have 2 decimal places i.e. #.## or ####.##
3. AUDJPY, EURJPY, GBPJPY, USDJPY will have 3 decimal place i.e. ##.### or ###.###

I do have a table in the MasterData worksheet. And I can add a column there to give reference to the number of decimal places for each symbol. But not sure how to go about it i.e. just plain number 2, 3, 5 or in formate such as #.##, #,###, #.#####

The attached should be doing what you need.

I added a column (column E) to the SYMBOLS section of the MasterData sheet which shows the type of number format for any particular symbol- you'll obviously need to check that these are updated.
When you change column E on the Trades-Master sheet, the applicable formatting will be automatically applied to the relevant cells.

Originally Posted by deadlyduck

The attached should be doing what you need.

I added a column (column E) to the SYMBOLS section of the MasterData sheet which shows the type of number format for any particular symbol- you'll obviously need to check that these are updated.
When you change column E on the Trades-Master sheet, the applicable formatting will be automatically applied to the relevant cells.

Brilliant work. Thanks very much. This is exactly what I was looking.

I do want to ask you something. Because the of the new column (column E) in the SYMBOLS table, my other formula isn't working now. So, if I were to shift column E to the end of the table i.e. column K, am I correct in changing the column number from 2 to 8 in the attached image of your formula?

Hi,

Yes, change the 2 to 8 or whatever the column is in the symbols range (column D is the first column of the symbols range)
You can also delete the line that begins Debug.print as it's just for testing purposes.