Category Archives: Data Analysis

Transposing Arrays to Columns

I’m currently working on developing network graphs for companines within the software space. I hope to derive insights across 1. what companies compete in the most (or least) impacted industries and 2. systematically “bucket” companies by function

I’ve recently completed populating my database of companies by function. Now, I need to clean my data and prepare to feed in to a clean csv file. Notes for myself below:

Order of Operations

  • Clean data to preserve relationships between nodes
  • build a reusable turk that transforms arrays in to a single column

Parse

  • each row begins with a parent node
  • each row ends with a count of the number of children nodes in a row
  • a = parent (begin)
  • b = number of children in row (end)
  • a2 = b1 + 1
  • results in unique value for each node in system (>400 children)
  • next sheet harbors x…x+n
  • [ next puzzle: toggle between rows ]
  • run index match on b1 through bn (child counter column)
  • matched value is equivalent to row number
  • if match results in error, select last row letter
  • drive indirect to call value
  • [next puzzle: toggling columns]
  • Next column:   Count up number of occurrences of each row number
  • recycle count when last row is not equal to current row

Non-Conventional Data Manipulation

Today, I transformed CSV data to JSON by cobbling together JSON syntax in excel. Although unconventional, a few thoughts on “automating” this process in the future:

  • GUI / Input: Excel
  • Concatenate data in Excel
  • Export: VBA script that writes column data to “data.json”
  • Point to JSON file within HTML file

What Is the Consensus Opinion?

It is now possible to track what media users are consuming. By extension, it’s possible to observe what themes / controversies rise (and fall) in popularity over time. The following include data sources I plan on digging in to and observing over the next few months:

  • Twitter: Hashtags and Influencers
  • YouTube: Influencers
  • Google: Searches
  • Facebook: Trending Data

Housing Prices in San Francisco

The cost of living in San Francisco is similar to that of New York, as evidenced by the median sale price of homes and rent per month.

However, a wide disparity exists: the price per square foot per home in San Francisco is still much lower than that of New York. This enables us to deduce that 1) homes in SF are probably much larger than those in New York and 2) there is still significant room for Real Estate in San Francisco to appreciate to what (arguably) represents its terminal value – Price per Square Foot in New York.

Upside Considerations:

  • There is more land available in San Francisco
  • San Francisco infrastructure still has a lot of room for improvement (Transbay Terminal; BART extension to China Town)
  • San Francisco is a first mover with respect to *potential* new infrastructure technologies (driverless cars, micro-housing projects)
  • Desirable climate
  • Growing technology industry

Downside Considerations

  • Growth of housing supply in Oakland
  • Updating policies associated with housing supply restrictions in SF

Despite high pries, perhaps property values still represent a significant discount to terminal value?

Source Data: Zillow

Systematic Value Turk

  • Limit investable universe (geography, size)
  • Exclude companies at risk of permanent loss of capital (accrual basis: net income > cash flow from operations)
  • Gauge negative investor sentiment (price earnings or ev / ebit basis)
  • Screen for quality
    • Identify economic moat (long term FCF generation, long term return on capital, long term margin strength (gross, operating))
    • Current financial strength (F-Score (3 elements): current profitability, current stability, operational improvements)

Continue reading →