John Ji

John Ji

Financial Analyst

Hello!

Paper Napkin Forecasting

Fast and light weight revenue forecast with dynamic value generation and basic automated error/data checking. Ideal for last minute investor calls or time crunch situations.

Keying in baseline assumptions in the dashboard panel makes for fast and accurate model building. Easy to understand UI and can be used by non-technical personnel.

Excel Forecasting Model

Discounted Cashflow Model

Full scale DCF of AT&T Inc. Forecasts assumption balance sheets and income statements, projects FCF and computes WACC. Derives terminal value using exit multiple method and perpetuity/Gordon growth

Originally made for class project back in college. Production level DCF models available for sampling by request only.

Excel DCF Model

Real-Time Stock Portfolio Dashboard

Porfolio table for select small cap value stocks. Pulls live stock data from Yahoo! Finance API (YQL) using xlwings library and requests module. Written in Python.

Replaces excel VBA with Python Programming language, leading to more robust and efficient code base.

Excel Stock Portfolio Model

FeedRover

FeedRover

FeedRover is a minimalist news aggregator. Get today’s top trending World News headlines in under 30 seconds. No fluff-piece or click-bait article promise.

Built on Django, served via NGINX and Gunicorn. Individual project.

Flipbook

Flipbook

Flipbook is a company screening and lead generation tool for middle market investment banks and private equity firms. The purpose of this tool is to eliminate unnecessary copy & pasting between your workbook and browser, improving efficiency

Written in C#, served via IIS7. Co-partnered project. Built front end only.

Email Crawler

Web crawler that finds faculty and staff in the IR department of all US universities and inventories their email

Written in Python, uses Selenium Webdriver, BeautifulSoup and PhantomJS

I'm John, thanks for visiting my porfolio. I like to keep things simple, clean and organized.

I do a lot of work in excel and I like to program a bit as well on the side. In my free time, I enjoy strumming away on the guitar and being a tourist in my own city.

I can be reached at (Contact Email Has Been Disabled Available)

Excel GUI
Script

                        
                            from xlwings import Workbook, Range
                            import requests, logging, datetime, time, random, os


                            # Initiate Logging
                            logging.basicConfig(filename='errors.log', level=logging.INFO)

                            # Global Variables
                            jsonData = {}
                            outputData = []
                            now = datetime.datetime.now().strftime("%m-%d-%Y %H:%M")


                            # Establish Connection Between Workbook and Python

                            def connect():
                                wb = Workbook.caller()
                                Range("Dashboard", "excelData").clear_contents()
                                logging.critical(now + ": Could not connect with Workbook, check VBA connection (Alt+F11)")
                                query()


                            # Making ReST Query to Yahoo and Retrieving Data
                            def query():
                                queryString = ""
                                counter = 1
                                stockList = Range('A6:A20').value
                                # stockList = ["MSFT", "GOOG", "YAHOO"] # Debugging Purposes
                                for stock in stockList:
                                    queryString += '"' + str(stock) + '"' + '%2C'

                                queryString = queryString[0:-3]
                                stringAddon = "store%3A%2F%2Fdatatables.org%2Falltableswithkeys&callback="
                                query = "https://query.yahooapis.com/v1/public/yql?q=select * from yahoo.finance.quotes where symbol in (%s)&format=json&diagnostics=true&env=%s" % (queryString, stringAddon,)
                                logging.info("ReST Query Made at " + now)
                                # print query
                                r = requests.get(query)

                                if r.status_code == 200:
                                    jsonData = r.json()
                                    logging.debug(now + "Successful Request Made to Yahoo")
                                    # print jsonData
                                else:
                                    while counter < 5:
                                        logging.warning(now + ": ReST query failed, check site URL and server status. Attempt #" + counter)
                                        r = requests.get(query)
                                        if r.status_code == 200:
                                            jsonData = r.json()
                                            break
                                        counter += 1
                                        time.sleep(random.randrange(2, int(counter)*5))


                                counter = 0 #reset counter

                                logging.info(now + "Began parsing JSON response")
                                while counter < len(stockList): 
                                    # Parsing JSON response into actionable data
                                    stockDataRaw = [
                                        str(jsonData["query"]["results"]["quote"][counter]["AverageDailyVolume"]),
                                        str(jsonData["query"]["results"]["quote"][counter]["Ask"]),
                                        str(jsonData["query"]["results"]["quote"][counter]["Change"]),
                                        str(jsonData["query"]["results"]["quote"][counter]["BookValue"]),
                                        str(jsonData["query"]["results"]["quote"][counter]["EarningsShare"]),
                                        str(jsonData["query"]["results"]["quote"][counter]["MarketCapitalization"]),
                                        str(jsonData["query"]["results"]["quote"][counter]["EBITDA"]),
                                        str(jsonData["query"]["results"]["quote"][counter]["PERatio"]),
                                        str(jsonData["query"]["results"]["quote"][counter]["PEGRatio"]),
                                    ]
                                    StockData = stockDataRaw
                                    outputData.append(StockData)
                                    counter += 1

                                # Writing data to Excel Workbook    
                                Range("Dashboard", "excelData").value = outputData


                            if __name__ == '__main__':
                                path = os.path.abspath(os.path.join(os.path.dirname(__file__), 'SmallCapValueStocks.xlsm'))
                                Workbook.set_mock_caller(path)
                                # query()
                                connect()