Why Datetime Formatting for Crypto API Data is the Most Important Part of Your Trading Analysis

Published in

Cryptosheets

5 min readApr 10, 2020

Being able to instantly convert back and forth between hundreds of different datetime & timestamp formats is a game changer for any analyst. Here’s how to do it in 5 seconds

Sexy right? Pound for pound this might be the most powerful Excel tool I’ve used my entire professional trading career (Bloomberg doesn’t count ;))

If you’ve ever spent time as a professional investment analyst, trader, portfolio manager or similar then you know that “pain” is another word for re-formatting date time data in columns

Example of switching datetime formats on the fly using Cryptocompare API data

Salvation for analysts and traders working with models is now just a 12 letter word (_formatDates)

Important Modeling Tips for Traders & Analysts

Check your timezone
Check your timestamp
Check your timezone again*
Check your local machine/software timezone settings
Too late, you already missed the trade…

Sounds ridiculous right…?
Traders can suffer huge losses by not adjusting their trading or analytics models to match their computer’s local operating system time or simply by virtue of the different date time formatting conventions in their locality

Example
This is the EXACT same spreadsheet seen from Singapore and San Francisco at the same time.

Both users specified the same date time format
Microsoft Windows Excel automatically reverted it to each user’s respective local convention where day is quoted ahead of month instead of the other way around

Singapore user’s machine automatically adjusts to local date convention formatting… which is in the future*

Help desk response:
“...hhmmm yes it looks as though you’re trying to trade futures but you have defined SPOT instead … but the problem is also that, eh well you’re trying to do it IN the future…. do you understand? or have you also gone cross eyed as well…?”
Customer reaction:
“(WTF#&%^>??)… by the way — who the hell ever invented UNIX timestamps?”

Load this template to learn how to use **_formatDates** **AND USE IT OFTEN**

Usage

For any Cryptosheets Core custom function you can simply add the _formatDates argument to any formula and then define the date format output you want to display.

TIP: you can define multiple different formats separated by commas to display multiple different formats for the same datetime value

This can be a helpful reference to convert datetime data from other/multiple datasets
You can use this to quickly build multiple aggregations that require specific datetime formats
See examples at: https://docs.cryptosheets.com/functions

_FormatDates — Reference Table

parameter | example | description

BASIC & COMMON

MM-DD-YYYY | 12-10-2019 | Date with -

MM/DD/YYYY | 12/10/2019 | Date with /

YYYY-MM-DD | 2019-12-10 | Date w/ yr first

MM-DD-YYYY hh:mm:ss.000

LT | 8:30:00 PM | Time

LTS | 8:30:25 PM | Time with seconds

L | 9/4/1986 | Month numeral, day of month, year

l | 9/4/1986 |

LL | 4-Sep-86 | Month name, day of month, year

ll | 4-Sep-86 |

LLL | 9/4/1986 20:30 | Month name, day of month, year, time

lll | 9/4/1986 20:30 |

LLLL | Thursday, September 4, 1986 8:30 PM | Month name, day of month, day of week, year, time

llll | Thu, Sep 4, 1986 8:30 PM |

NUMERICAL DATE REPRESENTATIONS

(HINT: These can be VERY POWERFUL)

M | 1 2 ... 11 12 | Month

Mo | 1st 2nd ... 11th 12th |

MM | 01 02 ... 11 12 |

MMM | Jan Feb ... Nov Dec |

MMMM | January February ... November December |

Q | 1 2 3 4 | Quarter

Qo | 1st 2nd 3rd 4th |

D | 1 2 ... 30 31 | Day of Month

Do | 1st 2nd ... 30th 31st |

DD | 01 02 ... 30 31 |

DDD | 1 2 ... 364 365 | Day of Year

DDDo | 1st 2nd ... 364th 365th |

DDDD | 001 002 ... 364 365 |

d | 0 1 ... 5 6 | Day of Week

do | 0th 1st ... 5th 6th |

dd | Su Mo ... Fr Sa |

ddd | Sun Mon ... Fri Sat |

dddd | Sunday Monday ... Friday Saturday |

e | 0 1 ... 5 6 | Day of Week (Locale)

E | 1 2 ... 6 7 | Day of Week (ISO)

w | 1 2 ... 52 53 | Week of Year

wo | 1st 2nd ... 52nd 53rd |

ww | 01 02 ... 52 53 |

W | 1 2 ... 52 53 | Week of Year (ISO)

Wo | 1st 2nd ... 52nd 53rd |

WW | 01 02 ... 52 53 |

YY | 70 71 ... 29 30 | Year

YYYY | 1970 1971 ... 2029 2030 |

Y | 1970 1971 ... 9999 +10000 +10001 |

`` | Note: This complies with the ISO 8601 standard for dates past the year 9999 |

gg | 70 71 ... 29 30 | Week Year

gggg | 1970 1971 ... 2029 2030 |

GG | 70 71 ... 29 30 | Week Year (ISO)

GGGG | 1970 1971 ... 2029 2030 |

TIME SPECIFIC

Often used by algo traders, HFT funds and quants

A | AM PM | AM/PM

a | am pm |

H | 0 1 ... 22 23 | Hour

HH | 00 01 ... 22 23 |

h | 1 2 ... 11 12 |

hh | 01 02 ... 11 12 |

k | 1 2 ... 23 24 |

kk | 01 02 ... 23 24 |

m | 0 1 ... 58 59 | Minute

mm | 00 01 ... 58 59 |

s | 0 1 ... 58 59 | Second

ss | 00 01 ... 58 59 |

S | 0 1 ... 8 9 | Fractional Second

SS | 00 01 ... 98 99 |

SSS | 000 001 ... 998 999 |

SSSS ... SSSSSSSSS | 000[0..] 001[0..] ... 998[0..] 999[0..]

Z | -07:00 -06:00 ... +06:00 +07:00 |

ZZ | -0700 -0600 ... +0600 +0700 |
z or zz | EST CST ... MST PST | Time Zone

X | | Unix Timestamp

x | | Unix Millisecond Timestamp

You’re welcome ;)

ADDITIONAL RESOURCES

TAGS : datetimeutc, unix, iso, timestamp, crypto, trading, excel, googlesheets, spreadsheet, crypto data, bitcoin, blockchain, data, convert, format, syntax, date convention, google sheets convert unix timestamp to date, excel timestamp to date, microsoft excel convert unix timestamp to date