SQL 筆記

  SELECT :指定要檢索的欄位(例如 name, age)。 FROM :指定資料來源的表格。 WHERE :用於過濾符合條件的資料(例如 age > 18)。 ORDER BY :用於排序結果(例如 ORDER BY age DESC) WHERE column_name BETWEEN value1 AND value2; 說明 value1 和 value2 是範圍的上下界(包含)。 BETWEEN 相當於 >= value1 AND <= value2。 如果要排除範圍內的值,可以使用 NOT BETWEEN。 INNER JOIN 表格2 ON 表格1.欄位 = 表格2.欄位; INNER JOIN:僅返回兩個表格中匹配的記錄,若某記錄在任一表格中沒有對應匹配,則不會出現在結果中。

yfinance

 import yfinance as yf


msft = yf.Ticker("MSFT")


# get all stock info

msft.info


# get historical market data

hist = msft.history(period="1mo")


# show meta information about the history (requires history() to be called first)

msft.history_metadata


# show actions (dividends, splits, capital gains)

msft.actions

msft.dividends

msft.splits

msft.capital_gains  # only for mutual funds & etfs


# show share count

# - yearly summary:

msft.shares

# - accurate time-series count:

msft.get_shares_full(start="2022-01-01", end=None)


# show financials:

# - income statement

msft.income_stmt

msft.quarterly_income_stmt

# - balance sheet

msft.balance_sheet

msft.quarterly_balance_sheet

# - cash flow statement

msft.cashflow

msft.quarterly_cashflow

# see `Ticker.get_income_stmt()` for more options


# show holders

msft.major_holders

msft.institutional_holders

msft.mutualfund_holders


# show earnings

msft.earnings

msft.quarterly_earnings


# show sustainability

msft.sustainability


# show analysts recommendations

msft.recommendations

msft.recommendations_summary

# show analysts other work

msft.analyst_price_target

msft.revenue_forecasts

msft.earnings_forecasts

msft.earnings_trend


# show next event (earnings, etc)

msft.calendar


# Show future and historic earnings dates, returns at most next 4 quarters and last 8 quarters by default. 

# Note: If more are needed use msft.get_earnings_dates(limit=XX) with increased limit argument.

msft.earnings_dates


# show ISIN code - *experimental*

# ISIN = International Securities Identification Number

msft.isin


# show options expirations

msft.options


# show news

msft.news


# get option chain for specific expiration

opt = msft.option_chain('YYYY-MM-DD')

# data available via: opt.calls, opt.puts

If you want to use a proxy server for downloading data, use:


import yfinance as yf


msft = yf.Ticker("MSFT")


msft.history(..., proxy="PROXY_SERVER")

msft.get_actions(proxy="PROXY_SERVER")

msft.get_dividends(proxy="PROXY_SERVER")

msft.get_splits(proxy="PROXY_SERVER")

msft.get_capital_gains(proxy="PROXY_SERVER")

msft.get_balance_sheet(proxy="PROXY_SERVER")

msft.get_cashflow(proxy="PROXY_SERVER")

msft.option_chain(..., proxy="PROXY_SERVER")

...

Multiple tickers

To initialize multiple Ticker objects, use


import yfinance as yf


tickers = yf.Tickers('msft aapl goog')


# access each ticker using (example)

tickers.tickers['MSFT'].info

tickers.tickers['AAPL'].history(period="1mo")

tickers.tickers['GOOG'].actions

To download price history into one table:


import yfinance as yf

data = yf.download("SPY AAPL", start="2017-01-01", end="2017-04-30")

yf.download() and Ticker.history() have many options for configuring fetching and processing, e.g.:


yf.download(tickers = "SPY AAPL",  # list of tickers

            period = "1y",         # time period

            interval = "1d",       # trading interval

            prepost = False,       # download pre/post market hours data?

            repair = True)         # repair obvious price errors e.g. 100x?

Review the Wiki for more options and detail.


Smarter scraping

To use a custom requests session (for example to cache calls to the API or customize the User-agent header), pass a session= argument to the Ticker constructor.


import requests_cache

session = requests_cache.CachedSession('yfinance.cache')

session.headers['User-agent'] = 'my-program/1.0'

ticker = yf.Ticker('msft', session=session)

# The scraped response will be stored in the cache

ticker.actions

Combine a requests_cache with rate-limiting to avoid triggering Yahoo's rate-limiter/blocker that can corrupt data.


from requests import Session

from requests_cache import CacheMixin, SQLiteCache

from requests_ratelimiter import LimiterMixin, MemoryQueueBucket

from pyrate_limiter import Duration, RequestRate, Limiter

class CachedLimiterSession(CacheMixin, LimiterMixin, Session):

    pass


session = CachedLimiterSession(

    limiter=Limiter(RequestRate(2, Duration.SECOND*5),  # max 2 requests per 5 seconds

    bucket_class=MemoryQueueBucket,

    backend=SQLiteCache("yfinance.cache"),

)

Managing Multi-Level Columns

The following answer on Stack Overflow is for How to deal with multi-level column names downloaded with yfinance?


yfinance returns a pandas.DataFrame with multi-level column names, with a level for the ticker and a level for the stock price data

The answer discusses:

How to correctly read the the multi-level columns after saving the dataframe to a csv with pandas.DataFrame.to_csv

How to download single or multiple tickers into a single dataframe with single level column names and a ticker column

pandas_datareader override

If your code uses pandas_datareader and you want to download data faster, you can "hijack" pandas_datareader.data.get_data_yahoo() method to use yfinance while making sure the returned data is in the same format as pandas_datareader's get_data_yahoo().


from pandas_datareader import data as pdr


import yfinance as yf

yf.pdr_override() # <== that's all it takes :-)


# download dataframe

data = pdr.get_data_yahoo("SPY", start="2017-01-01", end="2017-04-30")

Timezone cache store

When fetching price data, all dates are localized to stock exchange timezone. But timezone retrieval is relatively slow, so yfinance attemps to cache them in your users cache folder. You can direct cache to use a different location with set_tz_cache_location():


import yfinance as yf

yf.set_tz_cache_location("custom/cache/location")

...


留言

這個網誌中的熱門文章

國產機車馬力表 2019

國產機車馬力表 2018

國產機車馬力表 2020