Crypto Ledger API ETL Script Development by Shane SapolisCrypto Ledger API ETL Script Development by Shane Sapolis

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