Crypto Ledger API ETL Script Development

Shane Sapolis

Backend Engineer

Microsoft Excel

Python

Cryptocurrency & Blockchain

Crypto Ledger API ETL

This was a small API-based ETL script I worked on that would pull valuation data, parse it, perform basic arithmetic functions, and load the data into the appropriate cells located in a specified Excel worksheet.

User Input

The script would first take user date input in a specified format, validate it, perform Unix epoch date conversions for use with the API, and utilize the data as variables for subsequent functions.

def date_entry(self, start_date=None, end_date=None, date_range=None):
		if date_range is None:
			date_range = []
		self.start_date = start_date
		self.end_date = end_date
		self.date_range = date_range
# Loops until valid start date is entered
		while True:
			try:
				start_date = datetime.strptime(input('Enter transaction start date as mm/dd/yyyy: '), '%m/%d/%Y')
				break
			except ValueError:
				print('Please enter a valid date/date range in mm/dd/yyyy format: ')
# Loops until valid end date is entered
		while True:
			try:
				end_date = datetime.strptime(input('Enter transaction end date as mm/dd/yyyy (Leave blank if running for a single day): '), '%m/%d/%Y')
				if end_date and end_date < start_date:
					print('End date must not be earlier than start date. Please enter a valid date range: ')
					return self.date_entry()
				if not end_date:
					break
				break
			except ValueError:
				if end_date is not None:
					print('Please enter a valid date/date range in mm/dd/yyyy format or leave end date blank for single day transaction: ')

#Performs Unix epoch time calculations on dates within range. This is used to compare transaction dates with CoinGecko API data as dates
#are represented within the retrieved JSON data in Unix epoch time format.
	def epoch_calc(self, date_range):
		self.date_range = date_range
		epoch_range = []
		for date in date_range:
			epoch_range.append(int(date.replace(tzinfo=timezone.utc).timestamp() * 1000)) #Includes conversion to UTC and multiplication by 1000 to match API values for midnight

#Testing for Unix epoch time conversion - DELETE WHEN FINISHED
		for epoch_date in epoch_range:
			print(epoch_date)

Using the supplied user input date, the script would then use openWorkbook() to open the appropriate Excel file and activate the required worksheet.

def openWorkbook():
    global excel
    excel = win32.gencache.EnsureDispatch('Excel.Application')
    workbookPath = '/Users/***********/Desktop/*/Financial Reports' + '/' + str(year) + '/' + str(month) + ') ' + wbMonth
    os.chdir(workbookPath)
    wbStr = wbMonth + " Ledger.xlsm"
    #NEED TO ADD THE ABSPATH IN ORDER FOR WORKBOOKS.OPEN TO ACCEPT THE FILEPATH AS VALID
    global wb
    wb = excel.Workbooks.Open(os.path.abspath(wbStr))
    excel.Visible = True
    ws = wb.Worksheets("Crypto Investments").Activate()

Following this, the script would send an API call to fetch specified data for a predetermined list of tokens, parsing it using JSON format.

def getDailyTokenValues():
    url = "https://api.coingecko.com/api/v3/simple/price?ids=bitcoin,ethereum,litecoin,clover-finance,fetch-ai,stellar,compound-governance-token,the-graph,cardano,amp-token,auction,loopring,melon,iexec-rlc,iotex,jasmycoin,cryptex-finance,render-token&vs_currencies=usd&x_cg_demo_api_key=CG-************************"
    response = urlopen(url)
    data_json = json.loads(response.read())

    if todayString == shortDateString:
        global currentBitcoinTokenValue
        currentBitcoinTokenValue = data_json['bitcoin']['usd']
        global currentEthereumTokenValue
        currentEthereumTokenValue = data_json['ethereum']['usd']
        global currentLitecoinTokenValue
        currentLitecoinTokenValue = data_json['litecoin']['usd']
        global currentCloverTokenValue
        currentCloverTokenValue = data_json['clover-finance']['usd']
        global currentFetchTokenValue
        currentFetchTokenValue = data_json['fetch-ai']['usd']
        global currentStellarTokenValue
        currentStellarTokenValue = data_json['stellar']['usd']
        global currentCompoundTokenValue
#Truncated for display#

The script proceeds to perform basic arithmetic functions using data found in the Excel workbook.

#Multiplies current token value by token balance and writes to current valuation for totaling
    if todayString == shortDateString:
        wb.Sheets("Crypto Investments").Range("N6").Value = currentBitcoinTokenValue * wb.Sheets("Crypto Investments").Range("M6").Value
        wb.Sheets("Crypto Investments").Range("N7").Value = currentEthereumTokenValue * wb.Sheets("Crypto Investments").Range("M7").Value
        wb.Sheets("Crypto Investments").Range("N8").Value = currentLitecoinTokenValue * wb.Sheets("Crypto Investments").Range("M8").Value
        wb.Sheets("Crypto Investments").Range("N9").Value = currentCloverTokenValue * wb.Sheets("Crypto Investments").Range("M9").Value
        wb.Sheets("Crypto Investments").Range("N10").Value = currentFetchTokenValue * wb.Sheets("Crypto Investments").Range("M10").Value
        wb.Sheets("Crypto Investments").Range("N11").Value = currentStellarTokenValue * wb.Sheets("Crypto Investments").Range("M11").Value
#Truncated for display#

The script concludes with the writing of the appropriate data to the associated cells in the Excel workbook.

        wb.Sheets("Crypto Investments").Range("N6").Copy()
        wb.Sheets("Crypto in Depth").Range(bitcoinCIDColumn + dateRow).PasteSpecial(Paste=-4163)
        wb.Sheets("Crypto Investments").Range("N7").Copy()
        wb.Sheets("Crypto in Depth").Range(ethereumCIDColumn + dateRow).PasteSpecial(Paste=-4163)
        wb.Sheets("Crypto Investments").Range("N8").Copy()
        wb.Sheets("Crypto in Depth").Range(litecoinCIDColumn + dateRow).PasteSpecial(Paste=-4163)
        wb.Sheets("Crypto Investments").Range("N9").Copy()
        wb.Sheets("Crypto in Depth").Range(cloverCIDColumn + dateRow).PasteSpecial(Paste=-4163)
        wb.Sheets("Crypto Investments").Range("N10").Copy()
        wb.Sheets("Crypto in Depth").Range(fetchCIDColumn + dateRow).PasteSpecial(Paste=-4163)
        wb.Sheets("Crypto Investments").Range("N11").Copy()
        wb.Sheets("Crypto in Depth").Range(stellarCIDColumn + dateRow).PasteSpecial(Paste=-4163)

Like this project

Posted Jun 17, 2025

Developed an ETL script for processing crypto valuation data into Excel.

Likes

Views

Timeline

Aug 15, 2022 - Aug 22, 2022